writing table to .txt file

[soontobefour]

I am looking for some assistance, writing a table to a .txt file. I have tried to use the transfertext method, but the data must be formatted with // between fields, so the transfertext does not work.

Any assistance would be appreciated.


Thanks

 

[oharab]

Try this function.

It will export any table or query. If the query needs parameters it will ask for them.

Copy it all into a module and call it from anywhere like:
ExportTable "qryTotal",True ,"C:\txt.txt","//"

HTH

ben


Public Function ExportTable(sTableName As String, bShowFields As Boolean, sSavePath As String, sDelimeter As String)
'exports a table or query to a text file.
'requires: table name
' whether or not you want the field headings
' the path you want to save the file
' the delimeter

Dim f As Integer
Dim rs As DAO.Recordset, fld As DAO.Field, qd As DAO.QueryDef, prm As DAO.Parameter
Dim strTmp As String

Set qd = CurrentDb.CreateQueryDef("~tmpExportTable", "SELECT " & sTableName & ".* FROM " & sTableName & ";&quot
If qd.Parameters.Count > 0 Then
For Each prm In qd.Parameters
prm.Value = InputBox(prm.Name)
Next prm
End If

Set rs = qd.OpenRecordset
'open the table/query we are exporting
f = FreeFile
'get the next available file number
Open sSavePath For Output As f
'open a file
If bShowFields = True Then
'if we want the field headings
strTmp = ""
For Each fld In rs.Fields
strTmp = strTmp & fld.Name
If fld.OrdinalPosition <> rs.Fields.Count Then
strTmp = strTmp & sDelimeter
End If
Next fld
Print #f, strTmp
End If

Do Until rs.EOF
strTmp = &quot;&quot;
For Each fld In rs.Fields
strTmp = strTmp & rs(fld.Name)
If fld.OrdinalPosition <> rs.Fields.Count Then
strTmp = strTmp & sDelimeter
End If
Next fld
Print #f, strTmp
rs.MoveNext
Loop
Close #f
rs.Close
Set rs = Nothing

Exitsub:
DoCmd.DeleteObject acQuery, qd.Name
End Function


----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[soontobefour]

hey Ben,

I tried the code and it worked. I had to do some tweaking to better format my data, but I truely apprecaite your assistance.

two Thumbs Up