Open Excel from Access, sending query info with a parameter

[mafrank101]

Gurus,

I have an Access (2003) query set up that has one filter criteria (Project Number) that it pulls from a form. I have set up a command button that opens an Excel (2007) workbook that then accepts the data from the query, and does some fancy stuff with it.

I am having difficulty in getting Excel to open properly with the filter criteria from the query. If I remove the filter criteria, it will open Excel just fine, however if I add the filter back to the query, I get an error that states :
"Too few parameters. Expected 1"

I realize this is because Excel cannot see the filter criteria from the form, however I have not found how to "preload" this filter into the query before sending it to Excel. The Excel workbook is set up with a connection to this query as well, however if I do a data refresh from Excel, I get the same error.

Below is the code from the command button in Access. Any help is appreciated.

Thanks

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook

Set appExcel = CreateObject("Excel.Application")

Set myWorkbook = appExcel.Workbooks.Open("C:\Documents and Settings\mfrank\My Documents\Book1.xlsx")


appExcel.ActiveWorkbook.RefreshAll

[ajetrumpet]

this does not make sense. or, I don't know how to read it. your code is opening a workbook. it has nothing to do with access. and there is also no "connection" to excel with your code. you are using DAO here, not ADO, which officially creates a connection using the jet engine (I believe). How does your access query relate to your workbook? Are you trying to FILTER your excel records based of off criteria that you have in Access somewhere? Like in your query?

If that is what you want, you need to use the FILTER object in excel, or the AUTOFILTER tool. You can do both of those via VBA, but you might have to record mouse clicks to figure out how to code it.

You can also do it through ADO code, and if you do it that way, you can use simple SQL to filter excel records because each sheet is treated like an actual table. That makes it easier of course, but ADO coding is more complex and harder to understand, in general, than DAO code is.

[mafrank101]

Thanks for your response. Forgive me, as I am a newbie at VBA (SQL developer).

A user will select a "project number" from a text box in an Access form. Once that is done, my hope was that they could click a button, which would open an Excel workbook that has a connection back to a query in the Access database. This Access query has a filter that uses the value of the "project number" that was entered into the Access form.

The Excel workbook should just get information about the Project Number that was selected, and then this data is used to create several graphs/pivot tables for analysis. Once the workbook is opened I don't need a dynamic connection between Excel and Access, just a data dump from Access to Excel.

Essentially, I want my users to enter a project number in Access, click a button, and get a workbook with analysis info about that project.

[ajetrumpet]

Essentially, I want my users to enter a project number in Access, click a button, and get a workbook with analysis info about that project.

You have a few different tools at your disposal. First of all, I don't believe that Access has a "copyfromrecordset" equivalent like excel does.

so what you want is a data transfer from access to excel. are you familiar with the fact that you can right click the object in access and export it to another program?

I would suggest you look at these methods in vba:

docmd.outputto()

docmd.transferspreadsheet()

docmd.runimportexport()

any of those might allow you to export the query you want, and if so, you wouldn't have to deal with the filter in vba because it would already be applied via the access query.

I am not sure if you can use all of those above methods with an existing workbook. trial and error for that.