I wrestled for a while with pushing data into Excel using various methods, all of which had some sort of problem. I finally opted for a solution I think can't be beat...
I create a simple Excel Template for each report. It includes a Macro than runs the database query and self-populates, then applies any additional formatting required. Since this is all from within Excel, it works very smoothly, and I have access to all functionality.
To create the basic Macro, turn on the Macro Recorder. Perform a database query (Tools/Import External Data/New Database Query, then follow the wizard). It will use MSQuery to retrieve the data. If you want to run a Stored Procedure, simply start the wizard, click Cancel, and when prompted to edit the query, click yes. Close the table selector, and click the SQL button to edit the query. You can enter "EXEC myStoredProcedure" or whatever there, and it will run. Simply closing MSQuery will return the data to Excel.
After that, perform the formatting you want, then stop the Macro Recorder. Open the VB editor, and you should see a Macro with all the basic code you need, ready for tweaking.
If you need to collect parameters for the report, create a simply UserForm. I have several reports that propt the user for the month and year this way using two comboboxes.
Here's the best part -- I give the report to the end user, and they can run it anytime they want, no more coming to me! I can control permissions on the DB side. I like to do this be specifying NT Authentication when creating the query and simply adding.removing object permissions as needed. SQL authentication will work as well, just that anyone with the template can use it.