writing file

[frobo]

How I can write out a file which I have to setup in code?
I need some records save to a file in an own format.
Can I iterate through a resultset, write the text to a string (would be long) and save that string to a file? Or append/save to the file on every record?

tia
frobo

 

[JerryKlmns]

Let Avg1 be a table with fields Bank, Center, Main, BookBalance , Server_Location a full path to the file (AvgFirst.txt) which is to be created. Delimiter for importing it later is "|"

Sub ExportSomeData()
Dim fso As Object
Dim TheFile
Dim rstWrite As ADODB.Recordset

Set rstWrite = New ADODB.Recordset
With rstWrite
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = "Select Bank, Center, Main,BookBalance From Avg1;"
    .Open
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(Server_Location & "AvgFirst.txt") Then fso.deletefile (Server_Location & "AvgFirst.txt")
    Set TheFile = fso.CreateTextFile(Server_Location & "AvgFirst.txt", True)
    While Not .EOF
        my_Text = .Fields(0) & "|" & .Fields(1) & "|" & .Fields(2) & "|" & .Fields(3)
        TheFile.writeline (my_Text)
        .MoveNext
    Wend
    file.Close
    .Close
End With
Set rstWrite = Nothing
Set TheFile = Nothing
Set fso = Nothing

 

[frobo]

nice,thanks alot.
I'll try that.

later

 

[frobo]

Thanks it works very well!
Just for the other users here the corrected WORKING version.

----
Line #22 contains an error.
Change
file.Close
To
TheFile.Close
----


so here's the corrected full version:
<code>
Sub ExportSomeData()
Dim fso As Object
Dim TheFile
Dim rstWrite As ADODB.Recordset

Set rstWrite = New ADODB.Recordset
With rstWrite
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = "Select Bank, Center, Main,BookBalance From Avg1;"
.Open
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(Server_Location & "AvgFirst.txt") Then fso.deletefile (Server_Location & "AvgFirst.txt")
Set TheFile = fso.CreateTextFile(Server_Location & "AvgFirst.txt", True)
While Not .EOF
my_Text = .Fields(0) & "|" & .Fields(1) & "|" & .Fields(2) & "|" & .Fields(3)
TheFile.writeline (my_Text)
.MoveNext
Wend
TheFile.Close
.Close
End With
Set rstWrite = Nothing
Set TheFile = Nothing
Set fso = Nothing
</code>