General Excel Question

robfo0 · Mar 20th, 2002, 06:18 PM

I have a question. I havent actually started working on this project yet because i wanted to see how it would work, I'm sure someone here has done it before.

Its regarding links in an excel file to other excel files. I have an excel file for each customer (about 3,000 files currently). I also have a database with all of the names in it. What i would like to do is make a report of each customer. I would like the report to contain certain information from each customer file (I probably wont be collecting data from all 3,000 files, but maybe files from this month). My idea was to create a link from my main database of names to each customer file. My question is: Is this a good idea? would updating all the links cause instability in excel? Is there a better way to do this? I also thought about possibly making a macro that opens each file and gets the data. Would this be a better solution?

I think access would be a better option for me right now, but its a bit late for that and i have no idea how to use it

Thanks very much.

Jay Petrulis · Mar 20th, 2002, 06:32 PM

Hi,

My 2 cents...

Access would be a better option, although it would be an intensive data entry task.

I wouldn't recommend the links option because that might cause the recalc time to slow to a crawl. Hyperlinking might require you to store all the files in a certain folder and not move and/or change the names.

Your best non-database bet might be to use a macro which prompts you for the files to load/data to retrieve at runtime (or automatically if criteria can be discerned.

Regards,
Jay

Tom Schreiner · Mar 21st, 2002, 06:16 AM

Hi.

I work with large numbers at work and have found a good alternative to Access. I am not familiar with Access either...

I am assuming that each sheet is set up the same way for each customer?

If this is the case, I would create a macro which would store each individual customer's info in a regular text file.

Then you could create another macro to recall based upon whatever criteria you wish.
Customer Name, ID number, Phone Number, ect...

If you want, send me one of the 3000 sheets and I'll write a macro which will convert each seperate sheet into a smaller, much faster file.

You will then have one sheet in Excel to display all of your customer info...

It's basically a customizable database with alot less overhead then 3000 seperate sheets.

TsTom@Hotmail.com

Good Luck!

Sounds like a fun little project

Tommy Bak · Mar 21st, 2002, 07:58 AM

Hi
you could make a macro, so when you put an X beside a costumer, it would automatic create the links to certain cells in the specific customerfile.
This way you will limit the amount of links.
This can only be made if you know exactly path / filename for each customer.

regards Tommy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 06:18 PM	#1
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	I have a question. I havent actually started working on this project yet because i wanted to see how it would work, I'm sure someone here has done it before. Its regarding links in an excel file to other excel files. I have an excel file for each customer (about 3,000 files currently). I also have a database with all of the names in it. What i would like to do is make a report of each customer. I would like the report to contain certain information from each customer file (I probably wont be collecting data from all 3,000 files, but maybe files from this month). My idea was to create a link from my main database of names to each customer file. My question is: Is this a good idea? would updating all the links cause instability in excel? Is there a better way to do this? I also thought about possibly making a macro that opens each file and gets the data. Would this be a better solution? I think access would be a better option for me right now, but its a bit late for that and i have no idea how to use it Thanks very much.

Mar 20th, 2002, 06:32 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, My 2 cents... Access would be a better option, although it would be an intensive data entry task. I wouldn't recommend the links option because that might cause the recalc time to slow to a crawl. Hyperlinking might require you to store all the files in a certain folder and not move and/or change the names. Your best non-database bet might be to use a macro which prompts you for the files to load/data to retrieve at runtime (or automatically if criteria can be discerned. Regards, Jay

Mar 21st, 2002, 06:16 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi. I work with large numbers at work and have found a good alternative to Access. I am not familiar with Access either... I am assuming that each sheet is set up the same way for each customer? If this is the case, I would create a macro which would store each individual customer's info in a regular text file. Then you could create another macro to recall based upon whatever criteria you wish. Customer Name, ID number, Phone Number, ect... If you want, send me one of the 3000 sheets and I'll write a macro which will convert each seperate sheet into a smaller, much faster file. You will then have one sheet in Excel to display all of your customer info... It's basically a customizable database with alot less overhead then 3000 seperate sheets. TsTom@Hotmail.com Good Luck! Sounds like a fun little project

Mar 21st, 2002, 07:58 AM	#4
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi you could make a macro, so when you put an X beside a costumer, it would automatic create the links to certain cells in the specific customerfile. This way you will limit the amount of links. This can only be made if you know exactly path / filename for each customer. regards Tommy