How to Index Match from multiple closed workbooks in excel.

Shubhi1198 

Run this macro:

Sub ListFiles()
    Dim sFolder As String
    Dim sFile As String
    Dim r As Long
    Application.ScreenUpdating = False
    Range("G3:G" & Rows.Count).ClearContents
    sFolder = Range("D3").Value
    If Right(sFolder, 1) <> Application.PathSeparator Then
        sFolder = sFolder & Application.PathSeparator
    End If
    r = 2
    sFile = Dir(sFolder & "*.xls*")
    Do While sFile <> ""
        r = r + 1
        Range("G" & r).Value = sFile
        sFile = Dir
    Loop
    Range("G1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

HansVogelaar  Thanks for the information.

Could I trouble with you for a code to get list of files names present in a folder by providing the path in the worksheet, let's say D3, and to get the file names present in that folder in column G.

Attached is a snapshot of what I'm looking.

I have earlier used the name defined function and since it includes array formulas it causes an error while sharing the workbook.

Thanks for the help in advance.

Shubhi1198 

The code only creates formulas that refer to a single cell, so they are not array formulas...

HansVogelaar 

Hey Hans, I have one more issue. I'm making the excel file as a shared workbook but while doing so, I get this error "Cannot share Workbook because it contains array formulas that are linked to other workbooks". Is there any workaround for this as I think the VBA code shared by you contains array formulas?

Thanks for the help in advance.

Apologies for the trouble. found the issue at my end. the code is working. 

Shubhi1198 

Does the folder path in W3 contain a backslash \ at the end?

HansVogelaar 

Sorry for the trouble again. I used this code but not working.

HansVogelaar 

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 🙂

Shubhi1198 

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

HansVogelaar 

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.

Shubhi1198 

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

HansVogelaar  Thanks for the reply. I have no knowledge about VBA or event procedures.

https://lbsimac-my.sharepoint.com/:x:/g/personal/shubham_2019_lbsim_ac_in/EZVYg0uCjhBFrP0wh4OkcJEBqS8WlWBJENC1OR0xXW4fHg?e=HXCFDj 

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.

Shubhi1198 

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.