Write Data to a Txt File

  • I recently received excellent guidance here for obtaining data from a text file and writing it to a worksheet.


    I thought I'd be clever and try to write my own code to now do it in reverse - to write data back to a .txt file - using the coding that I thought I'd learned.


    Not so! All I've managed to do is completely delete everything that was previously in the .txt file! I'm guessing I missed adding something to actually write the data.


    What I'm trying to do is copy Range C3:C102 from the open worksheet to an existing text file. The data is to be written as a single column and can overwrite the existing data.


    My non-functional coding is:



    Help on sorting out my mess would be much appreciated.

  • Re: Write Data to a Txt File


    hi try

  • Re: Write Data to a Txt File


    ops should swap the columns and rows around

  • Re: Write Data to a Txt File


    There's a compile error when running the code.


    I'm running it from the open 'Lists' worksheet but when I do it stalls at the line: Set ExpRng = Sheet . . . . . . The word 'Sheet' is highlighted.


    The pop-up shows: Compile error: Sub or Function not defined.


    Also, I only want to copy Range("C3:C102") so I've commented out the column loop, set the range in the problem line to C3:C102 and set c = 3 in place of For c = 1 to iCols. Hopefully those changes are correctly done.

  • Re: Write Data to a Txt File


    Try


    As I'm just typing the code in from my phone

  • Re: Write Data to a Txt File


    That cured the compile problem but further on there's a second problem. I now get an error pop-up advising: Run-time error '54': Bad file mode


    Running Debug, the line 'Line Input #1, strLine' is now highlighted.

  • Re: Write Data to a Txt File


    oh yeah that wont work
    try this

  • Re: Write Data to a Txt File


    Brilliant pike - that nailed it!


    This coding follows on from yesterday where you coded the split-up of a .txt file into four columns. As you may have gathered, I'm now writing each column to its own file. I've duplicated your amended block of code, changed the .txt file names and have now successfully written to the first three files and will do the fourth after posting this.


    Great - another piece in place! I've got one more I'd like to sort out this afternoon; I'll post a new thread shortly - watch for it if you're not yet over my coding issues! But hey - thanks so much for your help - again