DoCmd.TransferSpreadSheet query with paramaters

[Dav_evans]

Hi Im trying to use VBA's doCmd.TransferSpreadSheet command to export the results of an access query to excel. My problem is that my query takes a paramater and access asks me for its value with a popup box each time I want to do the export. Is there a way to set the value of paramater before I do the TSS so that access doesnt ask me for it?

my query looks like:

"Select * from Companies Where id=[x]"

thanks

 

[st3ve]

Make a temp table

You could make a new temp table with a MakeTable query and then export the newly made table.

eg SELECT * INTO test0
FROM testjunk Where id=[x];

Create a macro to run the queries as follows:

Set Warnings OFF
Run your new MakeTable query now, making a new table
Do the export from the newly made table
Delete the newly made table, optional
Set Warnings ON.

HTH

 

[Dav_evans]

looks good, but whats with the warnings

thanks for your help, the select into statement should solve the problem and the sequence of events youve got there make sense except what warnings are you refering to when you say 'turn off warnings' , and whats the code to turn them on and off?

thanks

 

[st3ve]

Set Warnings

Set Warnings refers to the dialog that you normally get when you run a query.
Usually says something like 'You are about to run a query that will ....'. This is handy to have on when testing you design, but if you switch them off in a macro, then the macro can do its job - ie run several instructions, automatically, without human intervention, You will still, however, get your parameter box displayed.

Code for warnings:
DoCmd.SetWarnings False ...turns warnings off
DoCmd.SetWarnings True ...turns warnings back on, (you may want to keep the warnings coming whilst you test another part of your design).

HTH