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?

Scant

VBA-code as Excel macro or VBScript? – Medarda

Hi Marek. To make things complicated I have both of course :-) – Scant

Answer 1

1. Write a VBscript wrapper that calls your VBA. See Way to run Excel macros from command line or batch file?

2. Run your VBscript via R's system or shell functions.

Waldman

Might be handy: shell(shQuote(normalizePath(path_to_vbs_file)), "cscript", flag = "//nologo"). – Medarda

Here's a way to run VBA from R, where you can specify inputs and arguments from within R rather tweaking the VBScript. – Quantic

Thanks, I put together a complete example (and associated blog with best practices) using this approach. See here: github.com/brshallo/macro-shell-example . Feel free to clone and can see macro enabled excel spreadsheet, wrapper VBScript and R script that kicks everything off via shell command etc. – Modesta

Answer 2

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()

Sydel

Credit where credit is due: here is the related question which inspired my answer. – Sydel

In my case, the line xlWbk <- xlApp$Workbooks()$Open("C:\\Temp\\macro_template.xlsm") yields an error Error in .COM(x, name, ...) : Cannot locate 0 name(s) Workbooks in COM object (status = -2147418111) – Craftwork

@user3507584 Is there a file called macro_template.xlsm in your C:\Temp folder? – Sydel

I am using the path to my own file. It is working now (maybe other application was calling it). – Craftwork

@Sydel How will I pass arguments to macro (while calling from excel)? – Sharyl

@Sydel You could either write the arguments to a flat file and read them in with VBA, or modify the spreadsheet directly through R. Example linked below. #34172853 – Anglicanism

Answer 3

I used RDCOM to run an Excel macro for months to pull data from SAP, and it started throwing an error today when attempting to quit the program. Not sure why. This was my solution, killing the task if a peaceful quit couldn't be achieved.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~ Define hard coded variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# Define paths to VBA workbooks and macro name
path_xlsb <- "I:/EXAMPLE_WORKBOOK.xlsb"
xlsb_macro_name <- "launch_SAP"

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~ Load or install packages
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# librarian
if(require(librarian) == FALSE){
  install.packages("librarian")
  if(require(librarian)== FALSE){stop("Unable to install and load librarian")}
}
librarian::shelf(tidyverse, readxl, RODBC, odbc,lubridate, pivottabler, xlsx, openxlsx, htmlTable)

# Load or install RDCOM Client 
if(require(RDCOMClient) == FALSE){
  install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")
  if(require(RDCOMClient)== FALSE){stop("Unable to install and load RDCOMClient")}
}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#~~ Run VBA Macro in Excel 
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# Kill any existing Excel processes
try(system("Taskkill /IM Excel.exe /F"),silent = TRUE)

# Kill any existing SAP processes (only relevant if you're working with SAP)
try(system("Taskkill /IM saplogon.EXE /F"),silent = TRUE)

# Open a specific workbook in Excel:
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(path_xlsb_reconnect)

# Set to true if you want to see your spreadsheet:
xlApp[['Visible']] <- TRUE 

# Run the macro 
Sys.sleep(2) # Wait for the workbook to load
xlApp$Run(xlsb_macro_name)

# Attempt to close the workbook peacefully  
Sys.sleep(2) # Wait for 2 seconds
try(xlApp$Quit())
try(rm(xlWbk, xlApp))
try(gc())

# Kill any Excel processes
try(system("Taskkill /IM Excel.exe /F"),silent = TRUE)

# Kill any existing SAP processes (only relevant if you're working with SAP)
try(system("Taskkill /IM saplogon.EXE /F"),silent = TRUE)

Anglicanism

Hi, I understand your code, but cannot get it to work for myself. Do you get data from SAP through Analysis add-in? – Nicely