1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Need Workbook Linked to Many Workbooks

Discussion in 'Business Applications' started by BARBBARELA, Feb 15, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    Microsoft Excel 2003 - I need help creating a macro that will link a workbook file with hundreds of other workbooks to create a customer list. All my workbooks are in C:\Documents 2007\Bids\BID____.xls. All files begin with "BID". All workbooks are identical and I only need information from certain cells. (i.e. G8, A6, G6, etc.). I want all the information from each file to go onto one row of the customer list - a new file's information on each row. Does anyone know how to do this? I have spent all day working on it and am about to go out of my mind. I would really appreciate any help. Thanks. :confused:
     
  2. harish

    harish

    Joined:
    Feb 12, 2007
    Messages:
    13
    Upto Lotus 123/MS-Excel 5.0 ACL was used to write macros. But with coming of VB relative reference has become difficult (if not impossible). Over the weekend I will have some spare time. I will try to work out your problem. I have faced similar type of problem in the past, which I solved by writing a number of macros instead of a single macro. Please wait (if you can) till Sunday evening (IST).
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Barbbarela, it would help a lot if you post one of your Excel Workbooks as an attachment.
    It does not have to have "Real Data" in it, but it will give posters the "Format" and structure of the data.
    A blank version of the "New workbook" showing what goes where would also help a lot.
    Have you also considered using an Access database for this application?
    As it looks as if it is "Growing" much bigger than you originally envisaged.
     
  4. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    Thanks, that's perfect. I can't work on it again until Monday anyway. Thanks again.
     
  5. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    OBP,
    I will post an example attachment on Monday. I won't be back into my office until then. That's a good idea. Thanks.
     
  6. harish

    harish

    Joined:
    Feb 12, 2007
    Messages:
    13
    Using MS-Access is a good idea (suggested by OBP).

    If you are familiar with MS-Access I will show you how to Link MS-Excel files and create a query in MS-Access. Then revert back to Excel (because you seem to be comfortable with Excel) for data by using the 'Automatic query update option on Fileopen' property of Excel Database your can retrieve the data in Excel exactly the way you want it. Use Auto Filter to view data.
    It won't work in your case because you are dealing with a lot of files containing a lot of records (your query). The limit is 65,536 records. There is a MS-Excel version for million records you have to purchase it separately from Microsoft.
     
  7. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    I have never used MS-Access before. I think I have attached a copy of the file we are using. The red cells are the information we need in a database. (There are six at the top and the total at the bottom) I appreciate all your help. :confused:
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Barbbarela, do you want the data "Linked" as stated in Post #1 or just copied and pasted?

    Do you want me to do this Excel or create an Access Database?
    An Access Database can do the Bidding Part as well.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Barbbarela, I have looked at your workbook and created a macro to transfer the data to a summary sheet (just the one sheet) at the moment.
    I have a big question though, is the format in the "Red Cells" actually

    General Contractor Name John Doe
    Contact Name Fred Flintstone

    i.e. is there descriptive text in those cells as well as the actual Data that you need?
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Barbbarela, please see the attached workbook, this has a macro (run by Ctrl + a) that takes the indicated data from your attached workbook and puts it in the Summary Workbook, All it needs is the code to open each workbook in turn for it to do what you want, but I need that question answered.
    Have a look at it and tell me if it does what you "expected" it to do.
    If it doesn't then let me know what it needs.
    If it is to do with my question then please provide another workbook with some "Dummy" actual format data.

    I am in the UK and won't be able to do any more on this until tomorrow now.
     

    Attached Files:

  11. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    In the box it does not have to say "General Contractor". It can just have the name of the general contractor and the same for the rest of the cells. The date needs to come first and then the contractor and the rest in the same order. But so far it looks AWESOME. Thanks OBP for all your time and effort. :)
     

    Attached Files:

  12. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    I forgot to tell you all bid files will be saved in H:\documents\documents 2007\bids. Thanks.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    BARBBARELA, I think you will find that this does what you need.
    To test is first create the following folders on your Drive C:.
    c:\documents\documents 2007\bids
    which imitates your Drive H: path but on drive C:.
    Place the attached files in the "bids" folder.
    Open the "Bid Summary" workbook and run the macro with Ctrl+a.
    You should get a message (not actually necessary) saying that it has found the 3 files and then the data should appear in your Summary as you required.
    I will attach the Summary Workbook on a seperate post.
     

    Attached Files:

  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    The Bid Summary Workbook.

    Let me know if you want the Summary converted to an Access Database, this import can be done just as easily in to Access as it is in Excel.
     

    Attached Files:

  15. BARBBARELA

    BARBBARELA Thread Starter

    Joined:
    Oct 3, 2003
    Messages:
    19
    It works perfectly. I have already moved it over into H:\ and changed the macro. THANK YOU SO MUCH FOR YOUR TIME AND EFFORT. I really appreciate it.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544455