Read information from a closed workbook

With the macros below you can read values and text from cells in closed workbooks. The example macros shows how you can read the value from cell A1 in Sheet1 in all workbooks in a given folder.

Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
    FolderName = "C:\Foldername"
    ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "" & "*.xls")
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook
    r = 0
    Workbooks.Add
    For i = 1 To wbCount
        r = r + 1
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "A1")
        Cells(r, 1).Formula = wbList(i)
        Cells(r, 2).Formula = cValue
    Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
    If Dir(wbPath & "" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

This method has some limitations on how many cells you can return information from since the Excel4-macro creates links to the closed workbook. You can use a similar example using ADO if you need to retrieve a lot of data from a closed workbook.

It is often much easier to open the workbook and get the information from it. If you set the Application.ScreenUpdating to False, the user will probably not notice that the workbook is opened and closed again.

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True) 
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("TargetSheetName")
        ' read data from the source workbook
        .Range("A10").Formula = wb.Worksheets("SourceSheetName").Range("A10").Formula
        .Range("A11").Formula = wb.Worksheets("SourceSheetName").Range("A20").Formula
        .Range("A12").Formula = wb.Worksheets("SourceSheetName").Range("A30").Formula
        .Range("A13").Formula = wb.Worksheets("SourceSheetName").Range("A40").Formula
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Here is another variant:

Sub CopyFromClosedWB(strSourceWB As String, _
    strSourceWS As String, strSourceRange As String, _
    rngTarget As Range)
' copies information from a closed workbook, no input validation!
' use like this to copy information to the active worksheet:
' CopyFromClosedWB "C:\Foldername\Filename.xls", "Sheet1", "A1:D100", Range("A1")
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    If Not wb Is Nothing Then ' opened the workbook
        On Error Resume Next ' ignore errors
        With wb.Worksheets(strSourceWS).Range(strSourceRange)
            .Copy rngTarget
        End With
        On Error GoTo 0 ' stop when errors occur
        wb.Close False ' close the source workbook without saving changes
        Set wb = Nothing ' free memory
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Sub TestCopyFromClosedWB()
    CopyFromClosedWB "C:\Foldername\Filename.xls", _
        "SheetName", "A1:D10", Range("A1")
End Sub

Document last updated 2004-10-14 17:45:05 Printerfriendly version

User comments:

Mark wrote (2006-07-19 00:49:12 CET):

Copy data from a closed workbook
I am trying to open a closed workbook while screen updating is off and copy information from that workbook to the workbook in which I am running the macro. But whenever the macro hits the "Set wb = Workbooks.Open("filename.xls", True, True)" statement, the macro opens that file and then stops running. Any suggestions?

Brian from USN wrote (2006-01-10 16:10:17 CET):

CopyFromClosedWB
Thank you so much for posting this. It worked flawlessly.

Ole P. from Norway wrote (2005-08-24 09:27:19 CET):

Re: Read comments from a closed workbook
You have to open the workbook first.
All actions that retrieves information from a closed workbook will in one way or other "open" the file to read information from it.
Use the last example above and the user will not see that the workbook is open while you get the information you are looking for.

Xor from Norway wrote (2005-08-24 00:15:04 CET):

Read comments from a closed workbook
Hi
After reading this example I have tried to use ExecuteExcel4Macro to read values from a closed workbook, and this works great. But what I really want to do is to read values and any comments if there are any. Any suggestions on how to get the comments (without opening the file first)?

Thanks in advance.

Ole P. from Norway wrote (2005-08-11 15:06:16 CET):

Re: Using Vlookup in VBA to look up values in a closed workbook
This seems not to be working.
You can either open the closed workbook with screenupdating disabled and look up the value like this:
result = Application.WorksheetFunction.VLookup(SomeValue, Workbooks("Book1.xls").Worksheets("Sheet1").Range("A$2:D$100"), 2, False)

Or you can retrieve the values from the closed workbook by using temporary link formulas in a worksheet like this:
Range("C2").Formula = "=VLOOKUP(SomeValue,'C:\Foldername\[Book1.xls]Sheet1'!A$2:D$100,2,FALSE)"
The retrieved values can then be stored in e.g. an array variable and the temporary link formulas can be deleted.

Flying Rabbit from New yOrk wrote (2005-08-11 00:29:38 CET):

Using Vlookup in VBA to look up values in a closed workbook
Using Vlookup in VBA to look up values in a closed workbook

Is that possible? The Second Argument of vlookup is the range from a closed workbook. I can't seem to get VBA to do that.

Thanks for ur help.

Application.WorksheetFunction.vlookup(SomeValue,RangefromAcloseWorkbook,3)

Ole P. from Norway wrote (2005-07-12 22:49:00 CET):

Re: Coul you please help me?
This will paste the copied values:
With wb.Worksheets(strSourceWS).Range(strSourceRange)
.Copy
Cells(2, contColum).PasteSpecial xlPasteValues
End With

Gaby from Costa Rica wrote (2005-07-11 18:44:52 CET):

Coul you please help me?
I'm using the last example, but when I try to copy the cells from workbook closed, it copies the formulas and not the values, then it shows me ceros.

With wb.Worksheets(strSourceWS).Range(strSourceRange)
.Copy Cells(2, contColum)
End With

Could you please help mt to copy the values and not the formulas.
Thanks

Ole P. from Norway wrote (2005-05-10 12:38:25 CET):

Re: Reading Worksheet Names
You will have to use the second example above since you are not able to pass the proper sheet name to ExecuteExcel4Macro.

Greg L from Portland, Oregon wrote (2005-05-06 23:28:03 CET):

Reading Worksheet Names
I need to read Cell "A1" from multiple Excel files, all in the same folder. My only problem is that although I want to read the first worksheet in each workbook, the worksheet names are all differenct.

How can I read Sheet(1) from each workbook if ExecuteExcel4Macro requires the worksheet name instead?