Excel macro loop using VBS

Question

I would like to run a VBA macro named MyMacro, which is saved as MyMacro.bas for many excel files. I have the VBS code below, but it is not doing what I want. I would really appreciate if somebody could take a look at it.

I am using Excel 2013. The files are saved as .xls.

Thank you.

Const sRootFolder = "C:\Documents"
Const sExportedModule = "C:\Documents\MyMacro.bas"
Const sMacroName = "MyMacro"

Dim oFSO, oFDR, oFile ' File and Folder variables
Dim oExcel, oWB ' Excel variables (Application and Workbook)

Start
'------------------------------
Sub Start()
    Initialize
    ProcessFilesInFolder sRootFolder
    Finish
End Sub
'------------------------------
Sub ProcessFilesInFolder(sFolder)
    ' Process the files in this folder
    For Each oFile In oFSO.GetFolder(sFolder).Files
        If IsExcelFile(oFile) Then ProcessExcelFile oFile.Path
    Next
End Sub
'------------------------------
Sub Initialize()
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oExcel = CreateObject("Excel.Application")
End Sub
'------------------------------
Sub Finish()
    oExcel.Quit
    Set oExcel = Nothing
    Set oFSO = Nothing
End Sub
'------------------------------
Function IsExcelFile(oFile)
    IsExcelFile = (InStr(1, oFSO.GetExtensionName(oFile), "xls", vbTextCompare) > 0) And (Left(oFile.Name, 1) <> "~")
End Function
'------------------------------
Sub ProcessExcelFile(sFileName)
    On Error Resume Next
    wscript.echo "Processing file: " & sFileName ' Comment this unless using cscript in command prompt
    Set oWB = oExcel.Workbooks.Open(sFileName)
    oWB.VBProject.VBComponents.Import sExportedModule
    oExcel.Run sMacroName
    oWB.Save
    oWB.Close
    Set oWB = Nothing
End Sub
'------------------------------

Here is a vbs code for a single file which works:

Option Explicit

ExcelMacroExample
Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 
  Dim objWorkbook  

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("C:\Documents\test.xls", 0, True) 
  Set objWorkbook = xlApp.Workbooks.Open("C:\Documents\test.xls")  

  xlApp.Run "MyMacro"
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 
End Sub

Would you tell us what it is doing vs what you expect it to do? — A.S.H
– A.S.H, Commented May 5, 2017 at 3:20
Try putting some MsgBox 12345 at the beginning of the macro to see if it is invoked. — A.S.H
– A.S.H, Commented May 5, 2017 at 3:25
Weird, this popup doesn't appear without this additional line?? — A.S.H
– A.S.H, Commented May 5, 2017 at 3:41
Have you tried changing Const sMacroName = "MyMacro" to Const sMacroName = "MyMacro.MyMacro"? (I had something happen to me this morning where my sub and module both had the same name, and it got confused.) — YowE3K
– YowE3K, Commented May 5, 2017 at 4:00
Oh - I just tried it and that resolved the problem for my test. :( — YowE3K
– YowE3K, Commented May 5, 2017 at 4:06

AK88 · Accepted Answer · 2017-05-05 08:12:01Z

0

I finally got it working:

Const sRootFolder = "C:\Documents"
Const sExportedModule = "C:\Documents\MyMacro.bas"
Const sMacroName = "Trip"

Dim oFSO, oFile ' File and Folder variables
Dim xlApp, xlBook, objWorkbook 

Start

Sub Start()
    Initialize
    ProcessFilesInFolder sRootFolder
    Finish
End Sub

Sub ProcessFilesInFolder(sFolder)
    ' Process the files in this folder
    For Each oFile In oFSO.GetFolder(sFolder).Files
        If IsExcelFile(oFile) Then ProcessExcelFile oFile.Path
    Next
End Sub

Sub Initialize()
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set xlApp = CreateObject("Excel.Application")   
End Sub

Sub Finish()
    xlApp.Quit
    Set xlBook = Nothing 
    Set xlApp = Nothing    
    Set oFSO = Nothing
End Sub

Function IsExcelFile(oFile)
    IsExcelFile = (InStr(1, oFSO.GetExtensionName(oFile), "xls", vbTextCompare) > 0) And (Left(oFile.Name, 1) <> "~")
End Function

Sub ProcessExcelFile(sFileName)
    wscript.echo "Processing file: " & sFileName ' Comment this unless using cscript in command prompt    
    Set xlBook = xlApp.Workbooks.Open(sFileName, 0, True) 
    Set objWorkbook = xlApp.Workbooks.Open(sFileName)     
    objWorkbook.VBProject.VBComponents.Import sExportedModule
    xlApp.Run sMacroName
End Sub

Also, make sure that Trust access to the VBA project object model enabled. I certainly may be wrong, but the game changer here seems to be this piece:

Set objWorkbook = xlApp.Workbooks.Open(sFileName)

edited May 5, 2017 at 8:12

answered May 5, 2017 at 5:55

AK88

3,0262 gold badges16 silver badges35 bronze badges

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

3 Comments

A.S.H Over a year ago

It would be more useful to the site's users if you specify what was going wrong and how you corrected it. If there was no change in the code but only the Trust Access issue, no need to post the code again?

AK88 Over a year ago

I would love to do that. I am still trying to figure out what was going wrong myself :) In the Sub ProcessExcelFile(sFileName) I tried to do the same as in the working example for a single file and then it worked. Will def get back if I will have anything meaningful to add.

AK88 Over a year ago

I started getting Programmatic access to VB Project is not trusted message only after modifications.

Collectives™ on Stack Overflow

Excel macro loop using VBS

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related