A Day in the Life

A day in my life. Thoughts on leadership, management, startups, technology, software, concurrent development, etc... Basically the stuff I think about from 10am to 6pm.

4/10/2006

Excel: Running Excel on Windows Task Scheduler

One thing that comes up from time to time on the Excel message boards is “How do I run Excel without opening it?” Basically the developer wants to run Excel programmatically as a COM server without any human intervention. I’ve seen folks recommend “Excel.exe workbook.xls” and making sure that the macro you want to execute is called from the Workbook_Open() event. The problem with this technique is control. The Excel developer would have to use the state machine methodology I mentioned previously, which is just a pain in the %$$.

I think the best way to run Excel without user interaction is using an Excel Controller. An Excel Controller is a script, DLL, or executable that uses Excel’s COM interface. This technique also answers the question of “How do I run Excel from a scheduler?”

So I’m going to answer the more complicated question of how to run Excel from Microsoft Task Scheduler.

1) You need an Excel Controller. If you’re comfortable with VBA then you’ll probably be comfortable with VBS. (Visual Basic Script).

Here is some sample code I use to control and automate an Excel workbook:


' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\YourWorkbook.xls"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & strPath & "\YourWorkbook" &
"!Sheet1.YourMacro"
on error resume next
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

oWorkBook.Save

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will
shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

2) In the Excel Controller code replace 'YourWorkbook' with the name of the workbook you want to open and replace 'YourMacro' with the name of the macro you want to run.

3) Launch the VBS Excel Controller using cscript.exe NOT wscript.exe. So let’s say you named the script RunExcel.vbs, then you would execute it using ‘cscript.exe RunExcel.vbs’. (If you make 'YourWorkbook' and 'YourMacro' input
parameters then the RunExcel.vbs script can run any macro in any workbook because you can just pass that information in on the command-line.)

4) Once you have the Excel Controller and workbook tested so that it does what you want, you can then use Microsoft Task Scheduler to execute ‘cscript.exe RunExcel.vbs’ automatically for you. I found a tutorial here:
http://www.iopus.com/guides/winscheduler.htm

If the Excel Controller is a script or executable, the developer has the option of passing parameters in to the Controller which can be used to initialize cells in the workbook before the macro is executed. Obviously, using the Excel COM APIs the developer can really do some neat things by expanding on the basic structure I have provided. I hope this helps someone and if you have neat additions to this, please feel free to add them in the comment section.

63 Comments:

At July 22, 2006 1:28 AM, Anonymous Anonymous said...

Excellent tip. Thanks!
JC

 
At November 15, 2006 8:56 AM, Blogger Dan said...

I really like your post too. I'm going to use it, and let you know how it goes ...

 
At November 15, 2006 6:34 PM, Anonymous Anonymous said...

G'day, I'm new to this kinda stuff and i'm having a few troubles. I have to run excel in the background (on log in ) and run a macro which alerts the user to machine calibration expiry dates for that particular week. I've used your code to open the excel file (which is awesome by the way) just wondering where to go to next??? I can't seem to get the macro running, and then there is the problem of displaying it? (I have no access to visual basic only excel) any tips would be awesome. Thanks again for getting me started!

 
At November 16, 2006 11:30 AM, Blogger Dan said...

Hi ...

It seems the call to the Macro, using this code:

strMacroName = "'" & strPath & "\Update_RLT_byDay2.xls" & _
"!SAPEntry.RecalculateRLT"

Is somehow flawed. I've tried all sorts of different combinations of quotes/double quotes, but cannot get the script to run the macro.

Do you have any hints?

Thanks in advance for the help.

 
At November 16, 2006 11:45 AM, Blogger Kim Greenlee said...

Dan,

You should first look at the results that you have in strPath. strPath should contain the directory where your Excel workbook is located.

Next, your strMacroName format doesn't match the one that I had in the sample script.

Try,

strMacroName = "'" & strPath & "\Update_RLT_byDay2'" &
"!SAPEntry.RecalculateRLT"

The differences are the removal of the ".xls" extension and the addition of the closing single quote at the end of Update_RLT_byDay2.

All Excel workbook instances run under an Excel Application instance, so when you want to start a macro, you have to tell Excel exactly which macro by putting in a complete path to the macro.

The trick is getting the path correct.

