< section id="introduction" class="level1">

Introduction

In this tutorial, you’ll learn how to save and close an Excel workbook using VBA (Visual Basic for Applications) and then doing it from R. We’ll create a simple VBA script that saves and closes a workbook, and then we’ll call this script from R using the RDCOMClient package.

< section id="prerequisites" class="level1">

Prerequisites

< section id="vba-script" class="level2">

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

< section id="explanation" class="level2">

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.

< section id="calling-vba-from-r" class="level2">

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.

< section id="step-by-step-r-code" class="level4">

Step-by-Step R Code

1. Install RDCOMClient: If you haven’t installed it yet, you can do so from the R console.

   install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")

2. Write the R Code: Here’s the R script to run the VBA code.

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

< section id="explanation-1" class="level2">

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.

< section id="try-it-yourself" class="level1">

Try It Yourself

This example gives you a starting point to work with VBA and R together. Try modifying the VBA code to suit your needs, like adding more functionalities or handling different scenarios. Experimenting with this setup will give you a better understanding of how to automate Excel tasks from R.

---

Happy coding!