CODEWITHSUNDEEP
rvbaworkflow
ddg
ddg

Reputation: 2523

Run VBA script from R

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?

Upvotes: 27

Views: 37657

Answers (3)

Ryan Bradley
Ryan Bradley

Reputation: 807

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)

Upvotes: 1

lebelinoz
lebelinoz

Reputation: 5068

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

Upvotes: 16

Richie Cotton
Richie Cotton

Reputation: 121057

  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.

Upvotes: 15

Related Questions