Scheduled Excel 2007/2010 VBA hanged

Question

Hi,

     I have been using Excel 2002 macro to generate reports on a server. Recently, we installed Excel 2007/2010 version on the same server, different directories, to support our new reporting need.

     I have enabled "All Macros" and add in the program directory as Trusted Sources in Excel 2007/2010. When I execute the report in the interactive mode, i.e. I click on the Excel file with "Auto-open" macro, it is able to generate the report successfully. However, when I schedule it to run at a certain timing, the process will just simply hang there; I saw the Excel process in the task manager, but I do not know what is it doing.

     Please advise what additional setting is required at the server so that the report can be generated smoothly. 

     Do note that this is not regarding the compatibility between Office 2002 and Office 2007/2010, nor is it regarding 2 version cannot be installed concurrently on the same machine. It is regarding why the report is not able to proceed in a scheduled mode, while there is no issue when running interactively.

     Thanks in advance.

Regards.

Answer 1

I assume that you tried from the server to run manually in both 2007 and 2010.  what you ned ot find out is which command files and command line options the server was using when it ran the program. To help isolate where the problem is you may want to create a .bat file and have the server and schedule the bat file to run so you can easily change the command line options.

There may be some missing (of different) environmental variables that get used when you are running a scheduled task.

The auto run method is suppose to be obsolete back in excel 2000(was developed for excel 1997) so I would recommend using the workbook open event.

---

jdweng

Answer 2

Thanks for the fast response and sorry, I did not specify clearly.

Yup, I am using a .BAT file to trigger off the Excel file. The command is as follow:

set SOURCEFILE=E:\program\Excel_file_to_execute.xlsm
"C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe" %SOURCEFILE%

When I run manually in the server, i.e. double click on the .BAT file, the report is generated successfully in less than a minute. However, when I do a schedule to trigger it off, the .BAT is triggered off, but the excel process is just hanged there. I am not able to know what it is waiting for.

As for the macro command, I am using "Sub auto_open()". Is the command deprecated in Excel 2007/2010?

Answer 3

I suggest you have a look at the following link. A lot of reading to get through all the posts that span several years up to the current month but it could be something to do with permissions and there are suggestions for fixing.

http://social.technet.microsoft.com/Forums/en-US/winservermanager/thread/d47d116e-10b9-44f0-9a30-7406c86c2fbe

Your comment "As for the macro command, I am using "Sub auto_open()". Is the command deprecated in Excel 2007/2010?". Still works in xl2010. Also it runs independant of the setting of Application.EnableEvents.

Also you could try placing a few MsgBox's in the VBA code in the workbook to be opened so you can see how far it gets. It is possible that you might need a DoEvents line in the code at some point to force it to finish some processing at some particular point before continuing.

---

Regards, OssieMac

Answer 4

See the following webpage for command line switches.  I would tru the /o and /e.  Yo also may need to specify the temp folder to use.

http://support.microsoft.com/kb/291288

---

jdweng