Solved

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

Posted on 2010-11-16
20
1,637 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now