how do I run excel in the scheduler

[Guest]

When I schedule a task, the status shows "running" for a
loooong time. What is going on? I am only opening an
excel file which has a startup macro with just a test
write to one of the cells.

Thanks

 

[Dave Patrick]

You're probably not closing it. This VBScript may help.

----------------------------
Option Explicit
Dim filePath, oExcel, oSheet

filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oExcel.Run "macro1"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing
----------------------------

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| When I schedule a task, the status shows "running" for a
| loooong time. What is going on? I am only opening an
| excel file which has a startup macro with just a test
| write to one of the cells.
|
| Thanks

 

[Pegasus \(MVP\)]

When I schedule a task, the status shows "running" for a
loooong time. What is going on? I am only opening an
excel file which has a startup macro with just a test
write to one of the cells.

Thanks

Excel is meant to run as an interactive application, not as
a backgroup job. This means that it could have various
prompts that require your response, e.g. a prompt to
allow macros to run.

 

[Guest]

Thanks much...it helps tremendously. I was struggling with
this for a looooong time.

 

[Guest]

Dave

I followed your advise and added activeworkbook.save and
close to the macro. Now, it is opening the file, running
the macro and saving the file. But, the status still
shows as running. What's wrong?

Thanks

 

[Dave Patrick]

Probably should have been;

----------------------------
Option Explicit
Dim filePath, oExcel, oSheet

filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oExcel.Run "macro1"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
----------------------------



--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| Dave
|
| I followed your advise and added activeworkbook.save and
| close to the macro. Now, it is opening the file, running
| the macro and saving the file. But, the status still
| shows as running. What's wrong?
|
| Thanks