How to execute vbscript successfully?

Question

I have an excel file that contains a macro. The macro is written in ThisWorkbook of the developer tab. i want to automatically run the macro by scheduling it in the windows task scheduler.

I did some research and found out i have to write a vbscript for this and run the vb script in order to run the macro.

This is the vb script. The vbscript should:

open the excel file
run the macro
close the excel file

This should be done automatically at a scheduled time, daily by using the windows task scheduler.

So far this is the vb script:

'Script to start my filter.xls program with named macro
'MsgBox "In RunExcel"

' 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

' Open the Workbook 
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = "C:\Users\Desktop\service calibration details\CC.xlsm"

'MsgBox "Opening filter"

on error resume next 
Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
if err.number <> 0 Then
    ' Error occurred - just close it down.
    MsgBox "open Error occurred"
End If
err.clear
on error goto 0 

'MsgBox "Running macro"

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

' Clean up and shut down
Set oWorkBook = Nothing
myExcelWorker.Quit
Set myExcelWorker = Nothing
Set WshShell = Nothing

I tried to run this using microsoft windows based script host. But i get the error "run macro error occurred".

I searched on the internet. Yet i could not find a solution to this.

What is causing this problem?

Is there an error in the vbscript I have written?

How do i perform this successfully?

"error occurred" message has no diagnostic worth. Add & " 0x" & Hex(Err.Number) & " " & CStr(Err.Number) & " " & Err.Description, then you could get more info to find a culprit. Hint: the Run method starts a program running in a new Windows process... — JosefZ
MsgBox "bla-bla Error occurred" & " 0x" & Hex(Err.Number) & " " & CStr(Err.Number) & " " & Err.Description. For all bla-bla: run macro, open, ... — JosefZ

Omega73 Omega73 · Accepted Answer · 2015-04-02T08:52:57

As an alterntive approach to this problem, you could just have a simple VB script file to open the spreadsheet. Something like this:

Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "C:\Users\Desktop\service calibration details\CC.xlsm"
xl.Application.Visible = True

Set xl = Nothing

Then place your excel macro into the Workbook_Open sub so that it executes when the workbook is opened. At the end of your macro add the lines:

ActiveWorkbook.Close False 'false prevents it from saving any changes
Application.Quit

or if you want to save

ActiveWorkbook.Save
Application.Quit

This should hopefully do the trick! Best of luck.

How to execute vbscript successfully?

1 Answers