Can you run a query with out viewing it?

[jedcomyn]

exporting to excel

hi

I have created a search form that gives criteria to a query, and then runs that query. I get the results in a query box. Can i export this to an excel file automatically? Without having to view the query box?


many thanks

jed

 

[Mile-O]

Yes. Do a search in the help files for the TransferSpreadsheet method. You can also find this in the macros.

 

[jedcomyn]

Many excel files

thank you for pointing me in that direction i need to find my way around the macros.

I have implemented the export macro, but i have two problems.

1) i must specify a filename for the file it is exported to, within the macro. If i run more than one search that file will be overwritten. i need to export to a different excel file each time i search. Can this be done, i think i may have to use code instead.

2) The data is exported but then reverts to access, can i stop this so it is exported and then the excel file stays on the screen?

Thank you for all your help

Jed

 

[jedcomyn]

I want to run a query and then export it to excel, at the moment i am using this code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim stDocName As String

stDocName = "qrySource"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.RunMacro "macexportsou", 1
DoCmd.Close acQuery, "qrysource", acSaveNo


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


However, this opens the query and then closes it producing a query box for a short period of time. I just want the results of the query to appear in the excel file dictated by the macro.

Hope someone can help.

Jed

 

[jedcomyn]

Solved one side not the other!!!

Solved the mulitple searches using OUTPUT TO thank you very much,
but i still get the white query box for about a second,
instead of OPENQuery is there another command to run a query?


Jed

 

[jedcomyn]

Yes!!!!!

Thankyou soooo much, that is perfect, didn't realise that output would run the query as well.


Forever in my debt.....

Jed

 

[Mile-O]

TransferSpreadsheet is another avenue to look at.

EDIT: Can you stop making more than one thread for the same problem, please?