Kim

 
At November 16, 2006 11:53 AM, Blogger Kim Greenlee said...

Al,

See Dan's comment and my answer above. Maybe that will help you with getting your macro running.

As for visibility, the Excel Controller in this example is written to hide Excel from the user because I originally wrote it to run Excel remotely.

You'll want to remove a few lines from the script that disable the UI and possibly add a line to make your Excel instance visible.

Remove this section:

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

And if that doesn't get Excel visible for you, then look at adding:

myExcelWorker.Visible = True

Kim

 
At November 17, 2006 8:49 AM, Blogger Dan said...

Kim,

Thanks very much ... you are nice to help just a stranger asking questions.

I've tried the syntax you've suggested (after adding a " _" for the line continuation), but the script returns an error like:

The macro ''C:\Temp\UpdateRLT3'!Sheet1.RecalculateRLT3' cannot be found.

So, it seems at least one single quote is redundant, upon taking it out, I get still a path not found error:

The macro 'C:\Temp\UpdateRLT3!Sheet1.RecalculateRLT3' cannot be found.

With this string path syntax and just double-clicking the .vbs script in windows:

strMacroName = strPath & "\UpdateRLT3!Sheet1.RecalculateRLT3"

Thanks again for any hints ...

Dan

PS - I like your blogspot (so much I've created my own [kind of bare right now: http://dpgnothing.blogspot.com/])

 
At November 17, 2006 11:22 AM, Blogger Kim Greenlee said...

Hi Dan,

Can you send me the Excel file and the script? It's not easy debugging this via blog comments. Use the email address krgreenlee at comcast dot net.

Thanks,

Kim

 
At November 28, 2006 8:17 AM, Blogger Dan said...

Hi Kim,

I modified the line where the macro path is built so that ONLY the macro name is used (since the file was already open). I have my macro in a module, and this code worked.

strMacroName = "YourMacro"

instead of:

strMacroName = "'" & strPath & "\YourWorkbook" & "!Sheet1.YourMacro"

Thanks for your help!

Dan

 
At January 18, 2007 5:12 PM, Blogger ARob said...

Dan,

This is the correct synatx:
strMacroName = "'" & strPath & "\myWorkBook'" & "!myReport"

Note the single quote opens before the workbook and slash and end right after the workbook name. The original posting was missing the ending quote. To test open two workbooks and try to run a macro in wkbA from wkbB. That is the how you want your string to look (eg. 'wrkA.xls'!myMacro)



Kim,

This is amazing. I'm so glad I stumbled across this. Thanks so much for posting this and being so helpful. This is my first time using vbs and your instructions were pretty straightforward.

One question: I know you designed this to run Excel behind the scenes. However, the macro I am calling creates a new file. Do you know of a way to make that created file visible? If I don't close the file in the macro, it will linger behind the scenes with no access to it; only killing the excel.exe process will let me close it.

Thanks again,
Alex

 
At January 22, 2007 11:01 PM, Blogger Kim Greenlee said...

Alex,

I'm a little confused. Is the file you created an Excel file? The idea behind the Excel Controller is that you can control Excel to make sure it behaves the way you need it to.

If the file was opened from an Excel instance then it must be closed from the Excel instance.

I would suggest creating the file in Excel, closing the file, then close Excel. This should get both your file and the Excel instance closed. (I have seen references on the boards where people suggest that Excel is slow to close. But it will eventually close if you shut everything down correctly.) After you've shut Excel down, while in the Controller, open your file and reset myExcelWorker.Visible = True.

Hope that helps.

Kim

 
At July 03, 2007 7:18 PM, Anonymous Anonymous said...

This is awesome! I've been wanting to automate, on a timed basis, an excel macro. Thanks a gazillion. The macro I concoted uses the iopus iMacros software (you reference their tutorial) to extract data to a flat ascii file. The macro continues by processing it in Excel and Access. This is great!

 
At August 13, 2007 1:48 AM, Blogger Unknown said...

Thanks Kim for such a great article. I have never programmed before and even I was able to follow the tutorial.

Can you please help me with an issue I am unable to resolve. Please bare in mind I am a total novice.

Using your code I have set up the start-up of a workbook and execution of a macro. The macro loops every 10 second, but if another excel workbook is opened it (the macro) can't tell which workbook to operate the commands on.

This is my extremely simple macro:
___________________
Sub BBStream()

Application.ScreenUpdating = False

Sheets("BL").Select
Cells.Select
Selection.Copy

Sheets("Webpage").Select
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ThisWorkbook.Save

Application.ScreenUpdating = True
Application.OnTime Now() + TimeValue("00:00:10"), "BBStream"


End Sub

___________
The macro just copies and pastes the values from one sheet (BL) to another (webpage).

1. I want the macro to only work on the workbook called "Webupdate", even if another workbook is opened.

2. The workbook to continue working in the background even if Excel is open and then closed.

3. Any other tips would be great.

If you can help that would be great and much appreciated.

Regards,
Kash

 
At August 13, 2007 12:49 PM, Blogger Kim Greenlee said...

Hi Kashif,

The best way to solve this problem is to use fully qualified paths everywhere. Look at some of the comments preceeding this one where we talk about the need to use fully qualified paths when referencing Excel objects.

Also, if you are running the Excel instance as an automated COM server then you should not be having these problems because each automated Excel instance runs in it's own process space.

So you may be missing models...

Good luck,

K

 
At August 29, 2007 8:25 PM, Blogger Unknown said...

Sorry for the late reply. I was able to make the necessary changes as soon as I used your advice.

Thanks Kim for all the help.

 
At August 30, 2007 8:40 AM, Blogger Condorson said...

Wonderful bit of coding. Very helpful, thanks v. much.

As to the suggested tutorial on how to run scripts, I couldn't get mine to kick off properly through calling cscript.exe so I pointed the program browser directly to the RunExcel.vbs and that worked instead.

Many thanks. Ian

 
At September 03, 2007 3:34 AM, Blogger Michael Dharmawan said...

Hi Kim,

i would like to make a document expiry reminder in excel and needs a macro that runs in background everyday checking all the expiry dates then email the detail to my email. Is it possible to build this based on your code?

thanks,


Michael

 
At September 03, 2007 1:35 PM, Blogger Kim Greenlee said...

Michael,

Yes. I think it can be done. You will probably want to send the email from your Excel macro, here is a link that should get you started: http://www.j-walk.com/ss/excel/tips/tip86.htm

Think of the scheduler and script as the shell that your Excel macro is running in.

- The scheduler kicks off the script at a set time everyday.

- The script loads and controls Excel (so you can do your setup and guarantee Excel shut down).

- And the Macro is where your specific work is done.

Kim

 
At September 05, 2007 10:05 PM, Blogger Michael Dharmawan said...

Thanks a lot Kim,
This really helps.


Michael

 
At April 16, 2008 10:20 AM, Blogger Unknown said...

Hi Kim, I love your vbs code and was able to apply to several of my programs. I do need some help though. I need to enable an Add-in via vbs. If I bring up excel as a stand-alone, this Add-in is visible, otherwise if I execute this vbs code, it is not. Any help is greatly appreciated.
Again, thank you for that lovely piece of code!!!

 
At April 16, 2008 11:09 AM, Blogger Kim Greenlee said...

Hi LetMeDoIt...

I'm really sorry but I am so busy right now I don't have time to research this. I don't see any free time at all opening up until mid-May. If you figure this out please post what you learned...otherwise I'll leave this in my queue until I have some time.

Kim

 
At June 19, 2008 6:07 PM, Anonymous Anonymous said...

Thanks
your post is awesome
Just have a little problem. I can't access the file starightway..when i tried to open, it says read-only
how can i move that file from read-only to normal access

 
At August 07, 2008 11:19 PM, Blogger Unknown said...

I have the same problem that LetMeDoIt had. I am using a proprietary datasource via an excel plugin. through the script the macro runs fine, however data doesnt get populated because it does'nt identify the addin.

I have searched internet without success. If you have found a solution. Please let me know.

 
At October 24, 2008 9:22 AM, Anonymous Anonymous said...

This is an awesome piece of code. Thanks a ton... I am facing a small challenge though when running he script every 30 mins. It says the file is already open. Also when i try opening the file manually it does not allow me until i kill the excel instance from the task manager.
Can this code be used when the file is open on screens so that the vbs keeps pasting data every 30 mins and keeps saving it.

 
At October 24, 2008 10:20 AM, Blogger Kim Greenlee said...

There are two possibilities that come to mind. The first is that your Excel program is long running, in which case you need to either a) speed it up b) distribute the work to other machines (see Digipede Technologies) c) increase the interval between executions. The other possibility is that you are not cleanly exiting either the script or the Excel program. Read the following post which is in 5 parts...you should find some hints there.

 
At October 27, 2008 8:51 AM, Anonymous Anonymous said...

