Task scheduler + VB script to auto open excel

[vbacoder]

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]

Here's an example. Save the following as myScript.vbs:

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

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]

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]

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:

Sub enter_text()

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

End Sub

The code in the script file is as follows:

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]

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]

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]

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]

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