Export of multiple tables to single text file

[Jean9]

Am trying to export, in a predefined order, 5 tables with different file formats to a single text file using code. I have already created the fixed width export specifications for all tables. Need each row exported to be separated with a crlf.

Thanks for any help provided.

 

[JerryKlmns]

Since you export 5 different tables, each one using its export specifications, to text files, I would ... merge those text files after exporting the tables! If you need a sixth table in the future or different exporting spec for one of them, your fixes are to be minor!

So, use FileSystemObject to open all text file as streams, (using the .ReadAll method) and writte to a new file. You could automate this to loop the reading for all files in the specified folder of the exporting.

I cant remember though the good old DOS command line on how to merge files ....

 

[kcn]

I'm trying to do almost exactly the same thing and am confused about how to merge the files as suggested. Jean9, have you had any success with the code? Many thanks!

 

[JerryKlmns]

I did that yesterday with 59 txt files.
Just add a reference to Microsoft Scripting Runtime.

Sub UniteFiles(Input_FullPath As String, Optional Output_FullPathAndFileName As String = "C:\Total.txt")

Dim fso As Scripting.FileSystemObject
Dim fldr As Folder
Dim fls As Files
Dim fl As File
Dim ts As TextStream
Dim t_fl As File
Dim t_ts As TextStream

Set fso = CreateObject("Scripting.FileSystemObject")

fso.CreateTextFile Output_FullPathAndFileName, True
Set t_fl = fso.GetFile(Output_FullPathAndFileName)
Set t_ts = t_fl.OpenAsTextStream(2)

Set fldr = fso.GetFolder(Input_FullPath)
Set fls = fldr.Files
For Each fl In fls
    If fl.Type = "Text Document" Then
        Set ts = fl.OpenAsTextStream(1)
        t_ts.Write ts.ReadAll
        ts.Close
        Set ts = Nothing
    End If
Next
t_ts.Close
Set t_ts = Nothing
Set t_fl = Nothing
Set fls = Nothing
Set fldr = Nothing
Set fso = Nothing

End Sub

The good old dos way though is simple

C:\>Type d:\ThePath\*.txt > C:\Total.txt

 

[kcn]

Thank you for this example.

Unfortunately, I'm still confused as to how to relate the data between the different files - i.e. how to pull in the data so that the "Total.txt" file puts the data in the correct order. In my case this means a pattern for each set of related data:

Record 1 = Table 1 data
Record 2 = Table 2
Record 3 = Table 3
Record 4 = Table 4 (or more if multiple)
and
Record 5 = Table 1, next data set (etc.)

I was wondering if there's a way (and I'm a very elementary user of VB) to write a text file that creates a different format based on a distinct value in each table (as it happens each table has a field with a global value - i.e. so Table 1 has a field - Rank - that is always "1").

Would appreciate any more ideas. Thanks!

 

[kcn]

FYI - found a solution that bypassed VB. Thanks again for your help!