export 1 query 5 times to 1 excel file

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

Define "doesn't work". Do you get an error message? Do you see nothing? What
happens/doesn't happen? Are you sure that the third, fourth, and fifth
queries will create any records at all -- have you tried running the query
on its own using the parameters that you're putting on the form?

What you want to do should work as you have set it up, so there must be
another reason.


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
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

the first two queries get exported to the spreadsheet, but the second two simply do not. and yes when the queries run individually, it works fine however when i try to run them all together it does not. i thought that the way i have the vba writen out should have worked too but it does not.


"Ken Snell" wrote:

Define "doesn't work". Do you get an error message? Do you see nothing? What
happens/doesn't happen? Are you sure that the third, fourth, and fifth
queries will create any records at all -- have you tried running the query
on its own using the parameters that you're putting on the form?

What you want to do should work as you have set it up, so there must be
another reason.


--

Ken Snell
MS ACCESS MVP

You get no error message or error condition for the third, etc. query's
export? Did you try stepping through the code (using F8 key) with error
handling turned off?


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
the first two queries get exported to the spreadsheet, but the second two
simply do not. and yes when the queries run individually, it works fine
however when i try to run them all together it does not. i thought that the
way i have the vba writen out should have worked too but it does not.


"Ken Snell" wrote:

Define "doesn't work". Do you get an error message? Do you see nothing?
What
happens/doesn't happen? Are you sure that the third, fourth, and fifth
queries will create any records at all -- have you tried running the
query
on its own using the parameters that you're putting on the form?

What you want to do should work as you have set it up, so there must be
another reason.


--

Ken Snell
MS ACCESS MVP

no error codes because all the exports run, the thing is that the third export replaces the data from the second export. the fourth export replaces the data from the third export, and so on and so forth. so in the final export file i get the first export on the first sheet and the last export on the second sheet. if you want i can send you a small mdb (156 kb) file to show you what's going on. let me know if you want that file )

"Ken Snell" wrote:

You get no error message or error condition for the third, etc. query's
export? Did you try stepping through the code (using F8 key) with error
handling turned off?


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
the first two queries get exported to the spreadsheet, but the second two
simply do not. and yes when the queries run individually, it works fine
however when i try to run them all together it does not. i thought that the
way i have the vba writen out should have worked too but it does not.


"Ken Snell" wrote:

Define "doesn't work". Do you get an error message? Do you see nothing?
What
happens/doesn't happen? Are you sure that the third, fourth, and fifth
queries will create any records at all -- have you tried running the
query
on its own using the parameters that you're putting on the form?

What you want to do should work as you have set it up, so there must be
another reason.


--

Ken Snell
MS ACCESS MVP

Ok - I now understand what you're seeing.

TransferSpreadsheet is overwriting the previous query's data because, as
you've noted, the spreadsheet is named using the query's name (with an
additional character, likely a 1, to distinguish it from the sheet created
by the OutputTo action), and when the query name doesn't change, the same
spreadsheet is used for the subsequent queries. You don't see this in the
second query because you used DoCmd.OutputTo to create the original
spreadsheet file and the sheet that is created by it is different from the
one created by TransferSpreadsheet.

