Text is being over written when it's supposed to be appended

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?

Solution 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

Solution 2

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

Comments

[Stian]

That looks might fine to me...!

[Tom Studee]

Can you paste the code where you are actually writing and then closing the file?

[Siddharth Rout]

There is nothing wrong with your code. IF the file exists, it will append to it else it will create a New file. Can you confirm if the file exists? Just a shot in the dark:- What could be happening is that the file might not exist and you might be under the impression that it is overwriting whereas it is actually recreating it?

[Jean-François Corbett]

I tested your code and it works. There's nothing wrong with it. The error is elsewhere.

[Stian]

Const ForReading = 1, ForWriting = 2, ForAppending = 8

[Scott Holtzman]

@Stian - if I remember correctly, I tried playing with that, but it was screwing with me, which is why I ended up using the text version. It was some time ago and I got it solved, but I think that was my process, as I remember feeling the OP's pain!

[JimmyPena]

You should consider editing this into a Sub and declaring your variables and constants.

[Jean-François Corbett]

Why speculate? Try it yourself before posting. Had you done so, you would have seen that it doesn't compile with 3 as an argument. There's an error in the example in that old documentation; the newer FileSystemObject documentation should be consulted instead.

[Jean-François Corbett]

"I think you just need to change your number reference to ForAppending" from what to what? Question says 8, which is correct! Also you don't declare your variables?! -1

[Scott Holtzman]

@Jean-FrançoisCorbett, I would appreciate you removing the downvote. Perhaps it wasn't clear in the comment, but I was saying to the OP to change the number to the actual text ForAppending, which I tried to address again in my comment to Stian. I came across this same issue in the past and found that ForAppending worked, where 8 did not. As for declaring variables, I accidentally left that out, because I pulled this from a module with several functions, where I declared them for globally. I do however, appreciate your sentiment and will edit my answer for clarity and understanding.

[Jean-François Corbett]

Well, at least your edited answer is clear, so I'm removing my downvote. But since ForAppending*is* 8, substituting the one for the other will never change anything. Your error must have been somewhere else.

[Scott Holtzman]

Thanks @Jean-FrançoisCorbett. I would have thought the same thing, but I went batshit trying to figure it out. Hard to say the error was somewhere else when changing from '8' to 'ForAppending' solved it. Who knows!

[Steve Rindsberg]

The basics work nicely for ascii text but will trash the results if you're working with e.g Chinese/Japanese/etc. For that you need to write to a Unicode file. To do that, add True as the fourth parm to OpenTextFile.