August 11, 2015

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

6 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

  4. Mr. Ahamad,
    I’m still searching for answer on how to achieve his task. I’m trying to copy a row with title “BOE” to an open workbook
    so the macro has to search for title “BOE” select the entire row and then copy to the open workbook. I have this code however missing what I’m missing is how to do the search and copy paste as values to the open workbook

    Can anyone help me please??

    Sub copy()

    Dim destSht As Worksheet
    Workbooks.Open (“C:\test\source.xlsx”)
    Set destSht = ActiveWorkbook.Worksheets(“Sheet1”)
    With ThisWorkbook.Worksheets(“Sheet1”)



  5. Hello-

    I used this code in a brand new workbook with no other macros and it worked perfectly. Now, I am using the same exact code in an existing workbook with other macros, and I am getting the following error:

    Run-time error ‘1004’:
    We found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct.

    Do you know why this might be happening? When I click “Debug” it highlights the row with the ExecuteExcel4Macro.

  6. Dear Sir,

    Thanks for your videos, its help me lot to know more about VBA. I would like to know on the same example how to count the specific column with or without conditions. I mean the video ” how to get the value from the closed workbook”

    in the end the of the video you had mentioned that it will be discussed on the next video, I have searched all your videos but I could not get it that. if you explained that it will be so helpful and I can use that to create my report. I’m looking forward the codes which need to read the data in closed work book and do the calculation and print the result in the destination or my workbook

Comments are closed.