We can get a value from a closed workbook automatically using VBA. Earlier we learnt how to get data from another Excel file using a manual method. Watch the training video below:
Watch the video on YouTube.
Here’s the complete VBA code:
Option Explicit
Private Function GetValueFromClosedWorkbook(path, file, sheet, ref)
Dim arg As String
‘ Let’s check whether the file exists
If Right(path, 1) <> “\” Then path = path & “\”
If Dir(path & file) = “” Then
GetValueFromClosedWorkbook = “File Not Found”
Exit Function
End If
‘ We create the argument
arg = “‘” & path & “[” & file & “]” & sheet & “‘!” & _
Range(ref).Range(“A1”).Address(, , xlR1C1)
‘MsgBox arg
‘ Now we execute an XLM macro
‘All references must be given as R1C1 strings.
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValueFromClosedWorkbook()
Dim p As String, f As String
Dim s As String, a As String
p = ThisWorkbook.path
f = “testworkbook.xlsx”
s = “Sheet1”
a = “C3”
‘ MsgBox GetValueFromClosedWorkbook(p, f, s, a)
ActiveSheet.Range(“G2”) = GetValueFromClosedWorkbook(p, f, s, a)
End Sub
Further Reading:
Get Value from closed file