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:

1. Open the VBA editor by pressing Alt + F11.
2. Insert a new module by right-clicking on any existing module or the workbook name, then selecting Insert > Module.
3. 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.

1. Install the RDCOMClient package if you haven’t already:

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

2. 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