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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|