Run VBA-script from a different Excel file using VBscript

Question

I use this code to search through a folder, finding all the excel file(with the same extension), run a VBA script from an opened excel file and save it without prompting.

strPath = "my path"
pathName="xlsx"

if strPath = "" then Wscript.quit
if pathName = "" then Wscript.quit

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xlsx" Then
   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)

   Set objWorksheet = objWorkbook.WorkSheets(1)
   objworksheet.Activate

objExcel.Application.Run "'filename and in quote because there is space.xlsm'!TestingMacro"


 objWorkbook.saveas(objFile.Path)
   objWorkbook.Close True 'Save changes
End If

Next

objExcel.Quit

However, everytime I run it, it just gives me an runtime error 800A03EC on line objExcel.Application.Run. So wat could I do to resolve it?

Thanks!

You should open the file with the macro in the instance of Excel you create before calling any code in it. — Tim Williams
– Tim Williams, Commented Jun 26, 2013 at 21:27

Ansgar Wiechers · Accepted Answer · 2013-06-26 21:26:04Z

3

The workbook containing the macro must be opened before you can run macros from it. Open the macro workbook with its full path, but run the macro with just the workbook and macro name.

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wbm = xl.Workbooks.Open("C:\path\to\macro workbook.xlsm")

Set fso = CreateObject("Scripting.FileSystemObject")

For Each f In fso.GetFolder("C:\some\where").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsx" Then
    Set wb = xl.Workbooks.Open(f.Path)

    Set ws = wb.Sheets(1)
    ws.Activate

    xl.Application.Run "'macro workbook.xlsm'!TestingMacro"

    wb.Save
    wb.Close
  End If
Next

wbm.Close

xl.Quit

answered Jun 26, 2013 at 21:26

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TitanTheYaphet Over a year ago

Yes it worked. Thanks! I had it open before. I just didn't know that I need to open the Macro file in the same instance.

Eddie Martinez · Accepted Answer · 2013-11-20 20:40:01Z

you are trying to run the macro from your personal workbook it might not work as opening an Excel file with a VBScript doesnt automatically open your PERSONAL.XLSB. you will need to do something like this:

Dim oFSO
Dim oShell, oExcel, oFile, oSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")
Set oExcel = CreateObject("Excel.Application")
Set wb2 = oExcel.Workbooks.Open("C:\..\PERSONAL.XLSB") 'Specify foldername here

oExcel.DisplayAlerts = False


For Each oFile In oFSO.GetFolder("C:\Location\").Files
    If LCase(oFSO.GetExtensionName(oFile)) = "xlsx" Then
        With oExcel.Workbooks.Open(oFile, 0, True, , , , True, , , , False, , False)



            oExcel.Run wb2.Name & "!modForm"


            For Each oSheet In .Worksheets



                oSheet.SaveAs "C:\test\" & oFile.Name & "." & oSheet.Name & ".txt", 6


            Next
            .Close False, , False
        End With
    End If



Next
oExcel.Quit
oShell.Popup "Conversion complete", 10

So at the beginning of the loop it is opening personals.xlsb and running the macro from there for all the other workbooks. Just thought I should post in here just in case someone runs across this like I did but cant figure out why the macro is still not running.

J P · Accepted Answer · 2013-06-26 20:47:44Z

-1

You may need to run each excel file in the objFolder directory in a new instance of excel.

strPath = "my path"
pathName="xlsx"

if strPath = "" then Wscript.quit
if pathName = "" then Wscript.quit

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files
    If objFso.GetExtensionName (objFile.Path) = "xlsx" Then

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
        objExcel.DisplayAlerts = False

        Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
        Set objWorksheet = objWorkbook.WorkSheets(1)
        objworksheet.Activate

        objExcel.Application.Run "'filename and in quote because there is space.xlsm'!TestingMacro"

        objWorkbook.saveas(objFile.Path)
        objWorkbook.Close True 'Save changes
        objExcel.Quit

    End If
Next

answered Jun 26, 2013 at 20:47

J P

3211 gold badge2 silver badges8 bronze badges

3 Comments

TitanTheYaphet Over a year ago

Sorry man but it still gives me error on the line "objExcel.Application.Run".

Andy G Over a year ago

I don't think it should be necessary to create separate Excel instances. I think - but I haven't been able to test - that you need to include the full path to the macro when running this from vbscript: Application.Run ("'C:\somefolder\macro1.xlsm'!macro_name")

Ansgar Wiechers Over a year ago

It's the opposite. You must call the macro without the full path, but you must open the workbook containing the macro first. And yes, it's definitely not necessary to create multiple Excel instances.

Collectives™ on Stack Overflow

Run VBA-script from a different Excel file using VBscript

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related