Access 2010 table export to csv (1 Viewer)

Monoceros

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2006
Messages
23
I need to export a table to a csv file with VBA code.

How do I do that ?

The name of the table is "Book".

I have Access 2010.
 

Monoceros

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2006
Messages
23
Unfortunately it doesn't seem to work :

DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

There is no file created in the location that I specified. What do I have to specify for "Standard Output" ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:53
Joined
Feb 19, 2002
Messages
29,465
DoCmd.TransferText acExportDelim, , "Book", "C:\txtfiles\April.csv", True
1. You asked to export a .csv file NOT a .doc file.
2. Did you look at the reference Galzxiom provided?
 

Monoceros

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2006
Messages
23
DoCmd.TransferText acExportDelim, , "Book", "C:\txtfiles\April.csv", True
1. You asked to export a .csv file NOT a .doc file.
2. Did you look at the reference Galzxiom provided?
Yes, I looked at the reference and also used the .csv extension.

But no file is transferred.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:53
Joined
Feb 19, 2002
Messages
29,465
Did you notice that your transfertext expression is incorrect? Did you try the one I provided?
 

Monoceros

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2006
Messages
23
Did you notice that your transfertext expression is incorrect? Did you try the one I provided?
Yes, I simply copied it and changed the table name. No file was transfered.

In the meanwhile I have found the following code and now it works :)


Dim trz As Integer
Dim strCSV As String

For trz = 1 To 511
Close #trz
Next trz
trz = FreeFile
Open "C:\Users\MyName\Documents\Book.csv" For Output Access Write As #trz

With CurrentDb.OpenRecordset("Book ")
Dim x As Integer
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & .Fields(x).Name & "; "
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)

Do Until .EOF
strCSV = ""
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>") & "; "
Next x
Print #trz, Mid(strCSV, Len(strColumnDelimiter) + 1)
.MoveNext
Loop
End With
Close #trz
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:53
Joined
Feb 19, 2002
Messages
29,465
Yes, I simply copied it and changed the table name. No file was transfered.
You said the table name was "Book". Why did you change it? I see you also changed the path. Did the path you were trying to write to with the TransferText method exist?

I guess two dozen lines of code trumps one. Glad you got it working. The TransferText works great assuming your syntax is correct.
 

Reflex_ht

Registered User.
Local time
Today, 13:53
Joined
Sep 11, 2012
Messages
64
Hello,

I'm trieng to export a Table to a CSV File. With a VBA like this:

Code:
DoCmd.TransferText acExportDelim, , "tbl_Tradeshift", "C:\tbl_Tradeshift_CSV" & ".csv", True
If I use for Specification "Standard output" I become this Error Message: "Runtime Error 3625" - The Specification "Standard Output" dont exist...

If I leav it blank I´ll bekome the Error Mesaage: "Runtime Error 3441 field seperator mathces decimal seperator...."

Am I doing something wrong? Can someon help me? THX
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:53
Joined
Feb 19, 2002
Messages
29,465
If your export will need an export spec, you need to create the export spec by manually exporting the file once through the export wizard. On the last screen of the export, press the "Advanced" button, change any parameters that need changing and choose the save option. Once you have named and saved the export spec, you can reference it in VBA or macros using the TransferText method.
 

Reflex_ht

Registered User.
Local time
Today, 13:53
Joined
Sep 11, 2012
Messages
64
Thanks but it didn´t work with that :( even if I manualy export the table and save a export specification. I used a modified version of the Monoceros code and it worked greate :D
 

elliotgr

Registered User.
Local time
Today, 23:53
Joined
Dec 30, 2010
Messages
67
Is there a bug in Access? It seems many people are complaining about this.
The transfer text does not work as I get error 3441. Even if I create a transfer spec, the message then becomes Transfer Spec not found.
The data is pure text with no funny characters except ().
MyPathFileName is on another PC.

Code:
DoCmd.TransferText acExportDelim, , "My_Query", "MyPathFileName"
Using Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6662.5000)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom