Get data from dynamic location and filename on closed workbook using VBA

[Joe006]

Hi, I got a lot of excel files in different location where each files is put in folder named month and year. I need to get the data from it while it's closed and data is pulled based on the drop down list for the month and the year. I have tried using the INDIRECT function and it work only when the file is opened. I just discovered that need to use VBA script to pull while it closed, if using VBA, how to call the data and set formula for the folder name and file dynamically according the chosen drop down list ?

Any helps much appreciated, Thank you

 

[eiloken]

if you want to use vba, the idea is macro will open specificity file each time when change value in B2 or C2, update data and close these files. is that OK?

 

[Joe006]

if you want to use vba, the idea is macro will open specificity file each time when change value in B2 or C2, update data and close these files. is that OK?

Hi, can it be pulled without the file is opened? If using VBA cant do that, any other method suggestion?

 

[eiloken]

in my knowledge, vba need open these files and get data from it then auto close them when done

 

[Joe006]

in my knowledge, vba need open these files and get data from it then auto close them when done

Oh okay, can you help me on how this can be done?

 

[eiloken]

Oh okay, can you help me on how this can be done?

in that case, you need to show me folder type name that we need to get data files and what type of name files we need to get data or just get all excel file in that folder

 

[Joe006]

The folder is like in Picture 1. The excel files inside the folder in Picture 2. So data is pulled from different cells from the source , E5 is sum up in Picture 3

Picture 1:

Picture 2:

Picture 3:

 

[Joe006]

 

[eiloken]

View attachment 102280

i dont know how you put data into summary worksheet then i create macro to open each file in match folder and recalculate summary sheet and close those file after done:

Private Sub GetFiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim pFol As String, xcode As String
    Dim fso As Object, fol As Object, subFol As Object, fil As Object
    Dim filPath As String
    Dim wb As Workbook
    pFol = "C:\Users\Desktop\testforstranger" 'change it to your OT Management path"
    xcode = Format(ThisWorkbook.Sheets(1).Range("B2").Value, "00") & ThisWorkbook.Sheets(1).Range("C2").Value 'change ThisWorkbook.Sheets(1) to your summary sheet
    Debug.Print xcode
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(pFol)
    For Each subFol In fol.SubFolders 'loop through each folder in your parent folder to find match folder with month and year
        If subFol.Name = xcode Then
            For Each fil In subFol.Files
                filPath = fso.GetAbsolutePathName(fil)
                If filPath Like "*.xlsx" Then 'open each excel file and recalculate summary worksheet
                    Set wb = Workbooks.Open(filPath)
                    ThisWorkbook.Sheets(1).Calculate  'change ThisWorkbook.Sheets(1) to your summary sheet
                    Debug.Print wb.Name
                    wb.Close (False)
                End If
            Next fil
        End If
    Next subFol
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Joe006]

i dont know how you put data into summary worksheet then i create macro to open each file in match folder and recalculate summary sheet and close those file after done:

Private Sub GetFiles()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim pFol As String, xcode As String
    Dim fso As Object, fol As Object, subFol As Object, fil As Object
    Dim filPath As String
    Dim wb As Workbook
    pFol = "C:\Users\Desktop\testforstranger" 'change it to your OT Management path"
    xcode = Format(ThisWorkbook.Sheets(1).Range("B2").Value, "00") & ThisWorkbook.Sheets(1).Range("C2").Value 'change ThisWorkbook.Sheets(1) to your summary sheet
    Debug.Print xcode
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(pFol)
    For Each subFol In fol.SubFolders 'loop through each folder in your parent folder to find match folder with month and year
        If subFol.Name = xcode Then
            For Each fil In subFol.Files
                filPath = fso.GetAbsolutePathName(fil)
                If filPath Like "*.xlsx" Then 'open each excel file and recalculate summary worksheet
                    Set wb = Workbooks.Open(filPath)
                    ThisWorkbook.Sheets(1).Calculate  'change ThisWorkbook.Sheets(1) to your summary sheet
                    Debug.Print wb.Name
                    wb.Close (False)
                End If
            Next fil
        End If
    Next subFol
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Thank you! , will try this