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_STA TUS] & [NAME_BUSNAME_DE C]


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

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_STA TUS] & [NAME_BUSNAME_DE C] AS Expr1
FROM [RT1_Declarant_ Information_1];



desired output in text file:

000000041DIRF20 08R1SMITH,JULES
000000051DIRF20 08O2WAYNE,BRUCE
000000061DIRF20 08O2ANDREWS,REN EE
000000071DIRF20 08O1ALLEN,MARK
000000081DIRF20 08O1OWEN,HALLE
000000091DIRF20 08O1MOONIE,IAN
000000101DIRF20 08O1CASTILLO,MA NUEL
000000111DIRF20 06R1AMANTE,POPE
000000121DIRF20 02R1SALTA,NEIL
000000131DIRF20 03R1CANOR,SANDR A
000000141DIRF20 04R1CANOR,FRANC IS
000000151DIRF20 07R1MEJIA,MELIN DA
000000161DIRF20 08R1AQUINO,JANE T
000000171DIRFR1 CASTRO,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 strOutputFilePa th.

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