pls urgent....using delimiter (|)

[erin_0502]

hello.....i would like to export a Ms Access table to delimiter Text file using this "|" delimiter...can anobody help me....



this is my coding for this problem, which part that i have to add...

Option Compare Database
Option Explicit

'Public Sub Main()
'ExportDatabaseObjects()
'End Sub

'Public Sub ExportDatabaseObjects()
Public Sub Main()
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
'Dim db As DAO.Database
Dim td As TableDef
'Dim d As Document
'Dim c As Container
Dim i As Integer
Dim sExportLocation As String

Set db = CurrentDb()

sExportLocation = "N:\- Guest -\guestbcs\usrPrivate\irni\text file\"
For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & td.Name & ".txt", True
DoCmd.RunMacro "table_name"
End If
Next td

For i = 0 To db.QueryDefs.Count - 1
Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
Next i

Set db = Nothing


MsgBox "All table been exported as a text file to " & sExportLocation, vbInformation

Exit_ExportDatabaseObjects:
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ExportDatabaseObjects
End Sub


my supervisor want the input come out like this using the "|" delimiter...

745395|745393|86961||||0.07|3.14||311||||||ST|||m2|||
746427|746425|86961||||0.07|0.06||311||||||ST|||m|||
749978|749976|86961||||0.04|0.04||311||||||ST|||m|||
751819|751817|86961||||0.07|0.07||311||||||ST|||m|||
757316|757337|86961||||0.05|2.56||311|0|6.43|0.01|3||ST|||m2|||
757372|757337|86961||||0.05|2.79||694|0|6.50|-1.52|3||ST|||m2|||
757626|757624|86961||||0.03|2.75||311||||||ST|||m2|||
759779|759777|86961||||0.07|0.07||311||||||ST|||m|||
760870|760868|86961||||0.03|1.39||311||||||ST|||m2|||
760992|760990|86961||||0.03|2.75||311||||||ST|||m2|||
761161|761159|86961||||0.03|1.39||311||||||ST|||m2|||


But now my report come out like this using "," delimiter...

745395,745393,86961,,,,0.07,3.14,,311,,,,,,"ST",,,"m2",,,
746427,746425,86961,,,,0.07,0.06,,311,,,,,,"ST",,,"m",,,
749978,749976,86961,,,,0.04,0.04,,311,,,,,,"ST",,,"m",,,
751819,751817,86961,,,,0.07,0.07,,311,,,,,,"ST",,,"m",,,
757316,757337,86961,,,,0.05,2.56,,311,0,6.43,0.01,3,,"ST",,,"m2",,,
757372,757337,86961,,,,0.05,2.79,,694,0,6.50,-1.52,3,,"ST",,,"m2",,,
757626,757624,86961,,,,0.03,2.75,,311,,,,,,"ST",,,"m2",,,
759779,759777,86961,,,,0.07,0.07,,311,,,,,,"ST",,,"m",,,
760870,760868,86961,,,,0.03,1.39,,311,,,,,,"ST",,,"m2",,,
760992,760990,86961,,,,0.03,2.75,,311,,,,,,"ST",,,"m2",,,

can u help me....

 

[ksj]

Erin, you need to create an export specification, otherwise it will use the default one (which uses a comma as the delimiter.)

The argument would go where I put the words "RIGHT HERE" (in quotes, or set a string variable to it.)

DoCmd.TransferText acExportDelim, "RIGHT HERE", td.Name, sExportLocation & td.Name & ".txt", True

To create an export specification, actually walk through the export process and in the second (or third?) step of an export to text wizard there will be a button labelled "Advanced". You can set settings here, and save the specification for use in your code. Hope that's enough detail to get you where you need to go.

 

[erin_0502]

thank for your response....

before this i try to do same like what u want but i get the error message when i put like this:

DoCmd.TransferText acExportDelim, "|", td.Name, sExportLocation & td.Name & ".txt", True


plz help me...

 

[ksj]

export specification

I'm sorry if I wasn't clear. You actually have to create an entire export specification and put it in that spot in the code.

This article will show you how to do that in Office 2007:
http://office.microsoft.com/en-us/access/HA012263071033.aspx?pid=CH100645741033

And this article will show you how to do that before Office 2007 (and it shows the steps to export in VBA, too:

http://www.blueclaw-db.com/export-specifications.htm