Macro that exports data from a query to an OUTPUT textfile without QUOTES

[maryanncanor]

Hi I would like to know if there is a macro that can export the data from a query to an output textfile that has NO QUOTES.


If I run the concatenated query Expr1: [SEQ_NUM] & [TYPE] & [DECLARANTS] & [FILENAME] & [CALENDAR_YR] & [O/R] & [DECLARATION_STATUS] & [NAME_BUSNAME_DEC]


and export it in Export Text wizard as text, with delimited, Other: | (pipeline)
and Text qualifier as none>next>finish

I can get the desired output without quotes. But this is a very long process for a user.

Can you suggest a macro that can output textfile from a query without quotes so I can just use a macro in a Command button.

I would like to export data from an existing concatenated query Expr1.



sql view of query:
SELECT [SEQ_NUM] & [TYPE] & [DECLARANTS] & [FILENAME] & [CALENDAR_YR] & [O/R] & [DECLARATION_STATUS] & [NAME_BUSNAME_DEC] AS Expr1
FROM [RT1_Declarant_ Information_1];



desired output in text file:

000000041DIRF2008R1SMITH,JULES
000000051DIRF2008O2WAYNE,BRUCE
000000061DIRF2008O2ANDREWS,RENEE
000000071DIRF2008O1ALLEN,MARK
000000081DIRF2008O1OWEN,HALLE
000000091DIRF2008O1MOONIE,IAN
000000101DIRF2008O1CASTILLO,MANUEL
000000111DIRF2006R1AMANTE,POPE
000000121DIRF2002R1SALTA,NEIL
000000131DIRF2003R1CANOR,SANDRA
000000141DIRF2004R1CANOR,FRANCIS
000000151DIRF2007R1MEJIA,MELINDA
000000161DIRF2008R1AQUINO,JANET
000000171DIRFR1CASTRO,ANDY


Thank you for your time.

[ADezii]

There is probably a shorter method, but this code should produce the exact results you are looking for, just make the necessary substitutions for strQueryName and strOutputFilePath.

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strQueryName As String
Dim strOutputFilePath As String
 
strQueryName = "qryEmployees"
strOutputFilePath = "C:\Testi\Outputfile.txt"
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strQueryName, dbOpenSnapshot)
 
Open strOutputFilePath For Output As #1
 
Do While Not MyRS.EOF
  Print #1, MyRS![Expr1]
  MyRS.MoveNext
Loop
 
Close #1
MyRS.Close
Set MyRS = Nothing

OUTPUT based on sample Northwind data:

DavolioNancySales RepresentativeSeattle
FullerAndrewVice President, SalesTacoma
LeverlingJanetSales RepresentativeKirkland
PeacockMargaretSales RepresentativeRedmond
BuchananStevenSales ManagerLondon
SuyamaMichaelSales RepresentativeLondon
KingRobertSales RepresentativeLondon
CallahanLauraInside Sales CoordinatorSeattle
DodsworthAnneSales RepresentativeLondon