Checking if a Workbook is Open Using VBA

VBA is an excellent tool for automating tasks within Excel, and checking if a workbook is open is a straightforward process. Here’s how you can achieve this:

1. Open the VBA Editor
   • Press ALT + F11 to open the VBA editor.
   • In the editor, insert a new module by clicking Insert > Module.
2. Write the VBA Function
   • In the new module, write the following function to check if a workbook is open:

Sub CheckWorkbookOpen()
    Dim resultCheck As Boolean
    Dim wb As Workbook
    Dim specific_wb As String

    On Error Resume Next
    specific_wb = InputBox("Check if this workbook is open:")

    Set wb = Application.Workbooks.Item(specific_wb)
    resultCheck = Not wb Is Nothing

    If resultCheck Then
        MsgBox "Workbook is open"
    Else
        MsgBox "Workbook is not open"
    End If
End Sub

This function takes the name of the workbook as an argument and returns True if the workbook is open, and False otherwise.

Executing the VBA Code from R

R is a versatile statistical programming language, and integrating it with Excel can enhance your data processing capabilities. To execute the VBA code from R, you can use the RDCOMClient package, which allows R to interact with COM objects, such as Excel.

1. Install RDCOMClient Package
   • If you haven’t already installed the RDCOMClient package, you can do so by running:

if (!require("RDCOMClient")) {
    install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")
    library(RDCOMClient)
}

2. Create the R Script
   • Write the following R script to execute the VBA function:

library(RDCOMClient)

# Create an instance of Excel application
excel_app <- COMCreate("Excel.Application")

# Make Excel visible (optional)
excel_app[["Visible"]] <- TRUE

# Open the Excel workbook containing the VBA code
workbook_path <- path_to_your_workbook_with_vba.xlsm
workbook <- excel_app$Workbooks()$Open(workbook_path)

# Define the macro name
macro_name <- "CheckWorkbookOpen"

# Run the macro
excel_app$Run(macro_name)

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

# Quit the Excel application
excel_app$Quit()

Replace "path_to_your_workbook_with_vba.xlsm" with the actual path to your workbook. This script creates an instance of Excel, opens the specified workbook, runs the TestIsWorkbookOpen macro, and then closes Excel.