Combining data from multiple workbooks (many variables involved)

[VitoC]

Hi all,

I always thought that I was rather skilled at working with Excel, but I’ve got me a ‘problem’ that I can’t fix by myself. Looking at the topics and solutions offered on this forum I’m merely a novice compared to what some people can do. Therefore: Any help would be much appreciated! I’ve searched online at ‘power queries’ or macro’s, but I’m stuck!

Background: I am logging my sports results and combine these with my food intake. I'm quite a fanatic, running marathons, swimming long distances, etc. and therefore I'd like to be able to do some proper data analysis to track my progress and improve where possible.

Problem: I want to combine data from multiple workbooks in one sheet, but there are many variables involved.

• The workbook-names are variable and naming is tied to the current date (ie. “report 20190122”, “report 20190123”, “report 20190124”, etc).
• The workbooks are grouped per month (so folders are named January, February, March, etc) and per year (2017, 2018, 2019). The main location is: C:\Documents\Reports\. So the full path for a document would be: C:\Documents\Reports\2019\January\report 20190122.xlsm
• Each workbook has the same layout. They all have 5 sheets with the same names, but I only want to collect data from the sheets: “morning”, “noon” and “afternoon”.
• Within these sheets the relevant cells are: F30:I30, F40:I40, and F54:I54.
• I’d like to be able to get a monthly and quarterly overview of data results.
• And I’d like to be able to refresh the data at any moment (so that I have the most up to date overview at any moment).
• Destination file would be something like: "summary 2019 January", "summary 2019 February", and "summary 2019 Q1" for the first quarterly, etc.

How can I best combine the data? Any advice is greatly appreciated!!

 

[ravishankar]

Save the following codes as macro and save the file in the same folder from which data needs to be pulled. It pulls data from closed file. I suspect it may not work with versions after 2007. Give it a try. if it does not work i will give you codes to open each file and pull the data

Sub VitoC()
Dim a As Long, c As Long, d As lonv
Dim f As String, y As String, e As String, m As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
For a = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    For c = 1 To 3
        y = Choose(c, "morning'!" & e, "noon'!" & e, "Afternoon'!" & e)
        Cells(a - 1, c + 3) = y
         For d = 1 To 12
         e = Choose("F30", "G30", "H30", "I30", "F40", "G40", "H40", "I40", "F50", "G50", "H50", "I50")
         m = "=" & "'[" & Cells(a, 1) & "]" & y
        Cells(a, c + 3) = m
        Next d
    Next c
  Next a
MsgBox "Completed"
End Sub

It lists file names in col A and data in col D to whatever.
Ravishankar

 

[VitoC]

Thanks for helping out Ravishankar. My problem has been solved by someone else. He solved it with a couple of formulas in the Power Query Editor.