export 1 query multiple times to 1 excel file

[Guest]

hello

i have been trying to export a query to an excel file. this query will run once and the data that it selects i want it to export to excel. then the query runs again but the WHERE conditions are changed (controled by vba, the WHERE conditions are placed on a form and the query looks for the WHERE conditions on the form) its the same query but the data now is different. i would like to export that data to the same excel file except the export doesn't work.

here's a sample of my vba code:
' WHERE conditions are placed on a form and the
' query looks for the WHERE conditions on the form

Me.Text108 = "7000" 'this is the Where condition for my query.

'the first export to the excel file.
DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcel(*.xls)", "c:\1.xls", False, ""

Me.Text108 = "8000" 'my where condition changes

'the second export to my excel file.
DoCmd.TransferSpreadsheet acExport, 8, "Query1", "c:\1.xls"

Me.Text108 = "9000" 'my where condition changes

'the third export to my excel file.
DoCmd.TransferSpreadsheet acExport, 8, "Query1", "c:\1.xls"

so on and so forth,
the second export works however the third, fourth, fifth, etc etc does not. I know that if i wrote multiple queries i wouldn't have this problem however i would have to end up writing too many queries. I know that in the export file that the tab name is the query name so I have also tried to change the tab names (using automation) after each query is export the tab names change but the 3rd 4th 5th etc etc export still does not work. I've also tired changing the field names but it has not work. I'm pulling my hair out tring to figure out why Access won't let me export this query multiple times.

please help me!!!

thanks
mike

 

[Ken Snell]

see reply to your other post of same question.

--

Ken Snell
<MS ACCESS MVP>

hello

i have been trying to export a query to an excel file. this query will

run once and the data that it selects i want it to export to excel. then the
query runs again but the WHERE conditions are changed (controled by vba, the
WHERE conditions are placed on a form and the query looks for the WHERE
conditions on the form) its the same query but the data now is different. i
would like to export that data to the same excel file except the export
doesn't work.

here's a sample of my vba code:
' WHERE conditions are placed on a form and the
' query looks for the WHERE conditions on the form

Me.Text108 = "7000" 'this is the Where condition for my query.

'the first export to the excel file.
DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcel(*.xls)", "c:\1.xls", False, ""

Me.Text108 = "8000" 'my where condition changes

'the second export to my excel file.
DoCmd.TransferSpreadsheet acExport, 8, "Query1", "c:\1.xls"

Me.Text108 = "9000" 'my where condition changes

'the third export to my excel file.
DoCmd.TransferSpreadsheet acExport, 8, "Query1", "c:\1.xls"

so on and so forth,
the second export works however the third, fourth, fifth, etc etc does

not. I know that if i wrote multiple queries i wouldn't have this problem
however i would have to end up writing too many queries. I know that in
the export file that the tab name is the query name so I have also tried to
change the tab names (using automation) after each query is export the tab
names change but the 3rd 4th 5th etc etc export still does not work. I've
also tired changing the field names but it has not work. I'm pulling my
hair out tring to figure out why Access won't let me export this query
multiple times.