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