Jump to content
Sign in to follow this  
hannes08

_Excel and scheduled tasks

Recommended Posts

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!


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×