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: