Need help exporting as fixed width

[mayonace]

Hi,

The following code is working as I like, only problem is, I cannot seem to have it export to text as fixed width. Any help would be greatly appreciated.

Option Compare Database
Option Explicit

Sub ExportTextFile2()

Dim rst As ADODB.Recordset ' Used to open record1
Dim rst2 As ADODB.Recordset ' Used to open record2
Dim rst3 As ADODB.Recordset ' Used to open record5
Dim rst4 As ADODB.Recordset ' Used to open record9
Dim cnn As ADODB.Connection

Dim lngFile As Long

lngFile = FreeFile ' Find next available file

Open "\test.txt" For Output As #lngFile ' Open file to export data to

Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
Set rst3 = New ADODB.Recordset
Set rst4 = New ADODB.Recordset
Set cnn = CurrentProject.Connection

With rst
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "Record1"
.Open Options:=adCmdTableDirect
Do Until .EOF
' Export the header
Print #lngFile, !Recordtype & !PorH & !ProvClaimNumber & !Auth & !ProvID & !ProvID & !MemberID & !POS

With rst2
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record2 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !PorH & Format(!DateFrom, "yyyyMMdd") & Format(!DateTo, "yyyyMMdd") & !ServiceCodeP

With rst3
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record5 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !DXRef & !DXCode

With rst4
.ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Record9 WHERE claimno='" & rst!claimno & "'"
.Open Options:=adCmdText
Do Until .EOF
'Export detail
Print #lngFile, !Recordtype & !Filler & !ProviderName & !Filler2 & !Clearinghousename & _
!Filler3

.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With
.MoveNext
Loop
.Close
End With

Close #lngFile
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst4 = Nothing
Set cnn = Nothing

End Sub

TIA!!!
mayonace

 

[PHV]

export to text as fixed width
You have to format each field to the predefined length.
Say you want ProviderName occupying 30 bytes in the text file, you may consider something like this:
strProviderName = Left(!ProviderName & Space(30), 30)
For the numeric fields, simply use the Format function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

BTW, you may also take a look at Tab in the help for the Print # instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mayonace]

THANKS PHV!!! Got lotsa typing to do, but this solves my problem perfectly. Have a nice weekend!