If you must use the same query as the basis for what you export, you'll need
to use VBA code to create a query based on the Query1, and then use the
newly created query as the export source. Something like this, perhaps (not
tested):

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()

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.
Set qdf = dbs.CreateQueryDef("Query1-2", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

Me.Text108 = "9000" 'my where condition changes
'the third export to my excel file.

Set qdf = dbs.CreateQueryDef("Query1-3", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

' ... continue for other two queries

dbs.Close
Set dbs = Nothing


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
no error codes because all the exports run, the thing is that the third
export replaces the data from the second export. the fourth export
replaces the data from the third export, and so on and so forth. so in the
final export file i get the first export on the first sheet and the last
export on the second sheet. if you want i can send you a small mdb (156 kb)
file to show you what's going on. let me know if you want that file
)

"Ken Snell" wrote:

You get no error message or error condition for the third, etc. query's
export? Did you try stepping through the code (using F8 key) with error
handling turned off?


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
the first two queries get exported to the spreadsheet, but the second
two
simply do not. and yes when the queries run individually, it works fine
however when i try to run them all together it does not. i thought that
the
way i have the vba writen out should have worked too but it does not.


"Ken Snell" wrote:

Define "doesn't work". Do you get an error message? Do you see
nothing?
What
happens/doesn't happen? Are you sure that the third, fourth, and
fifth
queries will create any records at all -- have you tried running the
query
on its own using the parameters that you're putting on the form?

What you want to do should work as you have set it up, so there must
be
another reason.


--

Ken Snell
MS ACCESS MVP

i tired what you recommended, it worked, as it should, but honestly i do'nt want to create copies of the query that is already written (your delete funciton doesn't work) in any case i had originally thought that it was because of the name of the query that caused the tables to be replaced so what i did in my original vba was to open the excel file after each export and rename the sheet (using automation) however that didn't do the trick. so what i guess i'm asking is do you know of a better way to do this?


"Ken Snell" wrote:

Ok - I now understand what you're seeing.

TransferSpreadsheet is overwriting the previous query's data because, as
you've noted, the spreadsheet is named using the query's name (with an
additional character, likely a 1, to distinguish it from the sheet created
by the OutputTo action), and when the query name doesn't change, the same
spreadsheet is used for the subsequent queries. You don't see this in the
second query because you used DoCmd.OutputTo to create the original
spreadsheet file and the sheet that is created by it is different from the
one created by TransferSpreadsheet.

If you must use the same query as the basis for what you export, you'll need
to use VBA code to create a query based on the Query1, and then use the
newly created query as the export source. Something like this, perhaps (not
tested):

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()

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.
Set qdf = dbs.CreateQueryDef("Query1-2", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

Me.Text108 = "9000" 'my where condition changes
'the third export to my excel file.

Set qdf = dbs.CreateQueryDef("Query1-3", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

' ... continue for other two queries

dbs.Close
Set dbs = Nothing


--

Ken Snell
MS ACCESS MVP

My apology... I mistyped the delete step:

dbs.QueryDefs.Delete qdf.Name


--

Ken Snell
MS ACCESS MVP

"Mike_Walrus" wrote in message
...
i tired what you recommended, it worked, as it should, but honestly i
do'nt want to create copies of the query that is already written (your
delete funciton doesn't work) in any case i had originally thought that it
was because of the name of the query that caused the tables to be replaced
so what i did in my original vba was to open the excel file after each
export and rename the sheet (using automation) however that didn't do the
trick. so what i guess i'm asking is do you know of a better way to do
this?


"Ken Snell" wrote:

Ok - I now understand what you're seeing.

TransferSpreadsheet is overwriting the previous query's data because, as
you've noted, the spreadsheet is named using the query's name (with an
additional character, likely a 1, to distinguish it from the sheet
created
by the OutputTo action), and when the query name doesn't change, the
same
spreadsheet is used for the subsequent queries. You don't see this in
the
second query because you used DoCmd.OutputTo to create the original
spreadsheet file and the sheet that is created by it is different from
the
one created by TransferSpreadsheet.

If you must use the same query as the basis for what you export, you'll
need
to use VBA code to create a query based on the Query1, and then use the
newly created query as the export source. Something like this, perhaps
(not
tested):

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()

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.
Set qdf = dbs.CreateQueryDef("Query1-2", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

Me.Text108 = "9000" 'my where condition changes
'the third export to my excel file.

Set qdf = dbs.CreateQueryDef("Query1-3", "SELECT * FROM Query1;")
DoCmd.TransferSpreadsheet acExport, 8, qdf.Name, "c:\1.xls"
qdf.Delete
Set qdf = Nothing

' ... continue for other two queries

dbs.Close
Set dbs = Nothing


--

Ken Snell
MS ACCESS MVP