Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Disregard the secon question.
    You already answered it.

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •