Introduction

When working with Excel files, you may need to extract data from one workbook and use it in another. This can be done manually by copying and pasting the data, but it can be time-consuming and error-prone, especially when dealing with large datasets. One way to automate this process is by using Visual Basic for Applications (VBA) to extract the data from one workbook and execute the VBA code from R.

In this blog post, I’ll walk you through the process of extracting data from another workbook using VBA and how to execute this from R. We’ll use the data in Sheet1 from an example workbook.

Extracting Data from Another Workbook Using VBA

Step 1: Setting Up the VBA Code

First, we need to write a VBA script that will open another workbook, extract data from Sheet1, and return this data. Here’s a simple VBA code to accomplish this:

Open the VBA editor by pressing Alt + F11.
Insert a new module by right-clicking on any existing module or the workbook name, then selecting Insert > Module.
Copy and paste the following VBA code into the module:

Sub ExtractData()
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Define the path to the source workbook
    Dim sourceFilePath As String
    sourceFilePath = "C:\Users\ssanders\Documents\GitHub\steveondata\posts\2024-06-19\random_data.xlsx" ' Change this to your actual file path

    ' Open the source workbook
    Set sourceWorkbook = Workbooks.Open(sourceFilePath)
    Set sourceSheet = sourceWorkbook.Sheets("Sheet1")
    Set sourceRange = sourceSheet.Range("A1:B30") ' Adjust the range as needed

    ' Open the target workbook
    Set targetWorkbook = ThisWorkbook
    Set targetSheet = targetWorkbook.Sheets("Sheet1")
    Set targetRange = targetSheet.Range("A1:B30") ' Adjust the range as needed

    ' Clear the target range before pasting
    targetRange.Clear

    ' Copy the data from source to target
    sourceRange.Copy Destination:=targetRange

    ' Close the source workbook without saving
    sourceWorkbook.Close SaveChanges:=False

    ' Save and close the target workbook
    targetWorkbook.Save
    targetWorkbook.Close SaveChanges:=True
    
    ' Quit Excel
    Application.Quit
End Sub

This script opens another workbook, copies the data from Sheet1, and pastes it into the current workbook’s Sheet1. Modify the sourceFilePath to the location of your source workbook and adjust the ranges as necessary. The data was already in a workbook and thus we knew the dimensions of the data.

Step 2: Executing the VBA Code from R

Now that we have the VBA code ready, let’s write some R code to execute this VBA macro. We’ll use the RDCOMClient package to interact with Excel from R.

Install the RDCOMClient package if you haven’t already:

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

Load the package and write the R code to run the VBA macro:

library(RDCOMClient)

# Path to your Excel workbook containing the VBA macro
excelFilePath <- "C:/Users/ssanders/Documents/GitHub/steveondata/posts/2024-06-19/get_data_from_another_workbook.xlsm"

# Create a COM object to interact with Excel
excelApp <- COMCreate("Excel.Application")

# Open the workbook
workbook <- excelApp$Workbooks()$Open(excelFilePath)

# Make Excel visible (optional)
excelApp[["Visible"]] <- FALSE

# Run the VBA macro
excelApp$Run("ExtractData")

NULL

# Close the workbook without saving
workbook$Close(FALSE)

[1] TRUE

# Quit Excel
excelApp$Quit()

NULL

# Release COM object
rm(excelApp, workbook)

This R script creates a COM object to interact with Excel, opens the workbook containing our VBA macro, runs the macro, and then quits Excel. Make sure to modify the excelFilePath to point to your actual workbook.

Now let’s see if it actually worked:

library(readxl)

f_path <- "C:/Users/ssanders/Documents/GitHub/steveondata/posts/2024-06-19/random_data.xlsx"
read_excel(f_path, sheet = "Sheet1", col_names = FALSE)

New names:
• `` -> `...1`

# A tibble: 30 × 1
     ...1
    <dbl>
 1 -0.371
 2 -1.00 
 3  0.226
 4 -0.323
 5 -0.142
 6  1.19 
 7 -0.827
 8  0.715
 9 -0.105
10 -1.06 
# ℹ 20 more rows

Conclusion

In this post, we’ve covered how to set up a VBA macro to extract data from another workbook and how to execute this macro from R using the RDCOMClient package. This approach allows you to leverage the power of VBA for Excel automation while controlling the process from R, providing a seamless integration between the two environments.

Feel free to adjust the VBA code and R script to suit your specific needs. Happy coding!

For more information on integrating R with other tools, check out my other posts at www.spsanderson.com/steveondata/.

If you have any questions or run into issues, don’t hesitate to reach out on LinkedIn or Mastodon. Let’s keep the conversation going!