Forum Discussion

amyprysephillips's avatar
amyprysephillips
Copper Contributor
Sep 11, 2020

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

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 😧 Sheet

column E: cell

column F: result

Thanks in advance!

Amy

Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 14, 2020

    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.

Resources