26

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?

0m3r
  • 12,286
  • 15
  • 35
  • 71
ddg
  • 2,493
  • 2
  • 20
  • 23

3 Answers3

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()
lebelinoz
  • 4,890
  • 10
  • 33
  • 56
  • 2
    Credit where credit is due: [here is the related question](http://stackoverflow.com/questions/43095645/how-to-export-an-excel-sheet-range-to-a-picture-from-within-r) which inspired my answer. – lebelinoz Apr 05 '17 at 05:26
  • 1
    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)` – user3507584 Jul 10 '17 at 09:56
  • @user3507584 Is there a file called `macro_template.xlsm` in your `C:\Temp` folder? – lebelinoz Jul 10 '17 at 10:29
  • I am using the path to my own file. It is working now (maybe other application was calling it). – user3507584 Jul 10 '17 at 10:30
  • @lebelinoz How will I pass arguments to macro (while calling from excel)? – Nikhil Vidhani Dec 27 '17 at 18:45
  • @lebelinoz 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. https://stackoverflow.com/questions/34172353/how-do-i-modify-an-existing-a-sheet-in-an-excel-workbook-using-openxlsx-package – Ryan Bradley Apr 19 '21 at 16:12
15

  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.

Community
  • 1
  • 1
Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
  • 5
    Might be handy: `shell(shQuote(normalizePath(path_to_vbs_file)), "cscript", flag = "//nologo")`. – Marek Oct 16 '13 at 15:11
  • [Here's a way](https://stackoverflow.com/questions/45622497/how-to-run-a-vbs-script-from-r-while-passing-arguments-from-r-to-vbs) to run VBA from R, where you can specify inputs and arguments from within R rather tweaking the VBScript. – bschneidr Jun 07 '19 at 15:51
  • Thanks, I put together a complete example (and associated blog with best practices) using this approach. See here: https://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. – Bryan Shalloway May 13 '21 at 19:17
1

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)
Ryan Bradley
  • 627
  • 6
  • 9
  • Hi, I understand your code, but cannot get it to work for myself. Do you get data from SAP through Analysis add-in? – Miha Trošt Nov 16 '21 at 14:06