Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel - Need Workbook Linked to Many Workbooks


(!)

BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
15-Feb-2007, 09:40 PM #1
Excel - Need Workbook Linked to Many Workbooks
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.
harish's Avatar
harish harish is offline harish has a Profile Picture
Computer Specs
Junior Member with 13 posts.
 
Join Date: Feb 2007
Experience: Advanced
15-Feb-2007, 11:33 PM #2
Thumbs up Writing a macro
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).
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
16-Feb-2007, 07:08 AM #3
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.
__________________
OBP
I do not give up easily
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
16-Feb-2007, 11:12 AM #4
Thanks, that's perfect. I can't work on it again until Monday anyway. Thanks again.
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
16-Feb-2007, 11:15 AM #5
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.
harish's Avatar
harish harish is offline harish has a Profile Picture
Computer Specs
Junior Member with 13 posts.
 
Join Date: Feb 2007
Experience: Advanced
17-Feb-2007, 12:27 AM #6
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.
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
19-Feb-2007, 02:11 PM #7
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.
Attached Files
File Type: xls BIDFORM.xls (27.5 KB, 93 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
19-Feb-2007, 02:33 PM #8
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
19-Feb-2007, 03:15 PM #9
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?

Last edited by OBP; 19-Feb-2007 at 03:30 PM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
19-Feb-2007, 03:35 PM #10
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
File Type: xls Bid Summary.xls (22.0 KB, 74 views)
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
19-Feb-2007, 06:06 PM #11
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
File Type: xls BIDFORM.xls (32.0 KB, 88 views)
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
19-Feb-2007, 06:11 PM #12
I forgot to tell you all bid files will be saved in H:\documents\documents 2007\bids. Thanks.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
20-Feb-2007, 11:10 AM #13
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
File Type: xls BIDFORM0.xls (28.5 KB, 59 views)
File Type: xls BIDFORM1.xls (28.5 KB, 72 views)
File Type: xls BIDFORM2.xls (28.5 KB, 66 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
20-Feb-2007, 11:12 AM #14
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
File Type: xls Bid Summary.xls (29.0 KB, 95 views)
BARBBARELA's Avatar
BARBBARELA BARBBARELA is offline   BARBBARELA has a birthday soon!
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Oct 2003
Location: Oklahoma
20-Feb-2007, 01:16 PM #15
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