Closing Excel and Query in Access

[Robert M]

I have the following file to create an Excell Spreadsheet from and Acess Query.

Private Sub PrntXLS_Click()

If LookUp = "" Then
MsgBox "Please Select Attendee via Drop Down Menu"
Exit Sub
Else
docmd.OpenQuery "OutputXLS_qry", acViewNormal, acEdit
docmd.Close acForm, "PrintGroupReportsForm"
docmd.OutputTo acOutputQuery, "OutputXLS_qry", acFormatXLS, , True
End If

I use a Combo Box to set the criteria for the creating the Parameter Query. From there the Query is sent to the Excell creating a very good spreadsheet.
My problem is that once all the above is done, how do I close the Excell Spreadsheet I have just created as well as the query that created it.

Thank you for your help on this matter.

Robert M

 

[boblarson]

Okay a few basics here.

1. You do NOT need to open the query to use the DoCmd.OutputTo.

2. If you want to close the Excel File you will have to use it another way. You will need to use Excel Common Object Model programming. So, if you want to send the query to Excel and be able to close it, you can by using my code on my website for the export of the query and then you will need to add at the bottom of the function -

xlWBk.SaveAs "PutNameAndPathHere"
xlWBk.Close
 
ApXL.Quit
 
Set ApXL = Nothing

 

[Robert M]

Thank You Bob
I was opening the query because of another "Customizing Access Parameter Query" dialog that I got from www.fontstuff.com/access/acctut08.htm
I still have a lot of tweeking to do with the program so that the user can click on a y/n box next to the name(s) to create the spreadsheet as I have it do for creating reports.
Thanks for your programing knowledge and examples. I will see if it fits what I need to get done.

Robert M