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?

Hi Marek. To make things complicated I have both of course :-) — ddg
– ddg, Commented Oct 16, 2013 at 13:52

lebelinoz · Accepted Answer · 2017-04-05 05:24:54Z

16

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

answered Apr 5, 2017 at 5:24

lebelinoz

5,09611 gold badges39 silver badges61 bronze badges

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

6 Comments

lebelinoz Over a year ago

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

User981636 Over a year ago

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)

lebelinoz Over a year ago

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

User981636 Over a year ago

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

Nikhil Vidhani Over a year ago

@lebelinoz How will I pass arguments to macro (while calling from excel)?

|

Community · Accepted Answer · 2017-05-23 12:18:09Z

15

Write a VBscript wrapper that calls your VBA. See Way to run Excel macros from command line or batch file?
Run your VBscript via R's system or shell functions.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Oct 16, 2013 at 13:16

Richie Cotton

122k47 gold badges254 silver badges371 bronze badges

3 Comments

Marek Over a year ago

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

bschneidr Over a year ago

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

Bryan Shalloway Over a year ago

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.

Ryan Bradley · Accepted Answer · 2021-04-19 16:17:58Z

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)

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

Collectives™ on Stack Overflow

Run VBA script from R

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related