Run VBA script from R

Question

I have to manage a workflow involving R-scripts and VBA-code. I would like to run the process in R (where most of my code is) and now and then to call VBA-code for specific calculation.

I would prepare the inputs for VBA in R, write somewhere the results (.csv, database) and then use the results in the rest of the R-script.

The best would be of course to move the whole code into R but this is for now not possible. The VBA-code is fairly complex. Translating this into R will be a challenging long-term task.

Is there any possibility to manage in R such a work-flow?

Answer 1

Here's a method which doesn't require a VBscript wrapper. You'll need to install the RDCOMClient package

library(RDCOMClient)  # Open a specific workbook in Excel: xlApp <- COMCreate("Excel.Application") xlWbk <- xlApp$Workbooks()$Open("C:\\Temp\\macro_template.xlsm")  # this line of code might be necessary if you want to see your spreadsheet: xlApp[['Visible']] <- TRUE   # Run the macro called "MyMacro": xlApp$Run("MyMacro")  # Close the workbook and quit the app: xlWbk$Close(FALSE) xlApp$Quit()  # Release resources: rm(xlWbk, xlApp) gc()