Write Data to a Txt File

OldFella · Apr 9th 2016

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:

Code

Sub WriteM400AD()
'
Dim strLine As String
Dim strFile As String
Dim iRow As Integer
Dim iColumn As Integer
    strFile = "F:\SM Lists\M400AD.txt"
    iRow = 2
    iColumn = 3
    Open strFile For Output As #1
    Do While Not EOF(1)
        iRow = iRow + 1
        Line Input #1, strLine
        Cells(iRow, iColumn).Value = strLine
   Loop
    Close #1
    Windows("SM_Performance.xlsm").Activate
    Range("K2").Select
'
End Sub

Display More

Help on sorting out my mess would be much appreciated.

**pike** · Apr 9th 2016

Re: Write Data to a Txt File

hi try

Code

Option Explicit


Sub WriteM400AD()
'
    Dim strLine As String
    Dim strFile As String
    Dim iRows As Long, iCols As Integer, r As Integer, c As Integer


    strFile = "F:\SM Lists\M400AD.txt"
    Set ExpRng = Sheet("Lists").Range("A3:C102")
    iCols = ExpRng.Columns.Count
    iRows = ExpRng.Rows.Count


    Open strFile For Output As #1
    For r = 1 To iRows
        For c = 1 To iCols
           strLine = ExpRng.Cells(r, c).Value
            If Not IsEmpty(strLine) Then
                Line Input #1, strLine
            End If
        Next c
    Next r
    Close #1


    '
End Sub

Display More

**pike** · Apr 10th 2016

Re: Write Data to a Txt File

ops should swap the columns and rows around

Code

Sub WriteM400AD() 
    Dim strLine As String 
    Dim strFile As String 
    Dim iRows As Long, iCols As Integer, r As Integer, c As Integer 
     
    strFile = "F:\SM Lists\M400AD.txt" 
    Set ExpRng = Sheet("Lists").Range("A3:C102") 
    iCols = ExpRng.Columns.Count 
    iRows = ExpRng.Rows.Count 
     
    Open strFile For Output As #1 
       For c = 1 To iCols 
           For r = 1 To iRows 
        strLine = ExpRng.Cells(r, c).Value 
            If Not IsEmpty(strLine) Then 
                Line Input #1, strLine 
            End If 
        Next r 
    Next c 
    Close #1 
End Sub

Display More

OldFella · Apr 10th 2016

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.

**pike** · Apr 10th 2016

Re: Write Data to a Txt File

Try

Code

sub WriteM400AD() 
    Dim strLine As String 
    Dim strFile As String 
    Dim iRows As Long, iCols As Integer, r As Integer, c As Integer 
     
    strFile = "F:\SM Lists\M400AD.txt" 
    Set ExpRng = Sheets("Lists").Range("A3:C102") 
    iCols = ExpRng.Columns.Count 
    iRows = ExpRng.Rows.Count 
     
    Open strFile For Output As #1 
    For c = 1 To iCols 
        For r = 1 To iRows 
            strLine = ExpRng.Cells(r, c).Value 
            If Not IsEmpty(strLine) Then 
                Line Input #1, strLine 
            End If 
        Next r 
    Next c 
    Close #1 
End Sub

Display More

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

OldFella · Apr 10th 2016

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.

**pike** · Apr 10th 2016

Re: Write Data to a Txt File

oh yeah that wont work
try this

Code

Option Explicit


Sub WriteM400AD()
    Dim strLine As String
    Dim strFile As String
    Dim ExpRng As Range
    Dim iRows As Long, iCols As Integer, r As Integer, c As Integer


    strFile = "F:\SM Lists\M400AD.txt" 
    Set ExpRng = Sheets("Lists").Range("A3:C102")
    iCols = ExpRng.Columns.Count
    iRows = ExpRng.Rows.Count


    Open strFile For Output As #1
   
        For c = 1 To iCols
         For r = 1 To iRows
           strLine = ExpRng.Cells(r, c).Value
            If Not strLine = vbNullString Then
                Print #1, strLine
            End If
        Next r
    Next c
    Close #1


End Sub

Display More

OldFella · Apr 10th 2016

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

Write Data to a Txt File

Participate now!

Share

Users Viewing This Thread