Solved

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

Posted on 2010-11-16
20
2,065 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:MaglinFurniture
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
20 Comments
 
LVL 3

Expert Comment

by:dazwillnot
ID: 34146564
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
 

Author Comment

by:MaglinFurniture
ID: 34146783
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
 
LVL 3

Expert Comment

by:dazwillnot
ID: 34146930
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MaglinFurniture
ID: 34147177
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148096
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148118
.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
 

Author Comment

by:MaglinFurniture
ID: 34148589
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148694
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
 

Author Comment

by:MaglinFurniture
ID: 34148911
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148966
Oh, then use
xlBook.Close(False)
instead of just xlBook.Close
That will tell it not to prompt and just close without saving.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34148991
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
 

Author Comment

by:MaglinFurniture
ID: 34149130
To: Tommy Szalapski:

I tested using your code. It worked fine.

Thanks much. (Now I hope it will work as a scheduled task!)
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34149196
I do it all the time so it certainly should.
0
 

Author Comment

by:MaglinFurniture
ID: 34149520
I just did a test. It works fine.

Thank you.
0
 

Author Comment

by:MaglinFurniture
ID: 34412252
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
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34412337
If you hit 'Object' it will let you assign the points. But it's not a big deal to wait 4 days either.
0
 

Author Comment

by:MaglinFurniture
ID: 34412363
Why wait?
0
 

Author Closing Comment

by:MaglinFurniture
ID: 34412420
Merry Christmas!

(I hope the point awards worked -- I had to award points to 2 comments -- strange.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34412494
Yep, worked just fine.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question