-----Original Message-----
I am trying to export a query to a text file when a user clicks a button.
Is there a way, through code, to export a query to a given path, with a
delimited format? I am looking to manipulate the to get no delimiter
characters or "" marks. Something like"
FiledX FieldYHJYYU FieldHJY FieldIOIO
Is this possible? I am playing with the TransferText function in a macro,
but cannot figure out how to get this format.
Thank you very much.
If you manually export the query, you will be prompted
with the Export Text Wizard. From here, you can specify
delimited (and set your delimiter) or fixed width. Also,
you can choose whether or not to have a text qualifier
(""). Before you click FINISH, click the ADVANCED
button. There will then be an option to save this export
specification (SAVE AS). In the Transfertext macro
option, you can than use that export specification.
As far as prompting the user, you will need to use code to
accomplish this. The OutputTo macro function will
automatically prompt but TransferText does not.
To do the code, I would first create the macro using a
default file path and name. Close the macro, click TOOLS,
MACRO, then CONVERT MACRO TO VISUAL BASIC. You can then
add a couple of lines of code to prompt them. It won't be
a browse window, but it should get the job done. I hope
this helps.
Barry
Function YourMacro()
On Error GoTo YourMacro_Err
Dim strUserInput As String
'This will prompt the user with an input box where
they can enter the file path and name.
strUserInput = InputBox("Please enter the desired file
path and name.", "Input required...", "C:\defaultpath")
' This will export your query using a delimited format
based on your specifications. It also uses their input as
the file output destination.
DoCmd.TransferText
acExportDelim, "APImportSpecification", "qryYourQuery",
strUserInput, True, ""
YourMacro_Exit:
Exit Function
YourMacro_Err:
MsgBox Error$
Resume YourMacro_Exit
End Function
