VBA Extracting Data from multiple workbooks, with varying sheet names

[oO P2K Oo]

Hi All,

I am looking for vba code that allows me to collate data from varying excel workbooks, however there are multiple sheet names within each workbook (of varying amounts) and the sheet names are different. I need to collect the raw data from these workbooks which do not always follow a set standard, but will have certain fields present. What I mean by this is:
Source 1: has 3 worksheets, but the raw data is on a worksheet called "Data for the period xx-xx-xx" and has 10 columns.
Source 2: has 5 worksheets, the raw data is on a worksheet called "Raw Data" and has 11 columns of data which needs to be extracted.
Source 3 has 2 worksheets with the raw data on a worksheet called xx-xx-xx - yy-yy-yy and has 8 columns.
I know this is a very big ask, but is there a way to make a macro that will find the right fields and populate a central worksheet with the columns of data in the correct columns, from the right worksheets?
A part of me thinks that this isn't possible and the user is better off collating the data manually, and then using that as a central file.

I have followed some online guides to extract data from multiple varying workbook names, but they all seem to refer to a workbook with only 1 sheet, with the sheet name being the same in all examples.

Many thanks in advance for your answers and suggestions!

 

[HaHoBe]

Hi,

could you clarify the understanding for the source files: are these located in one folder or in different folders? Will these always be the same?

What about the workbook names? How do you know the name of Source 1 etc? What kind of workbooks (xlsx, xlsm, xls)?

Do the names of the other worksheets in the workbooks in question differ significantly from the sheets designed to be copied?

Is the data to be added to exisiting data (any hint on where the data comes from to be placed on the sheet as well) or deleting any data range to start directly beneath the headers?
Name of worksheet resp. workbook?
Will that be the workbook with code?

You would need to pass the information about the headers in the collecting sheet in order to place data accordingly.
Is there a column that will be filled thoroughly on the collection sheet, if so which?

Holger

 

[oO P2K Oo]

Hi,

could you clarify the understanding for the source files: are these located in one folder or in different folders? Will these always be the same?

What about the workbook names? How do you know the name of Source 1 etc? What kind of workbooks (xlsx, xlsm, xls)?

Do the names of the other worksheets in the workbooks in question differ significantly from the sheets designed to be copied?

Is the data to be added to exisiting data (any hint on where the data comes from to be placed on the sheet as well) or deleting any data range to start directly beneath the headers?
Name of worksheet resp. workbook?
Will that be the workbook with code?

You would need to pass the information about the headers in the collecting sheet in order to place data accordingly.
Is there a column that will be filled thoroughly on the collection sheet, if so which?

Holger

Hi Holger,

Thanks for your reply.
The source files will be in one location, but will be split between financial periods. e.g.
C:Project/Raw Data Files/FY22-23
C:Project/Raw Data Files/FY23-24

The workbooks which contain the raw data will only be held in these folders, but will have differing names e.g.
Data for weeks 1-4
Weeks 5-8 Data
9-12 week data finalised
The source data can be identified by their corresponding period
All workbooks will be .xlsx

The other worksheets may contain pivots of the raw data, or summary tables of the raw data, which will show the activity on one row for the 4 week period, instead of having n rows for that particular item.

Yes, the data should be added on the bottom of the data already existing on the worksheet, so it contains data from period 1, period 2, period 3... so on, when running the macro it should retains periods 1,2,3 and also then add on period 4 data.
In regards to the hint where the data comes from I am not sure I understand the question, the worksheet containing the data may not have the same naming convention as prior workbooks.
All raw data worksheets will contain headers, however in earlier periods, not all columns were present, e.g.
File from 2019 will have 3 rows e.g. student number, subject and Marks scored, but files from 2022 will have student first name, student last name, student id, subject, teacher and marks scored.

I am not sure what you meant by
"Name of worksheet resp. workbook?
Will that be the workbook with code?"

The collecting sheet will have the max number of columns, in relation to earlier raw data files containing some of the columns, the columns which do not marry, could be blank for the respective period.

Many thanks for your time.

 

[HaHoBe]

Hi ,

this what I have figured out by now from the information:

MrE_1229377_1702712_vba extracting data_230208.xlsm
	A	B	C	D	E	F
1	What	Folder	Workbook	Worksheets.Count	Worksheet for Data	Number of Columns
2	Folders	C:\Project\Raw Data Files\FY22-23
3	Folders	C:\Project\Raw Data Files\FY23-24
4	Source 1		Data for weeks [x-y]	3	Data for the period [xx-xx-xx]	10
5	Source 2		Weeks [x-y} Data	5	Raw Data	11
6	Source 3		[x-y] week data finalised	2	xx-xx-xx - yy-yy-yy	8
7	Collector					all columns from all sheets
MrE1702712

The [x-y] stands for the actual period of 4 weeks.

Right now I'm not sure how to get the proper worksheet for Source 3, can you give a bit more information what may be the name of the worksheet in question (all numbers, characters and numbers, only characters)?

As a year has 52 or 53 weeks where is the point to switch from Folder FY22-23 to FY23-24, will it be concerning week 25-28 or any other combination of weeks? Take the date from today I assume the week to be named1-4 and the folder to search for the files FY22-23. Will it be single week numbers for 1 to 9 or will the be formatted to always appear as double figures like 01-04?

As I lack information about the headers it would mean that the headers from the first workbook to open be the first on the collector sheet while additional columns might be added if needed.

Should the names of the (last) workbooks imported be noted anywhere in the collector sheet, workbook or a textfile to avoid doubling up on data and deleting duplicates later due to multiple imports?

As you can see from the Folders in XL2BB I'm working on Windows and it seems that you are on a Mac. I do not have the possibilty to test on a Mac but will test thoroughly on my system.

Sorry about all these questions.

Holger

 

[HaHoBe]

Hi,

how do you want to use the code:

• run on all workbooks in given folders rebuilding the target sheet
• run on all workbooks in given folders adding information to the target sheet from new workbooks
• run on the actual set of workbooks regarding date (either the last set available or the one before) to rebuild the target sheet
• run on the actual set of workbooks regarding date (either the last set available or the one before) to add to the target sheet if needed

I don't know if Power Query is able to do the job but it may be a good idea to have a look at PQ.

Holger