Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Export Table to Text via VBA

Posted on 2003-02-27
Medium Priority
Last Modified: 2008-03-31
The goal of my project is to read in a dbf file, and after some processing, output it as a text file with either comma-delimiting, tab-delimiting, or fixed-width. Given the dbf files can have various field widths and number of fields. What is the best way using VBA to programmatically export the table? I have looked at the TransferText method, but it requires a schema file and I cannot find (through Help or Google) any details on how to build such a file.

Thank you for your help and be kind as this is my first question.
Question by:Moliere

Expert Comment

ID: 8035404
You can create a schema file by using the File --> Get External Data --> Import menu options.  Use the wizard to import a template file and during that process you can create a schema.  On the first page of the wizard choose the Advanced button on the lower left and you'll get an interface to create a schema... on the right of this screen you do a save as...

Another way is to write directly to a file while looping through a recordset - this will do the same and you don't have to worry about schemas.

Expert Comment

ID: 8035499
For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

DoCmd.TransferText acExportDelim, , _
    "ExternalReport", "C:\temp\ExternalReport.txt"

Expert Comment

ID: 8035532
just a note - you don't really need to import a file to create it - you just need to get access to start the import wizard so you can create the file - so pick any text file (remember to change the Files of Type dropdown from .mdb to All Files) and this will get you started.

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

ID: 8035565
To use code to create the file:

Public Function CreateTextFile()

 Dim myFile As String
 Dim strFileName As String

 Dim intFile As Integer

 strFileName = "yourfilename"

 myFile = "C:/YourDirectory")
 myFile = myFile & "/" & strFileName & ".txt"
 intFile = FreeFile
 Open myFile For Output As intFile
 Print #intFile, "Text to write to file, line 1."
 Print #intFile, "Text to write to file, line 2."
 Close intFile
End Function

Accepted Solution

kraig earned 300 total points
ID: 8035619
To write the table to the file:

 Dim rs As Recordset
 Dim fld As Field

 Dim strLine as String
 Set rs = DBEngine(0)(0).OpenRecordset("YourTable", dbOpenForwardOnly)
 Do While Not rs.EOF
    For Each fld In rs.Fields
        strLine = strLine & rs(fld.Name) & ", "

    strLine = Left(strLine, Len(strLine) - 2)
    Print #intFile, strLine
    strLine = ""

 Set rs = Nothing

Author Comment

ID: 8036937
This code worked except that I had to declare rs and fld as variants. Also, I like the way I can designate the split character as a variable instead of ", ".

Thanks for your help.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question