Excel VBA append current cell to a text file

[Nu2Java]

Hi All - I am using this code to basically re-write a range in Column A to a text file. My requirements have now changed to where I need to append to the text file with the current cell in Column A rather than re-writing the entire range. For example, I have the users scanning barcoded part numbers in column A starting at Row 9. Once scanned, the active cell moves down. What I need to do is append to the file at each cell, but cannot seem to get it without getting 'bad file' errors when changing 'CreateTextFile' to 'OpenTextFile'

Public Sub SaveTextToFile()

    Dim iCntr
    Dim filePath As String
    filePath = "C:\scripts\SMTscans.txt"

    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream

    Set fileStream = fso.CreateTextFile(filePath)
    
    Dim LastRow As Long
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    For iCntr = 9 To LastRow
        fileStream.WriteLine Range("A" & iCntr)
    Next iCntr
  
    fileStream.Close

End Sub

 

[Marc L]

Hi, external FSO is useless as VBA has all the necessary : just using VBA Open statement with For Append parameter …​

​

 

[BobBridges]

Don't pay any attention to Marc, nu2java; he knows a lot about Excel, more than me anyway, but I think the FileSystemObject is wonderful and use it all the time.

Anyway, that wasn't your question. Let's see, your current statement reads "Set fileStream = fso.CreateTextFile(<filename>)", and you want to use OpenTextFile instead. Seems to me the statement should, therefore, read like this:

Set fileStream = fso.OpenTextFile(<filename>, ForAppending, False)

  ' ... or, if your VBE doesn't recognize ForAppending:

Set fileStream = fso.OpenTextFile(<filename>, 8, False)

The third argument, False, tells it that if the file doesn't exist your program should abend instead of just creating a new copy; otherwise you might not realize when you give it the wrong filename or something.

So what does your statement actually look like?

 

[Nu2Java]

BobBridges... thanks a lot for your help on this, much appreciated. I was able to get this to work after seeing your reply. I was missing the arguments (DOH!). Its working great now.