VBA to collect data from multiple excels into 1 database

[TCR21]

Hi guys,

I´m trying to put together my first ever VBA application but cannot really move forward I would be so grateful if you could help or inspire me, I think many of you already have solved similar tasks.

So what I would like to achieve:

I have multiple excel source files (pictures attached : Source Switzerland Bern, Source Switzerland Zurich etc.) and I´d like to create one single database for all of them. So I´ve created a file "Database.xlsm" and when I choose the country, month and year, the vba should extract the data from the source files and put it into my "Database.xlsm" as shown on attached picture (Database xlsm).

Structure of source file:

A) Each country is a separate excel workbook (Switzerland.xlsm, Germany.xlsm, etc......)
B) Locations (Zurich, Bern) within country are separated by worksheets
C) Worksheets include all KPIs for the given location in current and base year - this is the biggest issue for me because for example in 2020, they changed it from 2018 vs. 2019 to 2019 vs 2020, thus 2018 data got lost. Otherwise I would be able to do it via Power Query.

So this is the main reason why am I looking for a VBA solution, to store historical data in an own database.

I attached some pictures hope it is helpful.

Thank you very much in advance!

Tomas

 

[MrSyyr]

Hi,
Do you want to do this import once or be able to refresh the data for these multiple sources when you like?
Will the set of data sources change much over time?
cheers

 

[TCR21]

Hi MrSyyr,

Many thanks for your reply.

The second option, I'd like to refresh data when I like. I would do it once per month when new data comes in. The sources are filled in with monthly data.

The structure of the data sources should remain the same.

Thank you.
Tomas

 

[MrSyyr]

Hi,
ok, try importing data from one sheet to another.
Data tab > New Query > From File > From Workbook
Select one of the sheets and click Load.
You should see a table appear with data in it.
Then add this macro to a new module in your worksheet (or you can just use Alt +F5 to refresh the data)
Sub refresh()
ActiveWorkbook.RefreshAll
End Sub
this should refresh all the data from it's source, no matter how many data sets you have. It will expect the same data structure in the same worksheet in the same folder for each source.

MrS

 

[TCR21]

Thank you for yoir response.

I have a similar solution using Power Query. I' ve created a single database for all sheets and workbooks however it does not adress my issue here - in 2021, they will replace 2019 column with 2020 data and 2019 will get lost. In source files they only compare current year vs previous year.

Thats why I' m looking for some solution to load the data via VBA for each month separately and store it in my own database. Or maybe i will ask them not to replace data in existing columns but rather to hide them and always create new columns for new years.

Thank you,

Tomas

 

[MrSyyr]

Hi,
Can you show me the structure of your database sheet?
MrS