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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

6 thoughts on “How to get multiple values from closed workbook using VBA”

  1. how to direct the extract ed results to a desired location? i mean to a different raw and column address?
    this always goes to “a1”
    thank for the wonderful coding.

  2. I cannot get this to run correctly.
    Every time I run it, I get a RUNTIME ERROR 1004. “The Formula you typed contains an error.”
    When I click the DEBUG button. It highlights the line:
    GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
    I have checked my code repeatedly and it appears to be exactly the same as yours.
    I am frustrated and confused.
    Any help with this would be greatly appreciated.
    Thank you in advance
    Scott

  3. BRAVO, j’en reste bouche bée, vous êtes des cracks!!!! mais ça nous étonne qu’à moitié. Je voudrais juste vous rappeler l&e1c87;obj#2tif de départ: ARRIVER!Maman Papa.

Leave a Reply

Your email address will not be published. Required fields are marked *