Open Excel Workbook, then Close Execl Using a Scheduled Task.

I am looking for a way to schedule a task which will open a specified Excel workbook, then close Excel. The workbook that I want to open has a macro inside the WorkBook_Open() function which causes the contents of one of the Worksheets to be saved to a text file.

The WorkBook contains Jet Reports coding. I can schedule a task to run the report, but using, say, the Before_Close() function does not work. I want to have this work under Windows Server 2008 using Excel 2007. The Before_Close function worked under Windows Server 2003/Excel 2033, but it does not work under W2008/E2007, so I am looking for another method to cause the saving of a Worksheet via a scheduled task.

I have tried this using a batch file, but have had no luck so far.

MaglinFurnitureAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
If you hit 'Object' it will let you assign the points. But it's not a big deal to wait 4 days either.
0
 
dazwillnotCommented:
Probably not the best of solutions and I'm sure someone here can do better, but you could script the file open, add a time delay then use a TASKKILL /f command on Excel.exe
0
 
MaglinFurnitureAuthor Commented:
Hi dazwillnot

Not sure how to script the file open. I put the following into a batch file:
@start "M\Management\Reports\Daily Status Summary.xlsm"
exit

Running the batch file didn't really do anything. Maybe because there was no time delay. Wht is a good method for creating a time delay?

What would the complete syntax be for the taskkill command in the batch file?

Thanks


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dazwillnotCommented:
At a guess, try this.  As I say, I'm pretty sure there will be a far beter method for this.  Was just throwing a random thought out there.  What I don't know, is whether this will open the file irrespective of your Macro Security settings, I suspect it won't.

REM Open Excel File
start "M:\Management\Reports\Daily Status Summary.xlsm"

REM Pauses for 10 seconds
SLEEP 10 

REM Stops Excel without Saving
TASKKILL /F /IM "EXCEL.EXE"

CLS

Open in new window


0
 
MaglinFurnitureAuthor Commented:
Strange, SLEEP is not recognized as an internal or external command.
The taskkill command is trying to close all Excels -- not good. In any case, it says "Access is denied,"
which is good.

'SLEEP' is not recognized as an internal or external command,
operable program or batch file.
ERROR: The process "EXCEL.EXE" with PID 8524 could not be terminated.
Reason: Access is denied.
ERROR: The process "EXCEL.EXE" with PID 18140 could not be terminated.
Reason: Access is denied.
ERROR: The process "EXCEL.EXE" with PID 6080 could not be terminated.
Reason: Access is denied.
ERROR: The process "EXCEL.EXE" with PID 13032 could not be terminated.
Reason: Access is denied.
ERROR: The process "EXCEL.EXE" with PID 19220 could not be terminated.
Reason: Access is denied.
0
 
TommySzalapskiCommented:
use a .vbs file with this code

set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

set xlBook = xlApp.Workbooks.Open("C:\temp\book1.xlsm")

xlBook.Macro1

xlBook.Save
xlBook.Close
xlApp.Quit

Open in new window

0
 
TommySzalapskiCommented:
.vbs is designed much better to work with Excel and just about everything else. They are often much better than batch files for anything more than running simple shell commands.

If you don't want the Excel file to pop up on the screen just don't set visible to true.

If you have a macro that runs on open you won't need to call anything but if you want more control, you can actually call the macro from the .vbs file.

You can point task scheduler to .vbs files with no problem.
0
 
MaglinFurnitureAuthor Commented:
To: TommySzalapski:

After reading your reply, I did a search for VB Script to do what you suggest, as I am not familiar with vbs. I found something and modified as follows. If run from a commnd prompt, I get a syntax error pop up, saying at line 3 char 14, which is where the name of the Excell file that has the macro in it is set. However, I can't see why there is a syntax error. Can you?

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objExcel = Wscript.CreateObject("Excel.Application")
Set objFile =
objFileSystem.GetFile("M:\Management\Reports\Daily Status Summary.xlsm")
objExcel.visible = false
objExcel.Workbooks.Open(objFile.path)
objExcel.Quit
Set objExcel = nothing

 VBS Syntax Error
0
 
TommySzalapskiCommented:
It should be like this
Set objFile = objFileSystem.GetFile("M:\Management\Reports\Daily Status Summary.xlsm")
If you want to break a line you need to put a _ at the end like
Set objFile = _
objFileSystem.GetFile("M:\Management\Reports\Daily Status Summary.xlsm")

(needs the space)

You don't need the FileScriptingObject though. The code I posted should work. I tested it. (Did you not see that post? It's right before the one you read.)
0
 
MaglinFurnitureAuthor Commented:
To: TommySzalapski:

Sorry, I completely miss your code post.
I made the change to the code I found as you suggested and the script now works. But, I got a pop up asking me if I wanted save changes to the Workbook.

I took a look at your code post and notice that you have added code to save the workbook. Is there code that will prevent the prompt to save/or cause a close without saving?

I don't need to save the workbook, just to cause a specified sheet to be saved as a text file. I could save it, but would rather not. (If I remove your save command, I get the prompt to save the changes)


0
 
TommySzalapskiCommented:
Oh, then use
xlBook.Close(False)
instead of just xlBook.Close
That will tell it not to prompt and just close without saving.
0
 
TommySzalapskiConnect With a Mentor Commented:
If you are still using the code you posted you will need to capture the return of the open like
Set objBook = objExcel.Workbooks.Open(objFile.path)

with
objBook.Close(False)
at the end
0
 
MaglinFurnitureAuthor Commented:
To: Tommy Szalapski:

I tested using your code. It worked fine.

Thanks much. (Now I hope it will work as a scheduled task!)
0
 
TommySzalapskiConnect With a Mentor Commented:
I do it all the time so it certainly should.
0
 
MaglinFurnitureAuthor Commented:
I just did a test. It works fine.

Thank you.
0
 
MaglinFurnitureAuthor Commented:
I just received an email regarding this. I did not realize I had not accepted and awarded points. I just tried to do that and could not because of the pending close request. In any case, I agree with closing with ACCEPT and award the 500 points. Give Tommy and A!

Thanks

All the best for the hlidays.
0
 
MaglinFurnitureAuthor Commented:
Why wait?
0
 
MaglinFurnitureAuthor Commented:
Merry Christmas!

(I hope the point awards worked -- I had to award points to 2 comments -- strange.
0
 
TommySzalapskiCommented:
Yep, worked just fine.
0
All Courses

From novice to tech pro — start learning today.