Run VBA script from R

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.

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

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 namepath_xlsb <- "I:/EXAMPLE_WORKBOOK.xlsb"xlsb_macro_name <- "launch_SAP"#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~#~~ Load or install packages#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~# librarianif(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 processestry(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 loadxlApp$Run(xlsb_macro_name)# Attempt to close the workbook peacefully  Sys.sleep(2) # Wait for 2 secondstry(xlApp$Quit())try(rm(xlWbk, xlApp))try(gc())# Kill any Excel processestry(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)