Thanks Kim... Can you pls suggest a tweak to this code so that i can use this on open files as well. Eg) I have one excel file which needs to be updated every 30 mins from a feed that i get from the server. I need to write a script an add it to the scheduled task which can keep on pasting data every 30 mins on a certain sheet in the excel file. I am so far able to pull the feed from the server automatically but am not able to paste it automatically using vbs

 
At October 27, 2008 11:12 AM, Blogger Kim Greenlee said...

Sunil K.,

I haven't worked with Excel in a few years...as you can see this post was created awhile ago. So I'm not able to provide any suggestions. Good luck.

Kim

 
At June 22, 2009 10:59 AM, Anonymous Tim Parrott said...

This post has a seriously long life. I had believed that there was a /x command line parameter to run a macro for Excel but was disappointed to find that it is only for MS Access.

I have used the code, but parameterised it so that any spreadsheet can run with an embedded macro. It now takes two parameters, the first is the full path and name of the file and the second is the macro name. The command line if placed in a Batch file is F:\runexcel.vbs -full workbook path and name- -Macro Name-. Repace the values as required and make sure there is a space between the three elements. Also avoid using directory and file names with spaces.

The script will run the workbook and then save it to a new name. The name is the original name with _YYYYMMDD to date stamp the updated file. The application presents the purchase action report for an MRP System.

