Unanswered: Is there a way to export a access table to a text file with VBA?
I need to export the access table to a text file because another program need to read it. When it is exported, the file extension has to be change to .OUT extension. Also, have an delimiter between each column.
I have browse through google.com and access books but no luck with codes on what I am doing.
1. Click on a table
2. Go to the File menu then click on Export
3. Set "Save Type As" as Text Files. An Export Wizard will pop up.
4. Click on the Advance button.
5. Change the Field Delimiter to "|" and set Text Qualifer to NONE
6. Click on "Save As" and choose a name that you will remember to put into the code later on. I save it as "DelimiterChar"
7. Click cancel twice to get out of the wizard.
you can do something like this I guess:
DoCmd.TransferText acExportDelim, , "PLINK", "C:\Programs\Plink.txt", False
FileCopy "C:\Programs\Plink.txt", "C:\Programs\Plink.out"
for double quotation, they had to be there because what hapens if there is comma inthe string? double quotation is the only way for applications to figure out if it is delimiter or a comma in the string.
I think after exporting you can open the exported file with old school methods, replace all "," with "|" and remove all doube quotation.
ok I wrote a Sub that exports any table or query or sql statemnt into a text file without double quotation and "|" delimitier. copy it into a module then all you need to is call this Sub with a filename and tablename/quername/SQL statement. for example:
CustomReport "C:\test.out","SELECT * FROM Table1"
Public Sub CustomExport(Filename As String, ATable As String)
Dim Rst As DAO.Recordset
Dim AField As DAO.Field
Dim TempStr As String
FileNumber = FreeFile
Open Filename For Output As #FileNumber
Set Rst = CurrentDb.OpenRecordset(ATable, dbOpenForwardOnly)
Do While Not Rst.EOF
For Each AField In Rst.Fields
TempStr = TempStr & AField.Value & "|"
Print #FileNumber, Left(TempStr, Len(TempStr) - 1)
TempStr = ""
Set Rst= Nothing
it was good to know that I didn't forget dealing with files.
Last edited by ghozy; 10-15-04 at 08:45.
Reason: code edit
I am having a small problem,
I need to say that I dont know VB at all,
but I need to extract all the tables
I got this for VB help
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
' Print name of obj.
this may be for printing filenames,
is there any way I can use this obj.Name to get that in the resulting text file
DoCmd.TransferText acExportDelim, , obj.Name, "C:Temp\obj.Name.txt", True
I am not able to use obj.Name.txt in the extracted file,