A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

export 1 query multiple times to 1 excel file



 
 
Thread Tools Display Modes
  #1  
Old June 19th, 2004, 01:59 AM
WALRUSIM
external usenet poster
 
Posts: n/a
Default export 1 query multiple 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
  #2  
Old June 19th, 2004, 04:13 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default export 1 query multiple times to 1 excel file

see reply to your other post of same question.

--

Ken Snell
MS ACCESS MVP

"WALRUSIM" 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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.