Results 1 to 4 of 4

Creating Master Database from Multiple Excel Workbooks

This is a discussion on Creating Master Database from Multiple Excel Workbooks within the Microsoft Access forums, part of the Question Forums category; I need to create a master “database” in an Excel Workbook or Access database by copying data from data contained ...

  1. #1
    New Member
    Join Date
    Jun 2008
    Posts
    5

    Default Creating Master Database from Multiple Excel Workbooks

    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.

    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”.

    Once the master “database” is created, the data will be converted to a CVS file and uploaded into an accounting system.

    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.

  2. #2
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    384

    Default Re: Creating Master Database from Multiple Excel Workbooks

    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).
    -bs0d | AllSyntax

  3. #3
    New Member
    Join Date
    Jun 2008
    Posts
    5

    Default Re: Creating Master Database from Multiple Excel Workbooks

    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

  4. #4
    Board Regular bs0d's Avatar
    Join Date
    Dec 2006
    Location
    Where the Hoot Owls Screw the Chickens
    Posts
    384

    Default Re: Creating Master Database from Multiple Excel Workbooks

    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 by bs0d; Aug 29th, 2008 at 11:42 AM.
    -bs0d | AllSyntax

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com