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
3 thoughts on “VBA Get Multiple Values From Closed Workbook”
Hello, I’m having trouble getting the Combobox1.value, the cell value got it, thanks for the tip.
Can you help me get the value of combobox?
“‘workboot.path & workbook.name & Worksheet.name & “‘!Sheets(“Plan2”).OLEObjects(“CheckBox1″).Object.Value”
“‘workboot.path & workbook.name & Worksheet.name & “‘!Sheets(“Plan2”).(“CheckBox1″).Value”
The two ways didn´t work return Error 2029
There is a spelling mistake. Please correct Workboot to Workbook at both places.