How to get multiple values from closed workbook using VBA

In the last post we learnt how to get a value from a closed workbook and place it in a new worksheet in a new workbook. Today’s video explains how to get multiple values from a closed workbook using VBA code with a ‘for next loop’.

Watch the training video  below before studying the VBA code:

Watch the video on YouTube.

Here’s the complete VBA code to get multiple values from a closed workbook:

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 GetMultipleValuesFromClosedWorkbook()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long

p = ThisWorkbook.path
f = “testworkbook.xlsx”
s = “Sheet1”
c = 10
Application.ScreenUpdating = False

For r = 1 To 3
a = Cells(r, c).Address
MsgBox a
Cells(r, 2) = GetValueFromClosedWorkbook
(p, f, s, a)
Next r
End Sub