The code I used is below. Any comments and improvements would be appreciated. This was a first attempt at scripting.

Many thanks to Kim Greenlees for the original article.

' Create a WshShell to get the current directory

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

If (Wscript.Arguments.Count < 2) Then
Wscript.Echo "Runexcel.vbs - Required Parameter missing"
Wscript.Quit
End If


'retrieve the arguments

Dim strWorkerWB
strWorkerWB = Wscript.Arguments(0)


Dim strMacroName
strMacroName = Wscript.Arguments(1)

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone


Dim StrPathNameNew

' Open the Workbook specified on the command-line
Dim oWorkBook

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB )

'on error resume next
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

StrPathNameNew = replace(ucase(strWorkerWB),".XLS","") & "_" & year(Date()) & right("0" & month(date()),2) & right("0" & day(date()),2) & ".XLS"
oWorkBook.SaveAs StrPathNameNew
oWorkBook.Close

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

 
At November 02, 2009 7:39 PM, Anonymous Anonymous said...

You are the best .

 
At February 05, 2010 2:26 PM, Anonymous Anonymous said...

Thanks for the post. How to people debug their VBscripts? VS doesn't seem to do it. Do you guys use Microsoft Script Editor?

 
At February 05, 2010 2:55 PM, Blogger Kim Greenlee said...

Hi,

Well, it's been a very long time since I've done this work. But I think I used Visual Studio. I did a quick search and turned up this link http://support.microsoft.com/kb/308364.

Good luck,

Kim

 
At October 01, 2010 1:55 PM, Blogger Nestor Urquiza said...

Thanks for this post. It has helped me to achieve my goal for today in no time.

I added some code to manage parameters from VBS to Excel Subroutines. I have posted the code here

 
At November 15, 2010 12:02 PM, Blogger Alex said...

I was at the Inet when I unexpectedly lost my excel files. To my good fortune a friend rapidly suggested me a resolution. He was right, reason of it solved out my troubles for a minute and absolutely easily. What is more the utility may help in this trouble also - Microsoft Excel repair tool.

 
At December 08, 2010 9:00 AM, Anonymous Anonymous said...

Can your run a macro to update a pivot table that is importing data from SQL?

Everything seems to run except the refresh
Sub update()
'
' update Macro
' Macro recorded 12/8/2010 by John
'

'
Sheets("Summary").Select
Range("A6").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWindow.SmallScroll Down:=6
Range("A23").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A24").Select
End Sub

 
At May 04, 2011 11:20 AM, Anonymous Mathew said...

