_Excel and scheduled tasks

[hannes08]

Hi folks,

I have written a small script that takes some csv files and puts them into excel and also creates a diagram in the sheet.

Now this script runs on a daily basis and is running fine on Win 2003 / Office 2003 without any problems.

I need to port it to Win 2008 (64 bit) / Excel 2003 and this is where I run into several problems:

1. The script fails to open an existing excel file. On _ExcelBookOpen it just stalls. There's no return from that function.

2. If there is no excel sheet to attach to it fails on saving the excel Book. There's no return from _ExcelBookSaveAs function.

This are the two arts of the script:

...
    cw($debugc, "File does already exist! Adding...")
    Local $oExcel = _ExcelBookOpen($s_filename, $debug)
    $er = @error
    $ex = @extended
    cw($debugc, "Result of opening an existing Excel Book:" & @CRLF & @TAB & "Object: " & @TAB & $oExcel & @CRLF & @TAB & "Error: " & @TAB & $er & @CRLF & @TAB & "Extended: " & @TAB & $ex)
...

...
    cw($debugc, "Saving Excel Book as: " & $s_filename)
    $rc = _ExcelBookSaveAs($oExcel, $s_filename, "xls", 0, 1)
    cw($debugc, "Error on SaveAs: " & @TAB & @error )
    If $rc <> 1 Then
    SetError(@error)
    Return 4
    EndIf
...

If I run the program in interactive mode (not from scheduled task) it works just fine. So the basic question is: What do I have to think of if I try running this in scheduled task mode?

Thanks for your help!

[water]

Hi Hannes,

what comes to my mind is:

• Depending on the setup of the scheduled task your script might run in another users context. So not all shares might be available
• Be careful when trying to interact with the console. The scheduled task has to be seup to interact with the desktop. I would suggest to replace ConsoleWrite with writing to a log file
• Be sure to run your scripts as 32-bit if the installed Office is 32-bit even if running on a 64-bit OS (The Excel save problem points into this direction)

Just my 0.02$ Edited August 8, 2011 by water

[hannes08]

Hi water,

thanks for your suggestions.

The scheduled task runs at the proper user, shares have been excluded from this problem because it reads and creates local files (C:\temp ...)

I need the console writes for debugs, as this is not run from the normal scheduled tasks but from a 3rd party task scheduling solution it grabs the outputs from console and displays it in a log.

The script was compiled on my 32bit XP in 32bit arch, so this is not an issue.

If I look into the _ExcelBookOpen() function I see that the script fails on:

With $oExcel
    .WorkBooks.Open($sFilePath, Default, $fReadOnly) ; "C:\temp\saprep\excel.xls", Default, False
EndWith