VBA - output dynamic $ query to excel - $ gets truncated

[PhilipEwen]

Hi,

As you may have guessed by the sunject heading, i am trying to output the results of a query to excel.
The query is dynamic ( build by vba depensing on form entry criteria ) and what i am finding is that if i use the following:

DoCmd.OUtputTo acOutputQuery, txt_exportsql, "MicrosoftExcel(*.xls)","", True, ""

then it seems to call the filename the initial part of the query. This therefore truncates the query and i don;t get the results.

What therefore would you recommend the best way is to export to Excel based on the fact that i have a text string which contains the query ( sql="SELECT contacts.name etc etc )

I need to be able to save the excel spreadsheet in variable places, so the filename cannot be hard-coded into the VBA.

Thanks for your help.

 

[PhilipEwen]

I suppose on further reflection, what I am asking is:

1. Should i create a stored query temporarily, then run the outputTo command on that query. Then delete the query.
This method allows me to select a filename if i use a macro to output to excel.

2. Alternatively is there a way that I can just parse a string ( the sql query ) directly to output to excel and then allow the user to select a location/filename before it runs

3. Just open the data in excel and then make the user save this manually.

I presume all this is possible based on my limited experience of VBA, but would not know how to do 2 or 3 and not sure if 1 is the best way forward.

Any advice appreciated.

 

[RichMorrison]

Philip,

Create a Recordset based on the SQL string you have built. Then output the RecordSet to Excel.

There is no need to build a temporary QueryDef.

RichM

 

[PhilipEwen]

Thanks for your response.
I think i have some standard code for creating a recordset from a query, but can you tell me how i transfer this recordset to Excel.
Please note I also need to be able to select a file location for this excel file.

Thanks again.