Merge text files within ms access using vba

I have three fixed width text files I export from within access.  Once the three text files are exported I would like to combine them into one text file and add the date to the name of the combined file.  I would like to do this using VBA so that I can execute the function with the click of a button.
LVL 15
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this codes, add windows scripting runtime library to your references

Sub combineTextFiles()
Dim fPath As String, fName As Object, txtContent As String
Dim fArr() As Variant, j As Integer, newFile As Object
Dim fs As Object
fPath = CurrentProject.Path & "\"

 'place your file in an array in the manner that you like them to appear in the new file

fArr = Array("file1.txt", "file2.txt", "file3.txt")

Set fs = CreateObject("Scripting.filesystemobject")
Set newFile = fs.createtextfile(fPath & "myTextFile_" & Format(Now(), "yyyymmdd") & ".txt", True)

For j = LBound(fArr) To UBound(fArr)
    Set fName = fs.OpenTextFile(fPath & fArr(j), ForReading)
        If fName.AtEndOfStream Then
            txtContent = ""
            txtContent = fName.ReadAll
        End If
        newFile.Write txtContent & Chr(10) & Chr(13)
set fs=nothing
End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
why are you exporting three text file?
is it not possible to combine all  the three files prior to exporting as text file?
PriceDAuthor Commented:
The three files have different fixed width fields that need to be in a specific order.  

There is a header (file 1)  has 10 fields that have to be fixed width
Records (file 2)  has 18 fields that have to be fixed width, different fields, different widths then the header.
Footer (file 3) has 7 fields different widths etc

I need to combine these three text files into one so that I can submit the one text file to an insurance agency that has set these parameters for automatic claims submissions.

If there is a better way to combine this data from within access and keep the width criteria I welcome any suggestions.
You can do it with shell command or with script:
Sub tst1()
Dim osh As Object
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim strCopy As String
Set osh = VBA.CreateObject("WScript.Shell")
'Create your copy command
strCopy = "copy c:\somefolder\a.txt + c:\somefolder\b.txt + c:\somefolder\c.txt c:\somefolder\c" & Format(Date, "-YYYY-MM-DD") & ".txt"

osh.Run "cmd.exe /S /C " & strCopy, windowStyle, waitOnReturn
End Sub

Open in new window

with script you can wait until script is finished. It is helpful if files are large and you like to check result
PriceDAuthor Commented:
Thanks Rey!

Had a bit of trouble until I realised I hadn't added the "Microsoft Scripting Runtime"  as you had instructed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.