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.
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.
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
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
You can find below output screenshot of above macro.