This is so cool... GREAT TIP!!!

 
At June 28, 2011 8:22 AM, Anonymous Toby said...

Regarding Tim P.'s comment about /x for command line parameters for Excel, he is correct, that is for Access. However, it can be done in Excel but it's not for beginners. Here is my post:
http://www.vbforums.com/showpost.php?p=4027694&postcount=13

The full thread:
http://www.vbforums.com/showthread.php?p=2234990

 
At August 25, 2011 2:41 AM, Anonymous Anonymous said...

Excel macro automation from the command line - brilliant.

 
At September 24, 2011 2:25 AM, Anonymous windows xp oem software said...

Earlier I thought differently, I thank for the help in this question.

 
At March 01, 2012 3:18 AM, Anonymous Anonymous said...

Hi,
my vbs script is running fine on my loacl mechine but it is not working on my server mechine
Mylocalmechine:Microsoft Windows XP [Version 5.1.2600]
my servermechine:Microsoft Windows [Version 6.1.7600]

below is the my vbs script
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.Application.AutomationSecurity = 1
oExcel.Application.ScreenUpdating = False
oExcel.Application.DisplayAlerts = False
Set objWorkbook = oExcel.Workbooks.Open("C:\anumala\test.xls")
oExcel.Run "RunMultiple"
objWorkbook.Save
objWorkbook.Close
Set objWorkbook = Nothing
Set oExcel = Nothing


Please help me

Thanks,
anumala

 
At April 10, 2012 5:55 AM, Anonymous James said...

Hi Everyone,

I'm bumping this thread back to life :)
Altho the original post is quite old the information in it is still verry valuable, and first off all i want to thank every single person who contributed to it, it helped me out sooo much!!

The only problem i still have is that when i run the vb script Kim made (your awsome for making it! :D )it opens excel on the background wich is good but it wont close automatically and save the changes that were made by executing the macro.
I hope someone is able to point me in the right direction.

Cheers, James

 
At April 10, 2012 10:36 PM, Blogger Kim Greenlee said...

Hi James,

I'm not going to have any time until May to investigate this for you.

I would suggest that you run your spreadsheet in the foreground to see if you get a clean shut down. If the shutdown is clean then I would suggest looking at error situations that might pop a window. Any dialog boxes that come up will make it impossible for the spreadsheet to run in the background. Microsoft states that even if you disable all the UI components, it doesn't mean the software won't try to start some.

In May I will be looking at productizing this solution. If you would like to reach out to me via my email address I would be interested in your thoughts.

Regards,

Kim

 
At April 11, 2012 3:16 AM, Anonymous James said...

Hi Kim,

Thanks for the reply on such a short notice!

I have been digging around and i found the answer to my problem, sadly enough it raised another one haha

the Set oWorkBook = Nothing part does not close down the Excel sheet as one should think.
I put a oWorkBook.Close after oWorkBook.Save and now it closes.

