VBA: Saving and Closing a Workbook

code
rtip
operations
vba
excel
Author

Steven P. Sanderson II, MPH

Published

July 17, 2024

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.

Prerequisites

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.

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

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_app\(Workbooks()\)Open(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.

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!