Task scheduler + VB script to auto open excel

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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,994
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,994
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)
 

gutterball

New Member
Joined
May 7, 2014
Messages
1
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.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,994
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.
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
 

JuanP

New Member
Joined
Oct 13, 2017
Messages
1
Thanks a lot John_w works perfectly fine !!!!!!!!!! :)

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
 

Forum statistics

Threads
1,081,522
Messages
5,359,259
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top