Extract cell values in multiple closed workbooks

[AWExcel]

I need to extract sheet1!C1 values in 100 closed excel files in a same folder. I created a new excel file "Summary" in the folder, and extract the 100 excel file's name to [Summary]sheet1!A1:A100. I tried to use indirect(), but it only works when the excel files are open. It returns #REF error when files are closed.

Is there anyway to extract a specific cell value with external workbook closed without typing the exact file name? Any help will be appreciated! Thanks. Allen

[Summary]Sheet1

	A	B	C
1​	File001.xlsx	'C:\[File001.xlsx]Sheet1'!C1	=indirect(B1)
2​	File002.xlsx	'C:\[File002.xlsx]Sheet1'!C1	=indirect(B2)
3​	File003.xlsx	'C:\[File003.xlsx]Sheet1'!C1	=indirect(B3)
...​	...	...	...
...​	...	...	...
100​	File100.xlsx	'C:\[File100.xlsx]Sheet1'!C1	=indirect(B100)

 

[Yongle]

Are you prepared to use VBA
- this method avoids opening the workbooks

It requires a rather quaint archaic method referred to as Excel4Macro (which tells you how archaic it is!)
Unfortunately the function below cannot be used like a formula

Place both procedures in the same module

call the function like this

Sub GetValues()
    Dim Cel As Range, ws As Worksheet
    Set ws = Sheets("Summary")
    For Each Cel In ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp))
        Cel.Offset(, 1) = ClosedValue(Cel)
    Next
End Sub

.

amend fPath string in function below

Private Function ClosedValue(ByVal fName As String) As Variant
    Dim arg As String
    Const Ref = "$C$1"
    Const fPath = "C:\folder\subfolder\"            'MUST end path string with path separator \
    Const sheet = "Sheet1"
    arg = "'" & fPath & "[" & fName & "]" & sheet & "'!" & Range(Ref).Range("A1").Address(, , xlR1C1)
    ClosedValue = "#FILE NOT FOUND"
    If fName = "" Then
        ClosedValue = ""
        Exit Function
    End If
    On Error Resume Next
    If Dir(fPath & fName) <> "" Then ClosedValue = Excel.Application.ExecuteExcel4Macro(arg)
    On Error GoTo 0
End Function

Book1
	A	B
1	Name	Value Returned by macro
2	File001.xlsx	3
3	File002.xlsx	0
4	File003.xlsx	#FILE NOT FOUND
5	File004.xlsx	25
6	Master.xlsx	3
Summary

 

[AWExcel]

Thanks very much!
Best,
Allen

 

[Yongle]

Note that the values are static

To automatically update values when workbook opens ...
- call your procedure using Workbook_Open (which MUST be placed in ThisWorbook code window)

 

[AWExcel]

Thanks very much for the new tip.

When I tried it, I keep getting compile error message "Sub or Function not defined". I have Worksheet_Open() in ThisWorkBook, and GetValues() Sub is defined in Sheet1.

GetValues() worked fine without the Worksheet_Open(). Any helps will be appreciated.

Best,
Allen

 

[Yongle]

In that case, insert the sheet CodeName before the procedure name

Private Sub Workbook_Open()
    Call Sheet1.GetValues
End Sub