Macro for export to txt file

I need to automate the process of writing the contents of a Table to a comma delimited text file.  I can use the menu system of Access to do this, but I cannot determine how to do this via a macro or module?  I have to do this several times a week, so automating the
steps would be a time saver.  I am importing and exporting data between two programs.

dastaubAsked:
Who is Participating?
 
peterpuscasConnect With a Mentor Commented:
To export a table into a text file:
from macros:

New

in the first line put
Macro Name         Condition            Action
--------------------  ------------------  ----------------------
macro_export                                TransferText  


At the bottom:
Transfer Type              Export Delimited
Specification Name                                    
Table Name                 YourTableName
File Name                    C:\Folder\file.txt     -> full path to your text file
Has Fields Names         Yes
HTML Table Name                                    

Save the macro with the name macro_export

You may run this mcro directly or from a form:

Private Sub Command0_Click()
  DoCmd.RunMacro "macro_export"
End Sub

The same for import except
Transfer Type              Import Delimited

You could also use linked tables if you have access from one mdb to another.


0
 
bonjour-autCommented:
Please be aware, that commas in your field may cause trouble
peterpuscas method will work

alternatively you can do in VBA
a completely VBA based method could include checking the content of your fields, or you dissalow putting in commas by tablefield definitions.

the altenata VBA syntax is

DoCmd.OutputTo acTable[, "nameoftable",axText,"path&NameofTargetfile"

so place this to some command button:

Sub nameofcommandbutton_Click
DoCmd.OutputTo acTable[, "nameoftable",axText,"path&NameofTargetfile"
End Sub

Regards, Franz
0
 
MobileOakAICommented:
"I have to do this several times a week"

Part of answer is platform and tools/versions, and who runs what. I had simliar situation long ago.

I began with running access from command line. This enables two features. With the right OS (I even used NT server) the command can be placed in a separate command file. That command file then can be run easily at will or on a schedule. Such as daily.

The other thing it gives is the option to have an access process run by name. Inside access associate that name with a few commands. A small set such as open, run, close, exit. see above. Much is easily accessible under access help and pick lists etc.

Once successful with that, a further option comes to mind. Inside the dos command file can be placed a few others, for making a backup copy of the test files and a log to track other things such as for run time and outcome (success indicator).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.