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

%3CLINGO-SUB%20id%3D%22lingo-sub-1659324%22%20slang%3D%22en-US%22%3EGetting%20data%20from%20a%20closed%20workbook%20using%20the%20workbook%20filename%20and%20path%20as%20variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1659324%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20know%20of%20a%20way%20to%20pull%20data%20from%20various%20closed%20%22source%22%20workbooks%20into%20a%20%22destination%22%20workbook%2C%20using%20the%20%22source%22%20workbook%20filenames%20and%20paths%20as%20variables%3F%20The%20source%20workbooks%20all%20have%20the%20same%20format%2C%20so%20as%20an%20example%20I%20would%20want%20to%20pull%20the%20value%20of%20cell%20B48%20from%20Sheet%201%20from%20all%20source%20workbooks%20into%20the%20destination%20workbook.%20My%20destination%20worksheet%20would%20look%20like%20this%3A%3C%2FP%3E%3CP%3Ecolumn%20A%3A%20pathname%3C%2FP%3E%3CP%3Ecolumn%20B%3A%20filename%3C%2FP%3E%3CP%3Ecolumn%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Sheet%3C%2FP%3E%3CP%3Ecolumn%20E%3A%20cell%3C%2FP%3E%3CP%3Ecolumn%20F%3A%20result%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3EAmy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1659324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1663607%22%20slang%3D%22de-DE%22%3ESubject%3A%20Getting%20data%20from%20a%20closed%20workbook%20using%20the%20workbook%20filename%20and%20path%20as%20variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1663607%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F790859%22%20target%3D%22_blank%22%3E%40amyprysephillips%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20is%20a%20small%20approach%20with%20VBA.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3EFunction%20and%20macro%20belong%20in%20a%20normal%20standard%20module.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EThe%20macro%20goes%20into%20the%20function%20and%20gets%20the%20value%20from%20the%20closed%20file%20(comparable%20with%20a%20cross-file%20link).%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EThe%20%22R3C2%22%20at%20the%20end%20means%20Row%203%2C%20Column%202%3B%3C%2FSPAN%3E%20%3CSPAN%3Ethis%20corresponds%20to%20the%20cell%20address%20%22B3%22.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EColumns%20and%20cells%20would%20have%20to%20be%20adjusted%20as%20required%3C%2FSPAN%3E%3CBR%20%2F%3EHere%20again%20the%20function%20and%3CSPAN%3Ecorrected%20macro%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20xl4Value(strParam%20As%20String)%20As%20Variant%0A%20%20%20%20xl4Value%20%3D%20ExecuteExcel4Macro(strParam)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Nikolino()%0ADim%20strSource%20As%20String%0A%20%20%20%20strSource%20%3D%20%22'C%3A%5CWINNT%5C...%5C%5BMappe2.xls%5DTabelle1'!R3C2%22%20%20'these%20is%20a%20example%20path%0A%20%20%20%20Range(%22B9%22).Value%20%3D%20xl4Value(strSource)%20%20'adjust%20the%20range%20as%20you%20wish%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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

1 Reply
Highlighted

@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.