export a simple text file

[G.]

I'm so frustrated. Why can't I build a macro that can export a text file? I
tried the output to action and got a text file with dashed lines around my
data. I searched this site and found I could use the TransferText action but
this gives me quotes around my data! I've searched the site (and looked at
other resources) but can't find anything that will help me to export just a
simple text file with nothing else but my data - so frustrating. Can anyone
help with what I am doing wrong please? Is it possibly that the data type in
my table is text and not number? When I switch it to number it says that it
is going to delete some of the data in my table. Thank you for helping.

 

[Steve Schapel]

G,

Run through the process manually first, via the File=>Export menu. That way
you can control delimiters and text qualifiers. When you get to the final
screen of the export wizard, click 'Advanced' and then save as a
Specfication. Then, in your TransferText macro, enter the name of that
specification.

 

[DStegon via AccessMonster.com]

you can use a standard vb function .getstring as follows with
filesystemobject and textstream

here is code that will give you an output of the table name, the fields and
then a string of the data. you can make the column and row delimiter anyting
you want (within reason) so that you can later read the data and write a
routine to parse. You could write each table to its own text file, but below
I put all the tables and their fields and their data in one text file.

Public Sub TxtALLData()
Dim rst As New ADODB.Recordset
Dim tbl As TableDef
Dim fld As Field
Dim str As Variant
Dim fso As New FileSystemObject
Dim TXT As TextStream
Set TXT = fspenTextFile("c:\AllDataTest.txt", ForWriting, True,
TristateUseDefault)
For Each tbl In CurrentDb.TableDefs
TXT.WriteLine "Table is " & tbl.NAME

If Left(tbl.NAME, 4) <> "MSys" Then
With rst
.Open tbl.NAME, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
str = ""
For Each fld In rst.Fields
str = str & fld.NAME & " "
Next
TXT.WriteLine str
TXT.WriteLine
str = ""
If .RecordCount > 0 Then
str = str & .GetString(adClipString, .RecordCount, " ",
"||") 'Column Delim, Row Delim
End If
.Close

End With
TXT.WriteLine CStr(str)
End If
TXT.WriteLine
TXT.WriteLine
Next tbl
TXT.Close

MsgBox "DONE!!", vbOKOnly

End Sub