Solved Export Table to txt-File with semicolon spezification in VBA (1 Viewer)

[fkcm95]

Hi,
i would like export one table in access to txt-File but with the semicolon spezification. How can I do this?

Currently I do the following code:

DoCmd.OutputTo acOutputTable, "table1", acFormatTXT, "table1.txt"

But the output shows similar as a table with horizontale and vertical lines. But i needs semicolons.

 

[CJ_London]

suggest use the transfertext method instead of outputto. See this link

DoCmd.TransferText method (Access)

Office VBA reference topic

docs.microsoft.com

pretty sure that will default to comma field separators rather than semi colon, but might depend on your language settings. If it does you need to create a specification - easiest way is to use the External Data>export text file option which enables you to create the specification. Before you finish, click on the advanced option and select Save As. Use the name you save it as for the specification name

 

[fkcm95]

pretty sure that will default to comma field separators rather than semi colon, but might depend on your language settings. If it does you need to create a specification - easiest way is to use the External Data>export text file option which enables you to create the specification. Before you finish, click on the advanced option and select Save As. Use the name you save it as for the specification name

Thanks for the fast answer. I test this and create a Export Specification and save this with the name TableExport. If I use this in VBA, I became the Error, that TableExport not found. What I do wrong?

 

[June7]

Interesting, Export Wizard also uses lines.

TransferText uses comma delimiter, at least it does for U.S. database.

Options I see:

1. use TransferText then open file in NotePad and replace commas with semicolon

2. use VBA to write lines to text file

Post your exact code for analysis.

 

[fkcm95]

My whole Code is the line on the top. I have a Form in access with Buttons. The Button have the Click-Callback:

Private Sub Btn1_Click()
    DoCmd.TransferText acExportDelim, "TableExport", "Table", "table.txt", True 
End Sub

If I run this code, i became the error that TableExport not found.

If I run without the specififcation, than I became the Error 3441.

 

[CJ_London]

just to confirm - you did save the export specification? If you go to the export wizard again and go to advanced, you should be able to select it there - and can check the spelling

 

[fkcm95]

I saw the Export in this Wizard. (Saved Exports)

 

[CJ_London]

OK - it works for me, you may need to upload your db. Just double check you don't have leading or trailing spaces in the name. Unfortunately I'm about to do client training which will be for most of the day so probably won't be able to look at it until tonight. Perhaps June can take a look

 

[fkcm95]

I have solve this. Thanks all

 

[CJ_London]

one other thought - 'Table' is not a good name for a table. Suggest try renaming it.

Also, suggest research using a schema.ini file instead of a specification - google something like 'vba schema.ini'. Found this link which may help

Create Schema.ini

Hi there, I have a new question. Is there any way to dynamically create a Schema.ini file? Problem is that I have a function that requires a Schema.ini file, and this file always has to be in the same directory where the current database is. But the user can accidentally delete this file, so...

www.access-programmers.co.uk

I have solve this.

what was the solution? (helpful to others who may find this thread)

 

[ebs17]

A saved export is very different from an export specification that CJ_London spoke of.

Saved export is saved in XML as an object in the database. Use:

DoCmd.RunSavedImportExport "SavedImportExportName"

An export specification is created as described by CJ_London. You may not save the export as such, only the selected settings for column separators, decimal separators, etc.
The export specification is stored in two system tables => simple records.

Eberhard