Combine Text Files

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

I have two queries that exports data into two seperate text files. I need
to combine the two files into 1 text file and would like to do it
programmatically using VBA from within Access. Right now I am combining
them manually using Cut/Paste in notepad. Any ideas?

Thank you
 
Do you mean you want the query to combine the data so it can be output as 1
file??

Select * from Table1 UNION ALL Select * from Table2
 
I have written a VBA "program" in EXCEL that allows a user to select the
text files that are to be "added" together (all the files need to be in the
same folder) and then it writes a new text file that is the combination of
the two. It probably could be "converted" to ACCESS VBA if you replace the
use of EXCEL's "openfile" dialog with the API code calls. I'll leave that
exercise for the user:


Public Sub CombineTextFilesIntoOneTextFile()
' Written by Ken Snell -- May 21, 2003
' May be used if authorship attribution is included in the code

Dim strInfile As Variant
Dim intLoop As Integer
Dim strOutfile As String, strLine As String
Dim objFSO As Object, objFileO As Object, objFileI As Object, objGetFile As
Object

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


On Error GoTo Err_CombineFiles

' Select the text files to be combined
strInfile = Application.GetOpenFilename(, , "Select One Or More Files:", ,
True)

' Check to see if the above method returned an array variable (true if at
least one file
' was selected; false if none were selected)
If IsArray(strInfile) = True Then
' Select the filename and folder for the new text file
strOutfile = Application.GetSaveAsFilename("COMBINED_" &
Mid(strInfile(1), _
InStrRev(strInfile(1), "\") + 1), , , "Choose The File Into Which
The Combined Files Are To Be Written:")
' Create the object variables and create the new text file
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CreateTextFile (strOutfile) 'Create a file
Set objGetFile = objFSO.GetFile(strOutfile)
Set objFileO = objGetFile.OpenAsTextStream(ForWriting,
TristateUseDefault)

' Read the contents of each selected file and write the contents into the
new file
For intLoop = LBound(strInfile) To UBound(strInfile)
Set objGetFile = objFSO.GetFile(strInfile(intLoop))
' Open a file for reading
Set objFileI = objGetFile.OpenAsTextStream(ForReading,
TristateUseDefault)
Do
strLine = objFileI.readline
objFileO.writeline strLine
Loop While objFileI.AtEndOfStream <> True
' Close the file that was read
objFileI.Close
Set objFileI = Nothing
Next intLoop
' Close the file that was created
objFileO.Close
Set objFileO = Nothing
Set objGetFile = Nothing
Set objFSO = Nothing
MsgBox "The macro is finished.", vbInformation, "File Combination Is
Complete"
Else
MsgBox "You didn't select a file. The macro is ending.", vbExclamation,
_
"Macro Is Ending"
End If

Exit_CombineFiles:
Exit Sub


Err_CombineFiles:
MsgBox "An error has occurred during the running of this macro. The macro
will close now.", _
vbCritical, "Error Has Occurred!"
Resume Exit_CombineFiles

End Sub
 
Back
Top