need help: append 3 exported text files (opentextfile)

[maryenc]

Hello,

I need to take 3 text files that I am exporting from an Access db and append all 3 files together. Through my searching today, it appears that I need some code using OpenTextFile. I'm no good at VBA and my attempt at copying, pasting, and editing some prewritten code to do this has not been successful. Can anybody help me out here?

Thanks!

 

[Uncle Gizmo]

Please could you explain why it is necessary to export the data out of MS Access, and then append it. Surely it would be much simpler to append the data first in MS Access and then export it?

 

[Surjer]

Solution A.)
Create a query to group the data together you want and then export out of Access.

Solution B.)
Create a new File and then open each file for reading and append to new file.
If solution B is your only option search for FileSystemObject.

 

[maryenc]

Ah, good question. The 3 files don't have the same fields. All 3 files must go into one transaction file for upload to our mainframe. Example...

$$$NCF1CWS006100105CWS PAY PERI 06NFA NSA910A
80E111111111F106187100 0055556+0055556+ 0
80E222222222F106187200 0012600+0012600+ 0
80E333333333F106187200 0012000+0012000+ 0
80U111111111 06187100P P 06
80U222222222 06187200P P 06
80U333333333 06187200P P 06

 

[Surjer]

I would have to refer you to the FileSystemObject.

This gives you access to files for read/write.

FileSystemObject Object


Description

Provides access to a computer's file system.

Syntax

Scripting.FileSystemObject

Remarks

The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)
a.WriteLine("This is a test.")
a.Close

In the code shown above, the CreateObject function returns the FileSystemObject (fs). The CreateTextFile method then creates the file as a TextStream object (a), and the WriteLine method writes a line of text to the created text file. The Close method flushes the buffer and closes the file.

 

[maryenc]

That's similar to the same code I tried to copy/paste/edit today to make my own files work -- without success.

Sub OpenTextFileTest()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f, fa
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("H:\Test\ACCESS\WS\Transactions\Pmts.txt", 3, 0)
Set fa = fs.OpenTextFile("H:\Test\ACCESS\WS\Transactions\Update.txt", 1, 0)
f.Write fa
f.Close
End Sub

It stops on the "Set f..." line. As I say, I really have no clue what I'm doing here.

 

[Surjer]

OK Check this out

I created a function for ya

You call this function like this

Private Sub Command1_Click()
AppendFiles "c:\MyFile1.txt", "c:\TheNewTextFile.txt"
End Sub

Here is the function

Public Function AppendFiles(ByVal OldFileName As String, ByVal NewFileName As String)

    Dim fso As FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim OldFile As File
    Dim oldTS As TextStream
    Dim newTS As TextStream
    
    Dim aline As String
    
    Set OldFile = fso.GetFile(OldFileName)
    Set newTS = fso.CreateTextFile(NewFileName, True)
    Set oldTS = OldFile.OpenAsTextStream(ForReading)


    Do Until oldTS.AtEndOfStream
        aline = oldTS.ReadLine
        newTS.WriteLine aline
    Loop
    
    MsgBox "DONE"
    newTS.Close
    oldTS.Close
    
End Function

Keep in mind you will have to add a reference to The scripting object.

To do that follow these steps.

Open the code view
Go To "Tools" and Then "References"
Check the box next to "Microsoft Scripting Runtime"
Click OK

 

[maryenc]

Wow, thanks!

I've added the reference as you indicate. I'll check into trying to get the rest of it to run for me in the morning. Pretty much looks like a foreign language to me, though, so I may have some additional questions for you.