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