VBA Script

First, let’s create a simple VBA script that saves and closes a workbook. Here’s the VBA code:

Sub SaveAndCloseWorkbook()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    wb.Save
    wb.Close
End Sub

Explanation:

• Sub SaveAndCloseWorkbook(): This line starts the subroutine named SaveAndCloseWorkbook.
• Dim wb As Workbook: This declares a variable wb as a Workbook object.
• Set wb = ThisWorkbook: This sets wb to refer to the workbook where the VBA code is running.
• wb.Save: This saves the workbook.
• wb.Close: This closes the workbook.

Calling VBA from R

Now, let’s see how you can call this VBA script from R using the RDCOMClient package. This package allows R to interact with COM objects, such as Excel.

library(RDCOMClient)

# Create a new Excel application
excel_app <- COMCreate("Excel.Application")

# Make the Excel application visible
excel_app[["Visible"]] <- TRUE

# Open an existing workbook or create a new one
workbook_path <- "C:/path/to/your/workbook.xlsx"
wb <- excel_app$Workbooks()$Open(workbook_path)

# Run the VBA macro
excel_app$Run("SaveAndCloseWorkbook")

# Quit the Excel application
excel_app$Quit()

# Release the COM object
rm(excel_app)
gc()

Explanation:

• library(RDCOMClient): Loads the RDCOMClient library to interact with COM objects.
• *excel_app <- COMCreate(“Excel.Application”)**: Creates a new Excel application instance.
• excel_app[[“Visible”]] <- TRUE: Makes the Excel application visible (optional).
• workbook_path: Path to your Excel workbook.
• wb <- excel_appOpen(workbook_path): Opens the workbook.
• excel_app$Run(“SaveAndCloseWorkbook”): Runs the VBA macro SaveAndCloseWorkbook.
• excel_app$Quit(): Quits the Excel application.
• rm(excel_app) and gc(): Releases the COM object and performs garbage collection to free up memory.