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

ExecuteExcel4Macro

ByRef and ByVal

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

3 thoughts on “Get Value from Closed Workbook with VBA”

  1. Sir
    How can import a particular Colum from PDF to Excel file how ever i have many of PDF files and I want Import a particular Colum (which contains some value) from PDF to Excel Please Help for this formula.
    Sir Please and Please

  2. Hello, I am new to Excel VBA and I would like to add the rows that matches criteria values of the column data, could you help me to modify in the above code. Thanks for your help in advance.

  3. Hello, what if you need to make a sum of range of cells, for example from C1 to C3. How can you perform that? also, do you have any language refernece of functions list for Excel 4.0 Macro which it is not .HLP foramt?
    Thank you

Leave a Reply

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