Creating Master Database from Multiple Excel Workbooks

GMeier

New Member
Joined
Jun 25, 2008
Messages
5
I need to create a master “database” in an Excel Workbook or Access database by copying data from data contained in about 230 separate Excel Workbooks.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
The 230 separate Workbooks are contained in a unique folder and all have four digit numeric names. These Workbooks contain a common Sheet named “Upload” that contains the data to be accumulated into the master. The “Upload” Sheet contains a fixed range (not named, A3:AH253) containing the data to be accumulated. The data “fields” within the named range are identical throughout. The data does not need to be consolidated. The 230 separate ranges need to be appended to each other to create what I am calling the master “database”.
<o:p> </o:p>
Once the master “database” is created, the data will be converted to a CVS file and uploaded into an accounting system.
<o:p> </o:p>
Can someone take a shot at helping create the master “database”? Your assistance would be greatly appreciated by someone who does not like manual processing. Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
The first step I would take is to get all of that data into one workbook. You can use a simple formula to link to the desired data. Then, fill the formula down to the last row you need. then, the formula will need to change based on the name & tab of the next file. This is easier since they are numerical and same tab names.

You could use VBA to loop through the workbooks (changing the filename after X rows have been copied).
 
Upvote 0
BSOD,

Thanks for the reply. I am a bit green in this area; can you direct me to a thread(s) that would describe how to accumulate the data using the loop methodology? Thanks.

Greg
 
Upvote 0
Heres an example of what I believe it would be like. I think it would nested loops: 1 running for the workbook count, and the other for the cell references within the workbook.

'This assumes the workbooks are named like this: "workbook1", "workbook2", and that the range of data you need is located on rows 1 - 500 in columns A, B and C.

FILE= 1
Z = 1

Do While FILE <= 230 'number of workbooks here

For X = 1 to 500 'references number of rows.

Range("A" & Z).formula = "'c:\path\to\file\[workbook" & FILE & ".xls]upload'!$A" & X & ""
Range("B" & Z).formula = "'c:\path\to\file\[workbook" & FILE & ".xls]upload!$B" & X & ""
Range("C" & Z).formula = "'c:\path\to\file\[workbook" & FILE & ".xls]upload!$C" & X & ""

Next X

Z = Z + 1
FILE = FILE + 1

Loop

So the Do loop will loop through each workbook, the for loop references the cells within the current workbook. Z is a running count so after each loop, the data is not overwritten by the next loop.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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
Back
Top