+ Reply to Thread
Results 1 to 7 of 7

Update txt files using vba

  1. #1
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Thumbs up Update txt files using vba

    I have a great number of .txt files in a directory which are all listed by name in column A of a spreadsheet and I need to open each of these files and update the fifth line with the data that corrospnds in column B and then save them. *Does anyone have any suggestions as to how i could use VBA to achieve this?
    Last edited by Tellm; 01-06-2014 at 06:59 AM.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Update txt files using vba

    Please Login or Register  to view this content.
    Assuming, as mentioned in the code, that the cells contain a fully qualified file name and there are no blank cells.

    You should consider adding error handling... If the files contain critical information, you should copy them to a backup directory first - there is no undo facility here.
    Last edited by cytop; 01-04-2014 at 04:29 PM.

  3. #3
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Update txt files using vba

    Cytop thanks, i'll try this out on Monday - Happy New Year

  4. #4
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Update txt files using vba

    Cytop, in anticipation I have tried to run this today, but I get a runtime error 53 file not found. The files are in a Directory C:\Temp\BB-Update and for the test have entered the full path, normally I would only have the file names without the path or the file extension, but I have added the full path and the file extension without success. Thanks

  5. #5
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Update txt files using vba

    Sorry to say but it works fine for me... As the error is 'File not Found' it is probably the way you are constructing the filename.

    Please Login or Register  to view this content.
    This code requires the directory to be added in cell C1. It uses a combination of directory and root filename in Col A to find a file ignoring the extension (I would assume you do not have files named xxx.txt and xxx.csv in the same directory).

    It also pops a msgbox if either the directory or any file listed in Col A does not exist...

    The choice of cell C1 is purely arbitrary - it can be anywhere, just change the code to suit.
    Last edited by cytop; 01-06-2014 at 05:50 AM. Reason: Typo

  6. #6
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Re: Update txt files using vba

    Cytop, sorry for the delay in responding. I have now run this and it works perfectly. Could you use this on files that have a different file extension? I can see a further use for this code but would need to change the extension to .CAM. The files are txt files that can be read by notepad and the like.

  7. #7
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Update txt files using vba

    It ignores extensions altogether... As long as the basic file name (without the path or extension) is unique in the directory, it will work.

    As small edit to the code in the previous thread. Replace
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    This is just to tighten up the logic a little and make sure only those files listed are found...
    Last edited by cytop; 01-06-2014 at 05:53 AM. Reason: Comment re code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. update master files from multiple children files
    By natalie.phuong in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2009, 03:34 AM
  2. [SOLVED] 2 files, mass update comments between files
    By Katycatchesnut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  3. [SOLVED] 2 files, mass update comments between files
    By Katycatchesnut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  4. [SOLVED] 2 files, mass update comments between files
    By Katycatchesnut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. [SOLVED] 2 files, mass update comments between files
    By Katycatchesnut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1