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, May 05 '17 at 03:20
I am not entirely sure, but I think the code is opening the excel files, but not linking/running the macro. I want to run the macro for each file in the folder. — AK88, May 05 '17 at 03:22
Try putting some `MsgBox 12345` at the beginning of the macro to see if it is invoked. — A.S.H, May 05 '17 at 03:25
Adding `MsgBox 12345` to my macro resulted in pop-up window through WSH (although I am running the VBS thru `cmd`) with `Processing file: FileName` — AK88, May 05 '17 at 03:37
Weird, this popup doesn't appear without this additional line?? — A.S.H, May 05 '17 at 03:41
It may be an idea to copy the code for `MyMacro` into the question. — YowE3K, May 05 '17 at 03:42
And then what happens after that popup? how many times does it appear? We need details, help us help you. — A.S.H, May 05 '17 at 03:46
@YowE3K do you think there is a problem with macro code? I am including a vb script that perfectly executes the macro — AK88, May 05 '17 at 03:46
@A.S.H sure. The popup appears only once for each file in the folder. The message is the same (obviously, with different names of the file) — AK88, May 05 '17 at 03:48
Okay, now try adding `oExcel.visible = True` at the end of `Initialize` and observe what is happening in Excel. — A.S.H, May 05 '17 at 03:50
I see an Excel workbook full of grey color - no grids, no cells. The popup message is the same... — AK88, May 05 '17 at 03:54
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, May 05 '17 at 04:00
Oh - I just tried it and that resolved the problem for my test. :( — YowE3K, May 05 '17 at 04:06
What @YowE3K noticed is relevant in any case. **Dont** use the same name for the macro and the module. Change one of them, regardless whether it will solve the problem. — A.S.H, May 05 '17 at 04:32
Did you change the subroutine name, or the module name? And did you remove the incorrect module from the workbooks that it had been placed into from the failed attempts? (E.g. If I left the subroutine name unchanged, but used a new module name, the code still wouldn't work because the `MyMacro` module still existed in the workbooks.) — YowE3K, May 05 '17 at 04:54
@YowE3K the thing is these files do not contain `MyMacro.bas` file. I presume that is why `oExcel.Run sMacroName` is failing. — AK88, May 05 '17 at 04:56
My tests (with the sub and mod having the same names) successfully imported the module, and saved the workbook with the module, but just didn't run the sub. Check one of the workbooks that was processed and see if it now has the module in there. — YowE3K, May 05 '17 at 04:57
Randomly checked several files - none of them has imported `MyMacro.bas` file. Which version of Excel are you using? I have `Excel 2013` and the files are `97-2003 xls` files. — AK88, May 05 '17 at 05:03
@YowE3K did you save the test files as Excel 2003, with extension `.xls`? — A.S.H, May 05 '17 at 05:03
I was using Excel 2010, and dummied the code to just work on one specific file which I had saved as `Book3.xls` in 97-2003 format so that it could save with macros. (So saved the file initially with no macros then, after the first test it had a `MyMacro` module, and then after another test loading `MyMacro.Bas` it had an additional `MyMacro1` module - VBA obviously doesn't like loading the same module name twice so it renames things a bit.) — YowE3K, May 05 '17 at 05:04
'Fraid I have to go home from work now, so I will check in again in about 90 minutes time to see how things are progressing. — YowE3K, May 05 '17 at 05:06

AK88 · Accepted Answer · 2017-05-05T08:12:01.023

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)

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? — A.S.H, May 05 '17 at 05:59
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, May 05 '17 at 06:07
I started getting `Programmatic access to VB Project is not trusted` message only after modifications. — AK88, May 05 '17 at 06:10

Excel macro loop using VBS

1 Answers1

Linked