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

RobE

New Member
Joined
Apr 3, 2002
Messages
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
If you can post the code, there's always people looking for similar solutions
Many thanks
Rob
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top