VBA Get Multiple Values From Closed Workbook

Get Multiple Values From Closed Workbook using Excel VBA. We automate multiple tasks by refering 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.

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 Multiple Values From Closed Workbook using VBA

Here is the macro to Get Multiple Values 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_Multiple_Values_From_Closed_Workbook()

    'Variable declaration
    Dim FilePath As String, Filename As String
    Dim SheetName As String, CellReference As String
    Dim iRow As Integer, iColumn As Integer
    
    'Assign values to variables
    FilePath = "C:\Someswari\VBAF1\"
    Filename = "VBAF1.xlsm"
    SheetName = "Input"
        
    'Loop through rows from 1 to 6
    For iRow = 1 To 6
        'Loop through columns from 1 to 2
        For iColumn = 1 To 2
            CellReference = Sheets(SheetName).Cells(iRow, iColumn).Address
            ThisWorkbook.Sheets("Output").Cells(iRow, iColumn) = VBA_Extract_Multiple_Values(FilePath, Filename, SheetName, CellReference)
        Next iColumn
    Next iRow
    
End Sub

Function to Get Multiple Values from closed workbook

Let us see the function to Get Multiple Values from the closed workbook.

'Function to get multiple values from closed workbook
Private Function VBA_Extract_Multiple_Values(sFilePath, sFileName, sSheetName, sCellReference)
    
    'Variable declaration
    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_Multiple_Values = ExecuteExcel4Macro(sInput)
    
End Function

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

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog