Appending a file

[Bythsx-Addagio]

Hello,
I have the following code to combine the contents of all the text files in
one folder, into one single text file.

The problem is, I get an error 70 Permisson denied on the second time I try
to open the CombindedFile. I don't beleive the file is actually being
opened, but do I need to close it or deactivate it after the first write?

Thanks!

Sub Append_Txt()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject
Dim oTS As TextStream
Dim oTS1 As TextStream
Dim vTemp

Dim Directory As String
'*************

Directory = "C:\New Folder\"
ChDir Directory

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject

f = Dir(Directory, 7)

Do While f <> ""

Set oTS = oFS.OpenTextFile(Directory & f, ForReading)
vTemp = oTS.ReadAll

Set oTS1 = oFS.OpenTextFile("C:\CombinedTemp.txt", ForAppending, True)
oTS1.Write (vTemp)
f = Dir
Loop
End Sub

 

[Dave Peterson]

I don't see

oTS1.close
after the loop

And I don't see
oTS.close inside the loop--after you've done the .readall

 

[Bythsx-Addagio]

Is this what you suggest? I still get the permission error when trying to
open the CombinedTemp.txt

Do While f <> ""

Set oTS = oFS.OpenTextFile(Directory & f, ForReading)
vTemp = oTS.ReadAll
oTS.Close

Set oTS1 = oFS.OpenTextFile("C:\CombinedTemp.txt", ForAppending, True)
oTS1.Write (vTemp)


f = Dir
Loop
oTS1.Close

 

[Dave Peterson]

I didn't notice that you were opening the output within the loop. Just opening
it once should be sufficient:

Option Explicit
Sub Append_Txt()

Dim oFS As FileSystemObject
Dim oTS As TextStream
Dim oTS1 As TextStream
Dim f As String
Dim vTemp As String
Dim myDirectory As String

myDirectory = "C:\my documents\excel\test\"
ChDrive myDirectory
ChDir myDirectory

Set oFS = New FileSystemObject

f = Dir(myDirectory, 7)

Set oTS1 = oFS.OpenTextFile("C:\CombinedTemp.txt", ForAppending, True)
Do While f <> ""
Set oTS = oFS.OpenTextFile(myDirectory & f, ForReading)
vTemp = oTS.ReadAll
oTS.Close
oTS1.Write vTemp
f = Dir
Loop

oTS1.Close

End Sub

Ron de Bruin has alternate methods:
http://www.rondebruin.nl/csv.htm