Getting data from a closed workbook using the workbook filename and path as variables

[amyprysephillips]

Does anyone know of a way to pull data from various closed "source" workbooks into a "destination" workbook, using the "source" workbook filenames and paths as variables? The source workbooks all have the same format, so as an example I would want to pull the value of cell B48 from Sheet 1 from all source workbooks into the destination workbook. My destination worksheet would look like this:

column A: pathname

column B: filename

column D: Sheet

column E: cell

column F: result

Thanks in advance!

Amy

[#2]

@amyprysephillips 

Here is a small approach with VBA.

Function and macro belong in a normal standard module.
The macro goes into the function and gets the value from the closed file (comparable with a cross-file link).
The "R3C2" at the end means Row 3, Column 2; this corresponds to the cell address "B3".

Columns and cells would have to be adjusted as required
Here again the function and corrected macro:

Function xl4Value(strParam As String) As Variant
    xl4Value = ExecuteExcel4Macro(strParam)
End Function

Sub Nikolino()
Dim strSource As String
    strSource = "'C:\WINNT\...\[Mappe2.xls]Tabelle1'!R3C2"  'these is a example path
    Range("B9").Value = xl4Value(strSource)  'adjust the range as you wish
End Sub

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.