Export Table to Text File and Include Column Name

[KevinTekTip]

Hello, thanks for helping me with this very annoying problem. I've searched message boards and google for over an hour and have not found a solution for this problem.

Using either a Macro or Visual Basic I need to export a table to tab delimited text file having the first row of the text file be the column names. I have everything working EXCEPT having the column names exported.

Here is my current VB Code:

DoCmd.TransferText acExportDelim, "kjmSpecs", "product_table_import", "c:\mytable.txt", True, ""

The "kjmSpecs" is my specification file that tells Access to export it as Tab Delimited rather than CSV.

The "True" is supposed to mean to also export the column names.

For some reason the column names are never exported!

As a test I wrote similar code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "product_table_import", "c:\spreadsheet.xls", True, "A1: ZZ99999"

and that DID export the column names.

What is the problem? Does anybody have any idea why the column names will not export into the text file?

Thanks for your help!

 

[Darrylles]

Hi,

Did you try
[tt]
DoCmd.TransferText acExportDelim, "kjmSpecs", "product_table_import", "c:\mytable.txt", True
[/tt]
without the last argument of "" ?


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[KevinTekTip]

Thanks for the idea Darrylle but no that doesnt help. I even tried replacing the "true" with a "0" and that doesn't work either.

This Microsoft website expalins how to properly use the transfertext function even though it doesnt work for me:

http://msdn.microsoft.com/library/d...ac11/html/acmthactTransferText_HV05186521.asp

From the above link, this is the basic format of the code:

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

HasFieldNames Optional Variant. Use True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.

This is driving me crazy! My data isn't goofy nor are amy of my column names. Any other suggestions would be greatly appreciated. Thanks a lot.

Kevin

 

[PHV]

Seems that the kjmSpecs specifications says to NOT include field names ...

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

 

[Darrylles]

Yep,

That WAS of course my next question: what have you defined in the spec?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[KevinTekTip]

Thanks for the ideas guys, but I don't think that the specs file says anthing about whether or not to include field names.

With the help of someone else, I have come up with workaround code. However, i'd still like to get to the bottom of my orginal problem if anybody has more suggestions.

Private Sub cmdExportDB_Click()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String

Set db = CurrentDb

blnColumnHeaders = True
strDelimeter = Chr(9)

Set rst = db.OpenRecordset("select * from product_table_exportQ")

Open "c:\exportfile.txt" For Output As #1

If blnColumnHeaders = True Then
For Each fld In rst.Fields
strText = strText & fld.Name & strDelimeter
Next fld

'get rid of the last delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))

strText = strText & vbNewLine
End If



Do While Not rst.EOF
For Each fld In rst.Fields
strText = strText & fld.Value & strDelimeter
Next

'get rid of the extra delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))

strText = strText & vbNewLine

rst.MoveNext
Loop
'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1

End Sub

 

[PHV]

Have you tried to replace True with [!]-1[/!] ?

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

 

[KevinTekTip]

Yes i've tried to replace True with -1.

In an earlier post when I said "I even tried replacing the 'True' with a '0' and that doesn't work either."

I meant to say -1.

Thanks again for your suggestions.