Removing " from exported queries

  • Thread starter Dan Pinheiro \(Hotmail\)
  • Start date
D

Dan Pinheiro \(Hotmail\)

Hi All.
When I export a file, through "TransferText" method, the Access includes
" before and after each line (see example bellow). The same occurs if I
export using the menu option.
Is there any way to export without ""?
Tks a lot
[] ´s

Dan Pinheiro
Brasil

=====Example=======
* Command
DoCmd.TransferText acExportDelim, "", "queryname", "c:\filename.txt", False

* Result
"1preco04080500054000165"
"20347200000000000096073270509"
"20347380000000000110484270509"
"20347460000000000096073270509"
"20347540000000000127297270509"
"20347620000000000127297270509"
"20347700000000000096073270509"
 
K

Krizhek

you can create a macro to do the same.

to do this open a new macro and select the 'OutputTo' Action. then select
the apporpriate agruments.
 
D

david epsom dot com dot au

You can use a named export specification to change or
remove the delimiter characters.

(david)
 
D

Dan Pinheiro \(Hotmail\)

How to create a named export specification?
Tks

Dan Pinheiro


david epsom dot com dot au said:
You can use a named export specification to change or
remove the delimiter characters.

(david)

Dan Pinheiro (Hotmail) said:
Hi All.
When I export a file, through "TransferText" method, the Access
includes " before and after each line (see example bellow). The same
occurs if I export using the menu option.
Is there any way to export without ""?
Tks a lot
[] ´s

Dan Pinheiro
Brasil

=====Example=======
* Command
DoCmd.TransferText acExportDelim, "", "queryname", "c:\filename.txt",
False

* Result
"1preco04080500054000165"
"20347200000000000096073270509"
"20347380000000000110484270509"
"20347460000000000096073270509"
"20347540000000000127297270509"
"20347620000000000127297270509"
"20347700000000000096073270509"
Click to expand...
Click to expand...
 
K

Ken Sheridan

As an alternative you can write the data directly to a file using the
following function. Just paste it into a standard module and call it,
passing the name of the query and the path to the text file to be created as
its arguments. It uses DAO so make sure you have a reference to the DAO
object library (Tools|References on the VBA menu bar)

Function ExportToTabbed(strQuery As String, strExportTo As String)

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef, fld As DAO.Field
Dim n As Integer
Dim strPrintList As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strQuery)
Set qdf = dbs.QueryDefs(strQuery)

If Dir(strExportTo) <> "" Then
If MsgBox("Overwrite " & strExportTo & "?", vbQuestion + vbYesNo,
"Export Query") = vbYes Then
Kill strExportTo
Else
GoTo Exit_Here
End If
End If

With rst
If Not (.BOF And .EOF) Then
Open strExportTo For Output As #1
Do While Not .EOF
For n = 0 To qdf.Fields.Count - 1
strPrintList = strPrintList & vbTab & rst.Fields(n)
Next n
' remove leading tab
strPrintList = Mid$(strPrintList, 2)
Print #1, strPrintList
strPrintList = ""
.MoveNext
Loop
Close #1
End If
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set dbs = Nothing

End Function
 
Top