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

 

[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

xcode = Format(ThisWorkbook.Sheets("Summary").Range("B2").Value, "00") & ThisWorkbook.Sheets("Summary").Range("C2").Value

is this correct syntax for Summary sheet

 

[eiloken]

xcode = Format(ThisWorkbook.Sheets("Summary").Range("B2").Value, "00") & ThisWorkbook.Sheets("Summary").Range("C2").Value

is this correct syntax for Summary sheet

that sheet in the picture you attach, if that sheet name as "Summary" then it correct

 

[eiloken]

View attachment 102237

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

and i saw that E5 equals to sum of all F6 in sheet name "Summary" of input files, if it true then you can use this code:

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
    ThisWorkbook.Sheets(1).Range("E5").Value = Empty
    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
    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).Range("E5").Value = ThisWorkbook.Sheets(1).Range("E5").Value + wb.Sheets("Summary").Range("F6").Value 'change ThisWorkbook.Sheets(1) to your summary sheet
                    wb.Close (False)
                End If
            Next fil
        End If
    Next subFol
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Joe006]

xcode = Format(ThisWorkbook.Sheets("Summary").Range("B2").Value, "00") & ThisWorkbook.Sheets("Summary").Range("C2").Value

is this correct syntax for Summary sheet

I run and comes out error, debug show at xcode line

 

[eiloken]

View attachment 102291

I run and comes out error, debug show at xcode line

what is the name of this sheet?

 

[Joe006]

what is the name of this sheet?

Sheet1

 

[eiloken]

then you can keep

ThisWorkbook.Sheets(1)

, no need to modify it

 

[Joe006]

then you can keep

ThisWorkbook.Sheets(1)

, no need to modify it

Ok it runs, if i want to only pull data with no calculation for the other cell like B6, need to put code outside the loop right?

 

[Joe006]

then you can keep

ThisWorkbook.Sheets(1)

, no need to modify it

B6:E6 is the cell that only pulls the data, how can i only pull into those cell?

 

[eiloken]

View attachment 102298
B6:E6 is the cell that only pulls the data, how can i only pull into those cell?

to clearly, i want to confirm that E2 equals with sum of all F6 values in input data files and B6 to E6 equals with values of C12 to F12 in file that named as "90XX SMod.xlsx". Is that true?