USING PRINT#1 TO WRITE TO FILE PART(2) 1

[Jean9]

I am using the following code to write the contents of 4 tables to a single text file columns delimited by commas. The tables are H, V, W, and D. There is always only 1 record in the H table. So that will always be first BUT the rest of the tables all have multiple rows. Right now they are written to the file with all Vs grouped together, all W's grouped together and all Ds grouped together. V, W, and D all have the same number of rows and really should be written to the file in H, V, W, D, V, W, D, V, W, D etc. order. Is there a way to write a row at a time from the V, W, D tables?

Public Function ProcessExpFile()
Dim sFILENM As String

' Close the text file if it is open from previous code runs
Close #1

sFILENM = "C:\OUTPUT\AP019.txt"
' Open text file for writing
Open sFILENM For Output As #1
Set RS_ADO = New ADODB.Recordset
' Open the tables and write the records to the text file
RS_ADO.Open "H", CurrentProject.Connection
WriteFile
RS_ADO.Open "V", CurrentProject.Connection
WriteFile
RS_ADO.Open "W", CurrentProject.Connection
WriteFile
RS_ADO.Open "D", CurrentProject.Connection
WriteFile
' Close the text file
Close #1

End Function

Public Function WriteFile()

' Write table data to string format
vDATA = RS_ADO.GetString(adClipString, , Chr(44), vbCrLf)
' Debug.Print vDATA
' Write string to text file
Print #1, vDATA;
' Close recordset to prepare to open next recordset
RS_ADO.Close

End Function

Thanks for all help,
J9

 

[PHV]

Typed, untested:

Public Function ProcessExpFile()
Dim sFILENM As String
Dim vArr, wArr, dArr
Dim i As Integer
    ' Close the text file if it is open from previous code runs
    Close #1
    sFILENM = "C:\OUTPUT\AP019.txt"
    ' Open text file for writing
    Open sFILENM For Output As #1
    Set RS_ADO = New ADODB.Recordset
    ' Open the tables and write the records to the text file
    RS_ADO.Open "H", CurrentProject.Connection
    Print #1, RS_ADO.GetString(adClipString, , Chr(44), vbCrLf);
    RS_ADO.Close
    RS_ADO.Open "V", CurrentProject.Connection
    vArr = Split(RS_ADO.GetString(adClipString, , Chr(44), vbCrLf), vbCrLf)
    RS_ADO.Close
    RS_ADO.Open "W", CurrentProject.Connection
    wArr = Split(RS_ADO.GetString(adClipString, , Chr(44), vbCrLf), vbCrLf)
    RS_ADO.Open "D", CurrentProject.Connection
    RS_ADO.Close
    dArr = Split(RS_ADO.GetString(adClipString, , Chr(44), vbCrLf), vbCrLf)
    ' Write V,W,D,V,W,D,...
    For i = 0 To UBound(vArr) '- 1 if last records are empty
      Print #1, vArr(i) & vbCrLf & wArr(i) & vbCrLf & dArr(i)
    Next
    ' Close the text file
    Close #1
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886