Text is being over written when it's supposed

Question

This seems really easy (I've done it a million times and never had a problem), but it's killing me.

I want to create some SQL scripts based on content in an Excel spreadsheet. To do this I've created a macro that reads a text file using the code below

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim stream As TextStream
Set stream = fso.OpenTextFile(filepath, 8, False)

This is supposed to open the text file for appending and plug in my new values.

Unfortunately, it's always overwriting instead of appending, and it's driving me nuts.

Any ideas?

Answer 1

I just recently built a function to Append Strings to a File. I came across this issue just a few weeks / months ago and found that if used the actual word ForAppending, just as it shows up in Intellisense, insted of the number 8 it worked for me.

Const ForAppending = 8

Sub AppendStringToFile(ByVal strFile As String, ByVal strNewText As String, Optional intBlankLine As Integer = 1)

Dim fso as FileSystemObject, ts as TextStream

Set fso = New FileSystemObject
Set ts = fso.OpenTextFile(strFile, ForAppending, True)

With ts
    .WriteBlankLines intBlankLine
    .WriteLine (strNewText)
    .Close
End With

Set ts = Nothing
Set fso = Nothing

End Sub

Answer 2

It's very odd, in this documentation, it mentions that the constant corresponding to ForAppending is 8, but it uses 3 in the example at the bottom.

Try:

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim stream As TextStream
Set stream = fso.OpenTextFile(filepath, 3, False)

Answer 3

Drop back to basics....
Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

used for your requirements:

Dim FNum As Integer

FNum = FreeFile()
Open strFile For Append As FNum

'do your stuff here
Write #FNum, MyData

Close FNum