none
Automate Excel via COM in a Scheduled task RRS feed

  • Question

  • Hi

    I have a a small program written in a 3rd party 4GL (Dataflex) that automates Excel via COM

    It works fine from the command line without any user interaction

    The automation process .....
    1. Connects to Excel
    2. Gets the Workbooks Collection
    3. Adds a Workbook
    4. Populates the first worksheet via a Range with information
    5. Saves it as an XLSX workbook and exports it to PDF
    6. Closes workbook and disconnects from Excel

    However when it is run from the windows task scheduler it gets stuck at point 3

    As it gets past points 1 and 2 I assume I don't have a user/authentication problem ?

    I have tried creating the blank desktop folders mentioned in this thread along with the DCOM user but as yet no luck
    http://serverfault.com/questions/609651/what-is-the-default-working-directory-for-a-scheduled-task-in-windows-2008-r2

    At point 3 it just hangs, no event log entry is generated and the task is stuck "running"

    Any help greatly appreciated

    Thanks

    Windows 10  Excel 2010

    Tuesday, July 19, 2016 11:11 AM

Answers

  • >>>However when it is run from the windows task scheduler it gets stuck at point 3

    As it gets past points 1 and 2 I assume I don't have a user/authentication problem ?<<<

    According to your description, developers can use Automation in Microsoft Office to build custom solutions that use the capabilities and the features that are built into the Office product. 

    Although such programmatic development can be implemented on a client system with relative ease, a number of complications can occur if Automation takes place from server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.

    Microsoft strongly recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and more quickly than Automation. For example Open XML SDK.

    For more information, click here to refer about Considerations for server-side Automation of Office


    Wednesday, July 20, 2016 4:55 AM
  • It turns out if you open and EXISTING workbook then everything works fine 

    So there must be something in the adding a new workbook to an empty collection that requires some extra rights/temps files etc

    • Marked as answer by Minieggs1999 Wednesday, July 27, 2016 10:24 AM
    Wednesday, July 27, 2016 10:24 AM

All replies

  • >>>However when it is run from the windows task scheduler it gets stuck at point 3

    As it gets past points 1 and 2 I assume I don't have a user/authentication problem ?<<<

    According to your description, developers can use Automation in Microsoft Office to build custom solutions that use the capabilities and the features that are built into the Office product. 

    Although such programmatic development can be implemented on a client system with relative ease, a number of complications can occur if Automation takes place from server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.

    Microsoft strongly recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and more quickly than Automation. For example Open XML SDK.

    For more information, click here to refer about Considerations for server-side Automation of Office


    Wednesday, July 20, 2016 4:55 AM
  • It turns out if you open and EXISTING workbook then everything works fine 

    So there must be something in the adding a new workbook to an empty collection that requires some extra rights/temps files etc

    • Marked as answer by Minieggs1999 Wednesday, July 27, 2016 10:24 AM
    Wednesday, July 27, 2016 10:24 AM