exporting query to text comma separated

[Pasi]

HI,

New to this thread, like to know how do I make a macro or automate the exporting to export my query to .txt ( comma separate) with a macro or any other way? when I export this file the file is pipeline delimited not text comma separated. I am using the macro builder in access 2010. I have attached the screen shot. Appreciate any help.


Thanks.

[June7]

The only ways I know use VBA. One of them:

DoCmd.TransferText acExportDelim, , "tablename", "Drive\folderpath\filename.txt"

Unfortunately, it also includes quote marks around text data.

[Pasi]

June 7 ,

Cool!! that did it!!! OMG .. thnx so much!!! one more thing how do I call this vba code to my macro?
Thanks!

[June7]

I don't use macros but I think they can call VBA code only if it is a custom function in a general module. Normally, I would put the VBA code behind a form, possibly the click event procedure of a button.

Look at the RunCode macro action.

[Pasi]

thanks but I am not sure how to do this? I looked into runcode from the drop down list in macro ( add new action) and all is asking for function name? how do I tell it to use the vba code? not too savy with this?
Pasi.


NO worries! I manage to make a function and call that function! it is working now! thanks so much for your help!! You Rock!!

[Pasi]

June 7

Can you tell me how to add the date to the File extension? like Myfile-12/27/13.txt?
Thanks!!

[June7]

Slashes will be an issue so try:

DoCmd.TransferText acExportDelim, , "tablename", "Drive\folderpath\filename_" & Format(Date(),"mmddyy") & ".txt"

or

Format(Date(),"mm_dd_yy")

or

Format(Date(),"mm.dd.yy")