Introduction

Welcome back to our series where we explore the synergy between R and VBA! Today, we’re diving into the FileDateTime function in VBA and how you can leverage it within R. This function is incredibly useful for anyone dealing with files, as it allows you to get the date and time when a file was last modified.

What is FileDateTime?

The FileDateTime function in VBA returns the date and time when a file was last modified. This can be particularly useful in various scenarios, such as tracking changes, logging file activities, or just keeping records up-to-date.

Examples

Basic Usage of FileDateTime in VBA

Let’s start with a simple example of how to use FileDateTime in VBA. Suppose you have a file located at C:\example\myfile.txt. Here’s how you can get its last modified date and time:

Sub GetFileDateTime()
    Dim filePath As String
    Dim fileModifiedDate As String

    filePath = "C:\example\myfile.txt"
    fileModifiedDate = FileDateTime(filePath)

    MsgBox "The file was last modified on: " & fileModifiedDate
End Sub

In this script: – filePath stores the path to the file. – fileModifiedDate gets the last modified date and time using FileDateTime. – MsgBox displays the result in a message box.

Executing VBA from R

To execute VBA code from R, you can use the RDCOMClient package, which allows R to interact with COM objects like Excel. Below is a step-by-step guide on how to achieve this:

1. Install and Load the RDCOMClient Package

   First, ensure you have the RDCOMClient package installed. If not, you can install it from CRAN:

install.packages("RDCOMClient")

Then, load the package:

library(RDCOMClient)

2. Create a VBA Macro in Excel

   Open Excel and press ALT + F11 to open the VBA editor. Create a new module and paste the GetFileDateTime function code. Save the Excel workbook with a .xlsm extension to enable macros.

3. Run the VBA Macro from R

   Now, let’s write an R script to open the Excel workbook and run the macro:

library(RDCOMClient)

# Define the path to your Excel workbook
excelFilePath <- "C:/Users/steve/Documents/GitHub/steveondata/posts/2024-07-10/file_date_time.xlsm"

# Create an Excel application object
excelApp <- COMCreate("Excel.Application")

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

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

# Run the macro
excelApp$Run("GetFileDateTime")

NULL

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

[1] TRUE

# Quit the Excel application
excelApp$Quit()

NULL

In this R script:

• excelFilePath specifies the path to your Excel workbook.
• excelApp creates an Excel application object.
• workbook opens the specified workbook.
• excelApp$Run("GetFileDateTime") runs the VBA macro.
• workbook$Close(FALSE) closes the workbook without saving changes.
• excelApp$Quit() quits the Excel application.

Here is the message box:

Understanding the Workflow

The above workflow shows how R can interact with Excel to execute VBA code. This approach is powerful for automating tasks that require both the statistical capabilities of R and the scripting power of VBA.

Try It Yourself

I encourage you to try this integration on your own. Modify the VBA code to suit your needs, and experiment with different R scripts to see how you can further automate your workflows. The combination of R and VBA opens up a lot of possibilities, and getting hands-on experience is the best way to learn.

Feel free to share your experiences or any questions you have in the comments below. Happy coding!

Stay tuned for more posts where we continue to explore the exciting interplay between R and VBA!