Results 1 to 11 of 11
  1. #1
    BakerMan
    Guest

    Merge/Combine Workbooks (2000)

    I have a report that is distributed throughout the company. It is for accidents. The report is fairly simple with who,when,where,why, and a lot of how information.
    When someone is injured they email the report to us.
    The data from this report is then extracted and placed in an Excel database(table) so it can be sorted etc.
    I need a direction to go in (i.e. macro, links etc.) to combine the data into the data base without having to manually type or copy/paste the data.
    The format of the user sheet will always be the same, but the sheet name that is sent in may not always be the same name.
    Can you start me in the right direction??? My thoughts are "a macro to allows me to open any sheet I want, then have the data copy to the data base sheet.." You folks may have a better idea...thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge/Combine Workbooks (2000)

    It will be helpful if you can send us a sample of the report and of the excel database. We can use this as a template to develop a macro to automate the precedure. It may tell us if this is necessary or if links can suffice.

    Clean out any confidential or sensitive data and send us something to work with!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  3. #3
    BakerMan
    Guest

    Re: Merge/Combine Workbooks (2000)

    Here is the report. Note that I have a macro in place to automatically save the workbook to the injured's name and date.

  4. #4
    BakerMan
    Guest

    Re: Merge/Combine Workbooks (2000)

    Here is the database - so far - with no entries

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge/Combine Workbooks (2000)

    A macro is certainly going to be your best option here. It is going to be quite a task though, as the report contains an enormous amount of info. An idea of a macro could be to copy the report into the database file and copy all the necessary details into the list. Once its done that it can descard of the copied report. I will not be able to get a working sample for you today, as I will be leaving for home soon. I will copy your files to disk and see if I can post some sample code for you tomorrow.

    Keep your eyes posted, as another lounger may have time and post a solution sooner.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  6. #6
    BakerMan
    Guest

    Re: Merge/Combine Workbooks (2000)

    Much appreciated

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merge/Combine Workbooks (2000)

    An "easy approach" might be to have a hidden sheet which reads each of the approapriate the cells in the "form" directly and have them in a line. Then just have the macro read these formulas and paste special -values into the next row of the database.

    The Hidden sheet could be created by copying the cells in the form as links then moving all those cells with linked formulas in the proper sequence in 1 row. The you can delete the rest of the items.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  8. #8
    BakerMan
    Guest

    Re: Merge/Combine Workbooks (2000)

    Good idea. But how to get the macro in the database to read the range of values in the report is my problem. Can you make a macro read from "Any other " open workbook no matter what it is named?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Merge/Combine Workbooks (2000)

    What do you mean by "any other". You can read from the activeworkbook, or you can read from a specific workbook (but yo must be able to tell VB its name) or you can loop thru all the open workbooks and only take from particular ones (you would have to set the criteria. The criteria may be something simple like having a hidden sheet named "My Hidden Sheet" or whatever you want.

    I assumed that you would know the name of the workbook with the database and know the name of the workbook with the "form" so it would be easy to copy from one to the other.

    Workbooks("Book1.xls").Worksheets("Sheet1").Range( "A1:Q1").Copy _
    Workbooks("Book2.xls").Worksheets("Sheet1").Range( "A65536").End(xlUp).Offset(1, 0)

    This copies the range A1:Q1 from Sheet1 of Book1.xls to the first blank cell after the data in col A of Book2 sheet1. Change book names, sheetnames and the range as appropriate.

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge/Combine Workbooks (2000)

    Here is an automated approach to the task. Pass it around and see if it can be further improved. I did not create all the code so the import is incomplete. (I did not know where to collect some of the details you have as column headers. Open the code and copy the lines to import the rest of the data for the list.

    Its the list that is automated. It pulls a copy of the report sheet, fills the list and deletes the sheet.
    Regards,
    Rudi

  11. #11
    BakerMan
    Guest

    Re: Merge/Combine Workbooks (2000)

    Thankyou for your help, sorry for the late reply

Posting Permissions

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