Macro that loops through all monthly folders

[Djani]

Dear all,

I am in need of your advice/expertise as I'm currently stuck on programming a macro in Access due to lack of knowledge.
Hereunder I'm giving you some background information which gives you an idea of the requirements for the macro:

Background information
- There are 12 monthly folders as we are working on a fiscal year basis
- Each monthly folder contains another 9 RBU (Regional Business Unit) folders
- Each RBU is split up in multiple country folders -> e.g. CENTER would be Austria/Germany/Switzerland, IBERIA would be Spain, Portugal
- The file names - in the country folders - are always named with the following structure: [VIVA][PRODUCT][COUNTRY][VF] -> e.g. VIVA_LEAF_EGB_VF
- The path file to these files will be something like this -> I:\R&E Internal\01 Reporting & Tools\06 Transaction Price\Transaction price report G5\14 - MOSY FY17\06_September\CEN\GER\Published

What do I want?
The idea is to set up 9 RBU Access databases that contains a macro which automatically loops through each of these monthly folders to consolidate all the data. Each time I run the macro it should restart the entire looping process where it deletes the existing information in Access and re-consolidates the data. I'm completely aware of it not being the most efficient way, but the aim is to provide accurate data as historical information could change due to whatever reason.

Is this even feasible?
If you need additional information, please let me know so I can provide you this

Many thanks for your time & willingness to help me out

Have a nice day

PS: I wanted to upload a local picture, but I don't see the URL

Djani

 

[stumac]

If you want it to go to the active worksheet you can change it from:

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

to

Set BaseWks = ActiveSheet

if it is a different sheet in the workbook:

Set BaseWks = ActiveWorkbook.Sheets("SheetName")

 

[Youngdand]

HI,

if i were you i would definitely have a look and play for an hour or 2. Power bi can use excel documents as data sources, and you set the relationships between them. You may or may not need to set a up a couple of Dimension tables, eg a list of business units, date table. But with very little time, you can pull together some quite meaningful information, and a simple stitching together of multiple reports that are all in the same format should be a doddle, and once you have set the data sources, its just a case of refreshing them, when you want to update.

Its worth having a play for a couple of hours to see what you can come up with.

Regards,

Dan.

 

[Djani]

If you want it to go to the active worksheet you can change it from:

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

to

Set BaseWks = ActiveSheet

if it is a different sheet in the workbook:

Set BaseWks = ActiveWorkbook.Sheets("SheetName")

That's working perfectly, thanks again.
Do you know how I can get the data pasted in the second row (as first row contains headers) of the current workbook?

 

[stumac]

Yeah the line that says:

rnum = 1

change to

rnum = 2

rnum represents the row number.