Combining data from multiple workbooks (many variables involved)

VitoC

New Member
Joined
Jan 22, 2019
Messages
2
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!! :)
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
Code:
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

New Member
Joined
Jan 22, 2019
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,308
Messages
5,623,895
Members
415,999
Latest member
Sovereign maphoso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top