VBA Get Value From Closed Workbook

Get Value From Closed Workbook using Excel VBA. We automate multiple tasks by referring multiple workbooks. When we work with multiple workbooks while running macro, we have to open and close workbooks. Always we think to reduce processing time while automate tasks. When we open workbook and close workbook it takes some time to process. We can reduce this processing time and avoid open and close workbooks, by running Excel 4.0 Macro function. Here specifying workbook name is mandatory. This function works for only specified workbook.

Table of Contents:

Objective
Syntax of the Excel 4.0 Macro function
Macro to Get Value From Closed Workbook using VBA
Function to get value from closed workbook
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of the Excel 4.0 Macro function:

Here is the syntax of the Excel 4.0 Macro function.

Expression.ExecuteExcel4Macro(string)
Or
Application.ExecuteExcel4Macro(string)

Where String is a required argument. It accepts a string data type value.

Macro to Get Value From Closed Workbook using VBA

Here is the macro to get value from the closed workbook. In the following macro we are calling another function named ‘VBA_Extract_Value’.

'VBA Get Value From Closed Workbook
Sub VBA_Get_Value_From_Closed_Workbook()

    'Variable declaration
    Dim FilePath As String, Filename As String
    Dim SheetName As String, CellReference As String
        
    'Assign values to variables
    FilePath = "C:\Someswari\VBAF1\VBA Functions\VBA Text Functions"
    Filename = "VBA Functionsa.xlsm"
    SheetName = "Image"
    CellReference = "D1"
    
    'Display Message
    MsgBox "Output value from closed workbook is : " & VBA_Extract_Value(FilePath, Filename, SheetName, CellReference), _
    vbInformation, "VBA Get Value from Closed Workbook"
    
End Sub

Function to get value from closed workbook

Let us see the function to get value from the closed workbook.

'Function to get value from closed workbook
Private Function VBA_Extract_Value(sFilePath, sFileName, sSheetName, sCellReference)

    Dim sInput As String
    
    'Check File Path
    If Right(sFilePath, 1) <> "\" Then sFilePath = sFilePath & "\"
    
    'Check specified file exists or not
    If Dir(sFilePath & sFileName) = "" Then
        VBA_Extract_Value = "File doesn't exists."
        Exit Function
    End If
    
    'Required Input String to run 'ExecuteExcel4Macro' function
    sInput = "'" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & Range(sCellReference).Range("A1").Address(, , xlR1C1)
    
    'Execute the ExecuteExcel4Macro function
    VBA_Extract_Value = ExecuteExcel4Macro(sInput)
    
End Function

Output Screenshot: You can find below output screenshot of above macro.