144 workbooks - need to add a column of data to each

RobE · May 1st, 2002, 04:08 PM

I have 144 workbooks all of the same format (orders sent in by customers). Each has all the same data in the same columns but are of different lengths depending on the number of lines on the order.
I need to open each one in turn and add the name of the file to the next Column (M), then close and save the file and repeat on all 144 files. (NB there will be no blank rows but there are a couple of blank columns)

Sample data of 3 rows
5279427 Biz Mr Fred Fox ABC Co 22 St John Street Dudley Road Brierley Hill London DY51LQ
5268422 Biz Mr D Jones My Co 123 the lane Bartholomew Wy Horsham Manchester RH12 5HJ
5275808 BIz Mr Lee Smith Smith Partners 5 the Road Pembroke Coventry SA71 5PZ

Any ideas. I can get a sheet with all the filenames etc and everything is saved in one place and I can save over the original files

Thanks
Rob

Tom Schreiner · May 1st, 2002, 05:02 PM

Hi Rob
Just want to be clear..
You need to open 144 files, add the filename of each to column M?
So, for instance, the first file opened is:
"CustomerName.xls"
Is that all you need to place in Column M?
Or the complete path such as:
"C:CustomerDataCustomerName.xls"

In column M in row number what?

Or are you wanting to combine all of this data from the seperate files onto one Workbook? Then adding the filename to Column M?

Thanks,
Tom

RobE · May 2nd, 2002, 01:09 AM

Just looking to add the filename not the complete path.
Don't want to combine all the sheets onto one, want to keep each file seperate
Want the filename added to each line. The reasoning is that the customer has not sent the date of order submission in the file but it is part of the filename. So if I can add the filename to the end of each line then I can extract the order date from it.

Tom Schreiner · May 2nd, 2002, 01:22 AM

Rob,
I would simply dump the files into one folder and run a procedure from a seprate workbook or a standalone VB exe.
Do you want me to post the VBA code here, mail it to you, or send you a VB executable?
Also, another ?, does the filename go on every line in column M or just M1?
It would seem that one cell with a reference to the filename would suffice???
Tom

Tom Schreiner · May 2nd, 2002, 01:24 AM

Disregard the secon question.
You already answered it.

RobE · May 2nd, 2002, 01:33 AM

If you can post the code, there's always people looking for similar solutions
Many thanks
Rob

Tom Schreiner · May 2nd, 2002, 01:41 AM

I'll post it asap...
Not on my home computer
Tom

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

May 1st, 2002, 04:08 PM	#1
RobE New Member Join Date: Apr 2002 Posts: 23	I have 144 workbooks all of the same format (orders sent in by customers). Each has all the same data in the same columns but are of different lengths depending on the number of lines on the order. I need to open each one in turn and add the name of the file to the next Column (M), then close and save the file and repeat on all 144 files. (NB there will be no blank rows but there are a couple of blank columns) Sample data of 3 rows 5279427 Biz Mr Fred Fox ABC Co 22 St John Street Dudley Road Brierley Hill London DY51LQ 5268422 Biz Mr D Jones My Co 123 the lane Bartholomew Wy Horsham Manchester RH12 5HJ 5275808 BIz Mr Lee Smith Smith Partners 5 the Road Pembroke Coventry SA71 5PZ Any ideas. I can get a sheet with all the filenames etc and everything is saved in one place and I can save over the original files Thanks Rob

May 1st, 2002, 05:02 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Rob Just want to be clear.. You need to open 144 files, add the filename of each to column M? So, for instance, the first file opened is: "CustomerName.xls" Is that all you need to place in Column M? Or the complete path such as: "C:CustomerDataCustomerName.xls" In column M in row number what? Or are you wanting to combine all of this data from the seperate files onto one Workbook? Then adding the filename to Column M? Thanks, Tom

May 2nd, 2002, 01:09 AM	#3
RobE New Member Join Date: Apr 2002 Posts: 23	Just looking to add the filename not the complete path. Don't want to combine all the sheets onto one, want to keep each file seperate Want the filename added to each line. The reasoning is that the customer has not sent the date of order submission in the file but it is part of the filename. So if I can add the filename to the end of each line then I can extract the order date from it.

May 2nd, 2002, 01:22 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Rob, I would simply dump the files into one folder and run a procedure from a seprate workbook or a standalone VB exe. Do you want me to post the VBA code here, mail it to you, or send you a VB executable? Also, another ?, does the filename go on every line in column M or just M1? It would seem that one cell with a reference to the filename would suffice??? Tom

May 2nd, 2002, 01:24 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Disregard the secon question. You already answered it.

May 2nd, 2002, 01:33 AM	#6
RobE New Member Join Date: Apr 2002 Posts: 23	If you can post the code, there's always people looking for similar solutions Many thanks Rob

May 2nd, 2002, 01:41 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I'll post it asap... Not on my home computer Tom