Task scheduler + VB script to auto open excel

vbacoder

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

I would like to hear ffrom anyone who has written a VB script that can be fed into Task Scheduler in order to open and close applications automatically.

What I want to do is use Task Scheduler with a script to open an excel spreadsheet, run a macro within, and subsequently close the sheet.

Has anyone tried anything like this before?

Thanks,

vcoder
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Here's an example. Save the following as myScript.vbs:

Code: 
Dim args, objExcel

Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "AddTimeInColumn", args(1)

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
The script opens the Workbook specified in the first argument and calls the AddTimeInColumn subroutine with the column specified in the second argument. That's just an example of using arguments in scripts and passing them to Excel subroutines. You could even specify the subroutine name itself as a script argument instead of hard-coding it.

Insert the following code in a Module in Excel and save it as Book1.xls
Code: 
Public Sub AddTimeInColumn(column As String)

    Dim LastRowInColumn As Long
    
    LastRowInColumn = Cells(Cells.Rows.Count, column).End(xlUp).Row
    
    'If column is completely blank, need to adjust last row
    If Cells(LastRowInColumn, column).Value = "" Then LastRowInColumn = 0
    
    'Insert time in next row
    Cells(LastRowInColumn + 1, column).Value = Time()

End Sub
This simply inserts the current time in the next free cell in the specified column.

To test, run the VBScript from the folder where you saved it using:

cscript myScript.vbs "C:\temp\Book1.xls" A

Any arguments with spaces in them must be enclosed by double quotes. For the scheduled task, you would specify the full path to the script, e.g.:

cscript "C:\temp\myScript.vbs" "C:\temp\Book1.xls" B
 
Upvote 0
Hi John,

Thank you very much indeed for this example! I will try it and report back on how it went.

Your expanation was very clear!

Thanks,

vcoder
 
Upvote 0
Hi John,

I tried the code and it worked. I couldn't check if the macro ran successfully because it didn't make any changes to the sheet. When I adapted it to run a macro that inserted text into a cell, he macro didn't run. I ran using ms-dos prompt, and the error message was as follows:

"Error: subscript out of range"

Below is the code in the macro:
Code: 
Sub enter_text()

Sheets("Sheet1").Select
    Range("A1").Value = "Test"

End Sub


The code in the script file is as follows:
Code: 
Dim args, objExcel

Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "enter_text", args(1)

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

I've tried to debug to get an insight into what is going wrong, but I just haven't been able to figure it out.

vcoder
 
Upvote 0
The error occurs because your enter_text subroutine does not have any parameters. The correct way to call it from the VBScript file is therefore:

objExcel.Run "enter_text"

BTW although I was able to spot that easily, it helps if you post the full error message from running the script as it contains the line number and character number on that line where the error occurred:

C:\temp\myscript.vbs(9, 1) Microsoft VBScript runtime error: Subscript out of range

Debugging VBScript is difficult without a 3rd party editor like Vbsedit, or some versions of Visual Studio. The best way without these is to put wscript.echo statements in the code, like:

wscript.echo "1st arg:" & args(0)
 
Upvote 0
Can somebody expand upon how and where I specify the name and location of the workbook I want to have run by the .vbs document.
 
Upvote 0
gutterball said:
Can somebody expand upon how and where I specify the name and location of the workbook I want to have run by the .vbs document.
Click to expand...
It is specified on the cscript command line.

For a Scheduled Task, enter the program to run as C:\Windows\system32\cscript.exe and in the Advanced Properties change the Run parameter to include the folder path and file name of the .vbs file and the workbook and any parameter(s) required by the macro:

C:\Windows\system32\cscript.exe "C:\temp\myScript.vbs" "C:\temp\Book1.xls" B
 
Upvote 0
Thanks a lot John_w works perfectly fine !!!!!!!!!! :)

John_w said:
It is specified on the cscript command line.

For a Scheduled Task, enter the program to run as C:\Windows\system32\cscript.exe and in the Advanced Properties change the Run parameter to include the folder path and file name of the .vbs file and the workbook and any parameter(s) required by the macro:

C:\Windows\system32\cscript.exe "C:\temp\myScript.vbs" "C:\temp\Book1.xls" B
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
407
nevyoung
N
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
216
Peter_SSs
Peter_SSs
J
  • Question Question
New Laptop - VBA Script will not run using VBS file manually or via Task Scheduler
Replies
0
Views
447
jaihawk8
J
W
  • Question Question
Excel VBA/Macro Help
Replies
2
Views
360
Walker_Ice
W
D
  • Question Question
Saving workbook "hides" macros
Replies
2
Views
707
dotsent
D

Forum statistics

Threads
1,224,051
Messages
6,176,086
Members
452,704
Latest member
Michael AA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top