VB code to export table to txt file

[Guest]

I'm using this code to export a table to a txt file, but the values of the
table become enclosed in quotation marks, which I don't want to happen:

DoCmd.TransferText acExportDelim, , "TableA", strSaveFileName, False

Does anybody here have a better code to export a table to a txt file (where
the values do not get enclosed in quotes, but are delimited by commas).

 

[Steve Schapel]

Anakin,

Go through the export process manually, via the File|Export menu. When
you get to the last screen of the Export Wizard, click the Advanced
button, and save your setup as an Export Specification. Then, put the
name of this Specification in your code, in the SpecificationName
argument of the TransferText method.

 

[Guest]

I found the answer here:
http://www.tech-archive.net/Archive...lic.access.externaldata/2005-05/msg00585.html


Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

 

[Steve Schapel]

Anakin,

Fair enough. But I would say the suggestion I gave before would be a
lot simpler.

 

[(PeteCresswell)]

Per Steve Schapel:

Then, put the
name of this Specification in your code, in the SpecificationName
argument of the TransferText method.

But be sure to document the heck out of the resulting specification bc they're
pretty much hidden once you've created one. Where it will come back and bite
you is if/when the app becomes corrupted and you build a new version by
exporting all objects. You'll tend to forget about the specs.

 

[Steve Schapel]

exporting all objects. You'll tend to forget about the specs.

In my experience, you only forget this once