Run VBA code for multiple Excel files in R

Question

Having looked at these topics

• Launch VBA macro from R
• Run VBA script from R
• Way to run Excel macros from command line or batch file?

I was able to replicate the process. Now I want to apply this for various Excel files located in my working directory.

I figured that in order to be able to run the VBA code from R the object file should contain the macro. However, I simply cannot save .bas file (which has lines of VBA code) in each of the Excel files in order to run it automatically via R. How do I go about it?

Thank you.

UPDATE

Came across the following vb script in a different topic:

Const sRootFolder = "C:\Billing\Import"
Const sExportedModule = "C:\Test\Module1.bas"
Const sMacroName = "MACRO"

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
    ' Recurse all sub-folders from this folder
    For Each oFDR In oFSO.GetFolder(sFolder).SubFolders
        ProcessFilesInFolder oFDR.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.Close
    Set oWB = Nothing
End Sub
'------------------------------

But its not executing the macro ...

Answer 1

R code that executes this particular task is as follows:

shell(shQuote(normalizePath("C:/Documents/VBS.vbs")), "cscript", flag = "//nologo")

And VB script can be found in this topic.