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?
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