Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 9 votes, 5.00 average. Display Modes
Old 05-20-2012, 01:37 AM   #1
Monoceros
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 0
Thanked 2 Times in 1 Post
Monoceros is on a distinguished road
Access 2010 table export to csv

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 is offline   Reply With Quote
Old 05-20-2012, 05:14 AM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,655
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Access 2010 table export to csv

TransferText Method
Galaxiom is offline   Reply With Quote
Old 05-20-2012, 05:47 AM   #3
Monoceros
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 0
Thanked 2 Times in 1 Post
Monoceros is on a distinguished road
Re: Access 2010 table export to csv

Quote:
Originally Posted by GalaxiomAtHome View Post
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" ?

Monoceros is offline   Reply With Quote
Old 05-20-2012, 11:49 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Access 2010 table export to csv

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?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
alforddh (03-30-2013)
Old 05-20-2012, 02:16 PM   #5
Monoceros
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 0
Thanked 2 Times in 1 Post
Monoceros is on a distinguished road
Re: Access 2010 table export to csv

Quote:
Originally Posted by Pat Hartman View Post
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.
Monoceros is offline   Reply With Quote
Old 05-21-2012, 10:13 AM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Access 2010 table export to csv

Did you notice that your transfertext expression is incorrect? Did you try the one I provided?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-21-2012, 11:28 AM   #7
Monoceros
Newly Registered User
 
Join Date: Oct 2006
Posts: 23
Thanks: 0
Thanked 2 Times in 1 Post
Monoceros is on a distinguished road
Re: Access 2010 table export to csv

Quote:
Originally Posted by Pat Hartman View Post
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

Monoceros is offline   Reply With Quote
The Following 2 Users Say Thank You to Monoceros For This Useful Post:
alforddh (03-30-2013), Reflex_ht (05-10-2013)
Old 05-22-2012, 11:17 AM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Access 2010 table export to csv

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
alforddh (03-30-2013)
Old 03-30-2013, 11:03 AM   #9
alforddh
Newly Registered User
 
Join Date: Mar 2013
Posts: 1
Thanks: 3
Thanked 0 Times in 0 Posts
alforddh is on a distinguished road
Re: Access 2010 table export to csv

Great instructions Thank you
alforddh is offline   Reply With Quote
Old 05-10-2013, 01:22 AM   #10
Reflex_ht
Newly Registered User
 
Reflex_ht's Avatar
 
Join Date: Sep 2012
Posts: 64
Thanks: 27
Thanked 3 Times in 3 Posts
Reflex_ht is on a distinguished road
Send a message via Skype™ to Reflex_ht
Re: Access 2010 table export to csv

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
__________________
Huber Tarik
ICS Logistik & Transport GmbH

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Reflex_ht is offline   Reply With Quote
Old 05-10-2013, 12:23 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Access 2010 table export to csv

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-10-2013, 05:09 PM   #12
Reflex_ht
Newly Registered User
 
Reflex_ht's Avatar
 
Join Date: Sep 2012
Posts: 64
Thanks: 27
Thanked 3 Times in 3 Posts
Reflex_ht is on a distinguished road
Send a message via Skype™ to Reflex_ht
Re: Access 2010 table export to csv

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
__________________
Huber Tarik
ICS Logistik & Transport GmbH

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Reflex_ht is offline   Reply With Quote
Old 09-03-2013, 03:45 AM   #13
elliotgr
Newly Registered User
 
Join Date: Dec 2010
Posts: 67
Thanks: 4
Thanked 2 Times in 2 Posts
elliotgr is on a distinguished road
Re: Access 2010 table export to csv

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)

elliotgr is offline   Reply With Quote
Reply

Tags
csv , export

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export to Excel from Pivot Table in Access 2010 nicon2k Forms 0 04-10-2012 05:16 AM
Export to Excel from Access 2010 - Control Messages in Excel Jim Dudley Modules & VBA 5 03-29-2012 07:48 AM
Export Report From MS Access 2010 to Word 2010 amir_ayub Reports 0 02-28-2012 11:26 AM
Export Access 2010 Report to Excel 2010 Starchildren3317 General 1 02-01-2012 10:14 AM
How do I use excel 2010 vba to update Access 2010 table jscriptor09 Macros 2 04-13-2011 11:25 AM




All times are GMT -8. The time now is 03:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World