CODEWITHSUNDEEP
excelvbavbscript
adrian
adrian

Reputation: 286

How to execute vbscript successfully?

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:

  1. open the excel file
  2. run the macro
  3. 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?

Upvotes: 1

Views: 348

Answers (1)

Omega73
Omega73

Reputation: 23

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.

Upvotes: 2

Related Questions