CODEWITHSUNDEEP
excelvba
Jonny Tingate
Jonny Tingate

Reputation: 11

Reference another cell outside workbook

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?

Upvotes: 0

Views: 71

Answers (1)

JvdV
JvdV

Reputation: 75840

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.

Upvotes: 1

Related Questions