Reference another cell outside workbook

Question

I have a list of 150 filenames from the same directory in column A in an Excel workbook using

=REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

at the top of the workbook and using formula

=IFERROR(INDEX(FileNameList,ROW()-2),"")

to list each filename in the directory.

I want to reference D4 in each of these files and put the value of this next to the filename in column B of this external workbook.

How can I go about retrieving these?

The best way (IMO) would be to iterate your files in the directory, but to **not** open them, but instead use [`Application.ExecuteExcel4Macro`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.executeexcel4macro). What would be the name of the worksheet these values need to be pulled from `D4`? — JvdV, Jan 22 '20 at 12:03
@JvdV depends on how you define *"best way"*. Excel4Macros are long deprecated and the function is there for compatibility reasons only. So I would not consider it the *"best way"*. Nevertheless it will work and probably be pretty fast. — Pᴇʜ, Jan 22 '20 at 12:13
@Pᴇʜ, the "best" way I know and can produce =) (it's still just a hobby hehe) — JvdV, Jan 22 '20 at 12:14
@JvdV obviously an *"opinion-based"* question, I voted to close it since [No attempt was made](http://idownvotedbecau.se/noattempt/). (btw I'm not a professional neither ;) — Pᴇʜ, Jan 22 '20 at 12:17

score 1 · Answer 1 · edited Jan 22 '20 at 17:35

The below would iterate through all files in a given folder, take the value from D4 on worksheets called Sheet1, then puts these into a Dictionary to Transpose onto your current Worksheet.

Sub Test()

Dim wbPath As String, wsName As String
Dim oFSO As Object, oFolder As Object
Dim Dict As Object: Set Dict = CreateObject("Scripting.Dictionary")

wbPath = "C:\Users\...\TestFolder\"
wsName = "Sheet1"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(wbPath)

For Each oFile In oFolder.Files
    Dict(oFSO.GetBaseName(oFile)) = ExecuteExcel4Macro("'" & wbPath & "[" & oFile.Name & "]" & wsName & "'!R4C4")
Next oFile

Range("A1").Resize(Dict.Count).Value = Application.Transpose(Dict.keys)
Range("B1").Resize(Dict.Count).Value = Application.Transpose(Dict.Items)

End Sub

By the way, I took some inspiration from Siddharth's answer on ExecuteExcel4Macro here.

Reference another cell outside workbook

1 Answers1