Appending workbooks

  • I have 50 workbook files. One workbook is a master report that is made from the 50 workbooks. I have to cut and paste the data from each workbook into the master workbook. I am in the process of automating the workbook to simplfy the process. I have reviewed several of the post here and have not seen a solution to my problem. Is there a way to automatically (marco,code) append the data from the 50 workbooks into the master workbook?


    Also I have attached a file representing what I need in a picture view.
    Thank you
    Cyrus

  • Re: appending workbooks


    Cyrus


    Have you tried a search in the Excel forum rather than this (Access) one?


    This is a common request and I'm sure you could find something you can adapt for your own circumstances.

  • Re: appending workbooks


    Cyrus,


    I agree with Norie.


    Have a look at this post that I answered the other day:
    http://www.ozgrid.com/forum/showthread.php?t=29907


    I am sure that you could adapt it to suit your data. Note that there are versions that create a worksheet and another one that creates workbooks with some other mods. Do read the whole post and look at the files and code.


    If you then need help please post back to this thread (do not start another one or reply to the link I have given) and we will try to help.


    Thanks,


    Alan.

  • Re: appending workbooks


    Hi Alan,
    Thanks for ur response, I read the entire post but it does not suit to my suituation, If you could see that picture that I have attached you can have a clear idea what I am looking for. Please let me know if I need to explain more.. Let me explain it through a small eg:
    I have 5 files.


    File one contains
    1. Emp name
    2 Emp No
    3 Emp Email
    4 Emp Address
    5 Amount


    2 nd file contains
    1. Emp name
    2 Emp Email
    3 Emp Address
    4 Amount
    5 Emp No
    Note: It is not in the same order as in file one.
    3rd file contains
    1. Emp name
    2 Emp Email
    3 Amount
    4 Emp No
    Note: Emp addres column is missing.
    But if you look into it all the three file contains Emp name, Emp Email and Amout. I just want to consolidate or append the values for these Emp name, Emp Email and Amount to a single file.


    As it is not in the same order and one or 2 columns might be missing in each file it is hard to copy and paste, but all the file contains Emp name, Emp Email and amount as common, I just need that to get append to a single file


    As there are more than 50 files it is hard to copy and paste. Just we need to append the values based on the column names.


    Any help is greatly appreitated.
    Please let me know if I need to explain more .... I badly need a resolution.
    Thanks in advance.

  • Re: appending workbooks


    Cyrus,


    My apologies I think the crucial word in your original post was

    Quote

    I have to cut and paste the data from each workbook into the master workbook.


    and not from which is the subject of the other threads.


    Now on to finding a solution.
    First a few questions.


    How many records are there in each of the 50 workbooks that you are combining into one?


    How many of the workboks have the same structure? (How many are of type file 1 and type file 2 in your diagram?)


    Are the field names in each file definitely the same (ie Emp Name is Emp Name in all files)? Can you make them the same?


    If there are hundreds of records per file then this post was correctly located in Access as that is probably the best for bring the data together.


    If there are only a few records in each file then copying and pasting in Excel would be easiest.


    Do you know Access?
    Can you link tables?
    Can you build queries in the query designer?
    Do you know the different types of queries:
    Select?
    Make Table?
    Append?


    Sorry for the delay in getting this going!


    Alan.

  • Re: Appending workbooks


    Hi cyrus


    I can help u in solving
    had a experience in appending 36 workbooks with 45 sheets into many reports


    for this, i need some samples of origin files and master file
    if u cant post, mail me at [email protected]


    regards, Youtham

  • Re: Appending workbooks


    Thanks for your reply Alan and Youtham.. Let me answer Alan First..
    1. Each file contains around 1000 records.
    2. I am Sorry I can't answer ur 2 nd question it is hard to tell. Actually each file contains 25 columns and I need to append only 10 columns which is common in all the files.. Just to explain u with an eg I told u that it has 5 columns and need to append those 3 common (emp name, emp email & amount).
    3. Yes all the field names which I need to append are definelty the same in each files
    and I know Access basic, I can make a table, link a table..but don't know to write queries..(I am at the learning stage) I appologize..


    Youtham...
    I am sorry, I would like to send the sample file but it is so large to send it...


    Eagerly waiting for a solution.
    Thanks in advance
    Cyrus.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!