The sad part is that my Macro is not beeing excecuted. The macro needs to insert some text from .txt files into other workbooks and then merges the data in the main workbook. The nasty part is that i inherited the macro from former co-worker so i did not make it on my own, makes it harder to figure it out :(

 
At April 27, 2012 6:11 AM, Anonymous Anonymous said...

Kim,
I am not a programmer. I love this post but I only understand 50%.

I need the following and am willing to pay for it:
A scheduled script that calls an Excel Macro that saves the open workbook(s) in Excel (in both .xlsx and .csv unattended) every minute.
I cannot have the macro running continuously in Excel because it prevents my workbook from updating market prices.
Thanks, Kai
kai (at) ingwersen.com

 
At August 09, 2012 12:44 AM, Anonymous Denis said...

Thanks for this script -- very useful.
I also had trouble getting it to run the macro until I specified the full path to the vbs file, then it worked like a charm.

 
At December 04, 2012 3:33 PM, Anonymous Dan said...

Hello Kim of 2006, I am hoping your future self still remembers this awesome code block you gave us!

I am attempting to write a fully automated bat script which calls your vbs, in turn executing an excel macro on...wait for it.. a read only workbook that gets replaced daily beyond my control (so I can't save a macro in it). The macro simply needs to save certain columns to a new csv file (trivial).

I'm attempting to use the "Personal Macro Workbook" to save the macro such that it can be called on any other workbook including the read only one...Is this even possible, or should I forget it and use PowerShell with the OLEDB API instead?

Thanks again!
-Dan

 
At January 03, 2013 8:58 AM, Blogger Unknown said...

Dear Kim,
WOW, what talent!
Would it be possible for me to pay for your services?
I have an Excel App that, when opened, opens two other apps, each in separate Excel instances. I need to run the main app at 6:30 AM each trading day. Then I need to save and close down the operation at 4PM each day by sending me an email indicating how much money i made while i am at the beach or vacationing in Paris.
The software is a never before rendered version of a day-trading platform that can see into the future. Being able to automate this software is the only remaining task for its successful deployment. I am very impressed with your code and look forward to doing business with you.
Stephen Druley
staryman@att.net

 
At February 17, 2013 11:40 AM, Anonymous Anonymous said...

Excellent! This code worked like a charm for me. Thank you so much for sharing this. However, I am running into an issue. I have created a .bat file to run this VB Script which is called via Windows Task scheduler on Windows 2008. Works perfectly, other than after the .xlsm is updated with the data, EXCEL.exe process doesnt get closed. And as a result, the the spreadsheet remains locked. Is there a way to end the EXCEL process without having to kill it manually?

 
At March 22, 2013 2:54 AM, Anonymous Rudolf said...

The VB script on it self works perfect, but as a scheduled task the .xlsm file is not updated and the excel.exe and wscript tasks remains active. Is ther a solution?

 
At January 24, 2014 5:38 AM, Anonymous chandra gupt said...

Thanks Kim for this article. This is exactly what I was looking for. Instruction are very clear in the article.

 
At February 13, 2014 2:20 AM, Blogger Unknown said...

Hi,

can i show the standard excel user interface from the windows scheduler using any method or any other scheduling application?

thank in advance.

 
At April 23, 2014 9:30 PM, Anonymous Anonymous said...

Task Scheduler for Windows application

 
At July 10, 2014 10:22 AM, Anonymous Andy G said...

Just a tip if someone is having a problem executing the Macro. I changed the following

strMacroName = "'" & strPath & "\NAMI_Exception_Template.xls" & "!Sheet1.YourMacro"

TO

strMacroName = "ThisWorkbook.YourMacro"

and all worked well.

 
At November 07, 2014 1:01 PM, Anonymous Anonymous said...

Hi Kim, what is an excel controller? I know how to write vba in excel, but have never heard of an excel controller.

 
At May 23, 2015 4:01 AM, Anonymous Anonymous said...

Hi Kim,

Awesome stuff! We're well into 2015 by now so almost a decade went by and it's still very useful. I'm using your script to start-up a macro in an excel file that in turn runs a variety of reporting macro's. I'm more of a VBA-guy so I'm switching to VBA at this point, setting the macros and filepaths as variable strings which can be typed into a main worksheet).

When I set the vbs script to run from the Windows Task Scheduler, I got an error on the StrPath. Turns out this was due to the fact that the StrPath is based on the current directory of WshShell which is created "on-the-spot" and results in the StrPath being set to the c:\windows\system32 folder when ran from the Task Scheduler.

I've quick-fixed this by manually setting the CurrentDirectory of WshShell to the folder were the script and my excel file are located after the object is created:

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")


WshShell.CurrentDirectory = "C:\Users\Baas\Desktop\VBS test"

Since your post is still visited frequently, I thought it might be good to comment in case someone else bumps into a similar issue.

 
At April 10, 2017 7:31 AM, Anonymous Anonymous said...

Thank you for the post.. Really helpful. After reading the comments I fixed some of the small errors in the original script. I am putting the vbs file and the macro in the same folder. This is the script that works for me:

My Excel file: "MacroTeamspaceDashboard"
My macro name: "Create_CSV_Dashboard_WIP"

Please be careful in the " ' "

SCRIPT:

' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\MacroTeamspaceDashboard.xlsm"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & strPath & "\MacroTeamspaceDashboard'" & "!Create_CSV_Dashboard_WIP"

on error resume next
' Run the calculation macro
myExcelWorker.Run strMacroName
if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

oWorkBook.Save

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

 
At April 26, 2017 12:03 PM, Anonymous Anonymous said...

Hi all,

This post was super helpful!

I was having a problem with Excel staying open after the macro executed like several others have pointed out. The script would work just fine from the command line, but once in task scheduler, the first time it runs, excel would get left open, ruining future executions. I simply added "oWorkbook.Close" and that fixed it for me. Specifically,

These lines:
oWorkBook.Save

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing


Became this:
oWorkBook.Save

' Clean up and shut down
oWorkbook.Close
Set oWorkBook = Nothing

myExcelWorker.DefaultFilePath = strSaveDefaultPath


I don't think it matters, but just in case, I chose to use wscript as opposed to cscript because I don't like the console window popping up all the time. Hope this helps others experiencing similar struggles!

 
At October 10, 2017 7:51 AM, Anonymous Anonymous said...

Hi All,

I tried the code issued on April the 10th, 2017. I changed names for excel file and macro. I prepared a .bat file with the following code

cscript 10102017.vbs "C:\Users\Administrator\Desktop\vbs automat\Book1.xlsm"


and when I run it (a .bat file) it's not starting macro and not saving a file....
Could you please help!

s.

 
At October 10, 2017 8:30 AM, Anonymous Anonymous said...

Solved. Working!
s

 
At October 10, 2017 1:41 PM, Blogger Kim Greenlee said...

Glad to hear you got it working!

 
At January 02, 2018 12:13 AM, Anonymous Anonymous said...

Noob tip: I was testing my vbs file in cmd.exe & kept getting an error "Input Error: can not find script file C:\...\xxxxx.vbs". I realised that I had saved the file in notepad as xxxxx.vbs but the file type reverted to .txt. That is, my filename became xxxxx.vbs.txt

I solved this issue by changing the file type to 'All files' in the 'Save As' window and adding .vbs to the file name. the file then became xxxxx.vbs

This took me all day to figure out so hopefully this helps another idiot like me.

 
At June 07, 2018 5:35 AM, Anonymous Anonymous said...

Hello,

When i schedule it in the task scheduler, it throws up an error like not able to find the file or an expected end of statement

Here is the macro that i am trying to run :

Sub Report()

Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object


TempFilePath = Environ$("temp") & ""


If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
FileExtStr = ".xlsm": FileFormatNum = 52
End If


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")

mBody = "Please find attached order status report"




For Each sh In ThisWorkbook.Worksheets


If sh.Range("A1").Value Like "?*@?*.?*" Then


sh.Copy


Set wb = ActiveWorkbook


'Change all cells in the worksheet to values


With wb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells.PasteSpecial xlPasteFormats
End With


Application.CutCopyMode = False


TempFileName = "Sheet" & sh.Name & " of " _
& "2018 Daily Circulation Pending spares orders MS Service" & " " & Format(Now, "dd-mmm-yy h-mm-ss") ' change this for your file path


Set OutMail = OutApp.CreateItem(0)


With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum


On Error Resume Next
With OutMail
.To = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "Spare/Parts order status report"
.Body = "Please find the attached report"
.Attachments.Add wb.FullName


.Attachments.Add ("D:/utills/New sheet")
.send 'or use .Send 'displays email change to Send to send emails
End With
On Error GoTo 0


.Close savechanges:=False
End With


Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr


End If


Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

Here is the modified version of your code that i am using :

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.C:\Users\Admin\Desktop
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\2018 Daily Circulation Pending spares orders MS Service.xlsm"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "Report"
On Error Resume Next
' Run the calculation macro
myExcelWorker.Run strMacroName
If Err.Number <> 0 Then
' Error occurred - just close it down.
End If
Err.Clear
On Error GoTo 0

oWorkBook.Save

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
If myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

Please help me with setting the current strpath.

Forgive me for asking for so much help from you but i am total novice with excel VBAs.

KIndly help me with this

 
At May 31, 2019 7:26 AM, Blogger Unknown said...

This is helpful beyond belief. You have solved my problem with respect to queries that ran too long. Now I process them at an hour where no one is at work and in the morning the macro and subsequent query is run and ready to rock and roll. Thanks so much.

 
At February 09, 2021 10:49 AM, Anonymous Rob said...

Just posting to let you know that this post lives on and on. I'm going to take several bits and pieces from all the different code posted here to automate my file operations. Thanks all.

 

Post a Comment

<< Home