Calling VBA macro from .vbs file throws 800A03EC error

0
votes

I'm trying to run a VBA macro through .VBS file(File name: Check_final.vbs). Here is the code

Option Explicit

run_macro

Sub run_macro()
    Dim xl1
    Dim sCurPath
    Dim xlBook
    Dim FolderFromPath
    Set xl1 = CreateObject("Excel.application")

    sCurPath =Wscript.ScriptFullName                                                                            
    Set xlBook = xl1.Workbooks.Open(sCurPath, 0, True)
    xl1.DisplayAlerts = False
    FolderFromPath = Left(sCurPath, InStrRev(sCurPath, "\"))
    xl1.Application.run FolderFromPath & "Changed_chk.xlsm!Check"

    Set xlBook = Nothing
End Sub

When I run this .vbs file I get this popup 'Changed_chk.xlsm is locked for editing' with Read only and notify options. If I acknowledge it with either Read only or notify option a excel sheet is opened in the name of Check_final (which is the file name of that .vbs file) and the above mentioned code is shown written in that excel file. Then I get a Windows script host error(code: 800A03AC) saying macro may not be available or all macro's are disabled.(Though I have enabled the macro as mentioned here.[http://www.addictivetips.com/windows-tips/enable-all-macros-in-excel-2010/)]. Any help on this is much appreciated. Thanks in advance.

3
vbaexcelvbscript
You are getting Read only and notify options because you didn't close the file and release your objects correctly the first time. Open task manager and kill all instances of excel. But before you do that close and save any open instances. Next Close the Excel file in the code properly and release your objects – Siddharth Rout

3 Answers

2
votes

You open your vbs-file instead of your excel-file... Also make sure that your function/sub is public. In the example below, I created a Public Sub Check in the module "YourModuleName", which I call from the vbs-file.

Option Explicit

run_macro

Sub run_macro()
    Dim xl1
    Dim xlBook
    Dim FolderFromPath
    Set xl1 = CreateObject("Excel.application")

    FolderFromPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "") 
    set xlBook = xl1.Workbooks.Open(FolderFromPath & "Changed_chk.xlsm")
    xl1.Application.run "'" & xlBook.Name & "'!YourModuleName.Check"
    xl1.Application.Quit
End Sub
1
votes

Try this simple code (UNTESTED)

Dim oXlApp, oXLWb, sCurPath

Set oXlApp = CreateObject("Excel.application")

sCurPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

Set oXLWb = oXlApp.Workbooks.Open(sCurPath & "Changed_chk.xlsm")

oXlApp.DisplayAlerts = False

oXlApp.Run "Check"

'~~> Close the file here. Save or discard the changes as per your requirement 
'oXLWb.Close (True)
'oXLWb.Close (False)
oXLWb.Close
oXlApp.Quit

Set oXLWb = Nothing
Set oXlApp = Nothing

Also where is your macro? In a sheet or in a module? You may want to see THIS

0
votes

I think there may be something wrong with calling the run_macro statement. From the test i created in excel VBA there is an error if i try to call the sub outside of another sub

Option Explicit
test

Sub test()
    MsgBox ("Test")
End Sub

I think you may want to

Option Explicit
Sub Start
    run_macro
End Sub

Sub run_macro()
    'code here
End Sub

or remove the run_macro line altogether?