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

x
?
Solved

Export Table to Text via VBA

Posted on 2003-02-27
6
Medium Priority
?
1,134 Views
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.
0
Comment
Question by:Moliere
6 Comments
 
LVL 5

Expert Comment

by:funke
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.
0
 
LVL 2

Expert Comment

by:i014354
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"
0
 
LVL 5

Expert Comment

by:funke
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.

0
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.

 
LVL 2

Expert Comment

by:kraig
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
0
 
LVL 2

Accepted Solution

by:
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) & ", "
    Next

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

 Loop
 
 rs.Close
 Set rs = Nothing
0
 
LVL 3

Author Comment

by:Moliere
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.
0

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