Aug 24 2021 09:56 AM
Dear Tech Community,
I am trying to reference multiple closed Excel workbooks with different file paths and file names. My initial approach was to define the file path and file name in different cells and concatenate it into the string with the relevant syntax (i.e putting square brackets around the file name, putting an exclamation point after the sheet etc.]
A1 - File Path | B1 - File Name | C1 - Sheet Name | D1 - Concatenated String Formula | E1 -Index Match Formula |
C:\Users\wadoodoo\Desktop\Cash Flow Tools\Money\ | Sample Excel.xlsx | Sample Sheet | =CONCATENATE("'" & A1 & "[" & B1 & "]" & C1& "'") | =INDEX('D1'!$C:$C,MATCH(G5,'D1'!$B:$B,0)) |
Unfortunately this does not work. Every time I have to manually select the file which is really tedious as I have to do this across 50 different files and each row refers to a different closed workbook.
Is there any way I can make this process a bit more dynamic since all I want to do is change the file name and excel should be able to pick up all the relevant data from the new file automatically. Each file will have a new version on a bi-weekly/monthly basis.
Look forward to any guidance and advice. Thank you in advance.
Aug 26 2021 10:12 AM
Aug 26 2021 10:31 AM
That is still more than 2100 columns, i.e. over 175 years!
By the way, in which row or rows do you want the formulas?
Aug 26 2021 10:35 AM
Aug 26 2021 10:41 AM
Starting in which row? I need exact information!
Aug 26 2021 10:42 AM
Aug 26 2021 10:43 AM
Aug 26 2021 11:07 AM
SolutionHere is a new sample workbook.
The current values in G5:UU5 are just dummy values.
First, enter some valid lookup values in G5, H5, etc. You don't have to do all of them, just a few for now.
Next, correct the values in A7, B7 and C7, or if they are already correct, select one of them, press F2, then press Enter.
Then look at G7, H7, etc. Hopefully, they will now contain formulas.
If that works, add data for a different workbook in A8 to C8 and then look at G8, H8 etc.
Aug 26 2021 11:12 AM
Aug 26 2021 11:14 AM
Assuming that the layout of your worksheet is the same as that of my sample worksheet, yes - copy the code as I described a few replies ago.
Aug 26 2021 11:21 AM
Aug 26 2021 11:24 AM
Yes: for example, "A7:C500" becomes "C7:E500".
Aug 26 2021 11:25 AM
Aug 26 2021 11:28 AM
Mar 27 2022 01:44 AM
Mar 27 2022 03:34 AM
The VBA code in the workbook is not a macro,so it doesn't show up in the Macros dialog.
It is a so-called event procedure that will be run automatically by Excel when you change the value of a cell in a specific range (A7:C500 in the sample workbook). The code belongs in the worksheet module, not in a standard module.
If you wish, you can attach a copy of your workbook to a reply, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.
Mar 27 2022 04:01 AM - edited Mar 27 2022 04:27 AM
@Hans Vogelaar Thanks for the reply. I have no knowledge about VBA or event procedures.
I have attached the file for your reference. Can you please have a look at the code?
I'm trying to pull data from multiple workbooks and multiple worksheets by providing path, workbook name, and worksheet name. From every worksheet, I need to pull data from a specific cell which is C5. I would appreciate it if you can look at the issue. There can be some errors in the code that I might not be able to identify. Also, it would be helpful if I can get it as a macro code.
Thanks for the help in advance. Let me know in case of any queries.
Mar 27 2022 05:24 AM
Here is the code as a macro.
Private Sub Get_Data()
Dim rng As Range
Dim r As Long
Dim m As Long
Dim s As String
Dim f As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
m = Range("A" & Rows.Count).End(xlUp).Row
Range("G7:G" & m).ClearContents
For r = 7 To m
s = Range("A" & r).Value & "\" & Range("B" & r).Value
If Dir(s) <> "" Then
s = "'" & Range("A" & r).Value & "\[" & Range("B" & r).Value & "]" & Range("C" & r).Value & "'!C5"
f = "=IFERROR(" & s & ","""")"
On Error Resume Next
Range("G" & r).Formula = f
On Error GoTo ErrHandler
End If
Next r
ExitHandler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Mar 27 2022 06:09 AM
Thank you so much for the help, Hans. This code worked and I'm able to extract the data from the workbooks.
Can you please help me modify the code a little bit? I'm using 7-8 workbooks and every workbook has 15-20 worksheets. So, I want to use different categories for every workbook and want to use a single cell for providing the workbook name for every category (can select them using data validation list). And for the file path, just want to have a single cell. I have attached the file for your reference. It would save me a lot of time since I have to update the file path and workbook name on a weekly basis.
Thanks for the help in advance :). If can't be done, will use it this long way only.
Mar 27 2022 06:26 AM
See if this works for you:
Sub Get_Data()
Dim r As Long ' row number
Dim m As Long ' last row
Dim p As String ' path
Dim b As String ' filename
Dim s As String ' cell reference
Dim f As String ' Formula
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
p = Range("B6").Value
m = Range("A" & Rows.Count).End(xlUp).Row
Range("G7:G" & m).ClearContents
For r = 7 To m
If Range("A" & r).Value <> "" Then
If Range("C" & r).Value <> "" Then
b = Range("C" & r).Value
Else
s = p & "\" & b
If Dir(s) <> "" Then
s = "'" & p & "\[" & b & "]" & Range("D" & r).Value & "'!C5"
f = "=IFERROR(" & s & ","""")"
On Error Resume Next
Range("G" & r).Formula = f
On Error GoTo ErrHandler
End If
End If
End If
Next r
ExitHandler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Mar 27 2022 06:40 AM
Thanks a lot for your patience and help on this. This code is working very well and I'm also able to execute it as a macro. Really grateful to you for all of your help on this code :)