Get Value from Closed Workbook with VBA

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


ByRef and ByVal