» Read information from a closed workbook using VBA in Microsoft Excel
VBA macro tip contributed by
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
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.
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
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
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
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)
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.
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
' 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
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating