How can I pass a parameter from an Access Form to an Excel Parameter query?

[firemark1817]

I have an Excel Pivot report setup that runs a parameter query when the file is opened. It prompts for a File Number and then retrieves the data from a SQL source. Here's the code of the parameter query.

SELECT * FROM DI_Bronze.dbo.V_DIL212 V_DIL212 where V_DIL212.UDS_CLAIM = ?;

This works quite well as a standalone Excel Report.

The issue is that I also have an Access DB Form with details about each file where a user has to enter information. In many cases they need to review the Pivot Report for the current file displayed on the Access Form.

There's no problem using a URL link to open the Pivot Report. The issue is, how to also pass the current file number from the Access form to the Excel parameter query.

Everything I've searched for so far shows only half a solution. Our users are going to be doing this about 5,000 times so it's important to minimize the number of clicks to get the data. Copy/Paste works but will give people carpal tunnel with so much repetition.

I tried searching options for opening Excel via a command line but cannot get it to accept and retain the File number parameter. Any suggestions for doing so or finding another way to accomplish the same result are welcome.

 

[Micron]

So you want to do something in Excel, from Excel, when an Access form is open, and the action depends on what the form is displaying? Did you consider a temp db table populated by the opening of the form which you then query from Excel? Or do I misunderstand the issue?

 

[firemark1817]

Almost. This is to function From the Access Form that's active, the file number is displayed and available to pass on as a parameter. The problem is figuring out how to get it to the Excel query as Excel tries to open/retrieve the data.

Yes, the original Excel report had a prompt for the file number that was written to a single record table and used in a join/filter to retrieve the data for that file. The problem with this approach is that we have multiple users doing the same thing for different claims. When 2 people try this at the same time, 1 gets the data for the correct claim, the other does not and it causes lots of confusion.

 

[Micron]

then maybe push the data to Excel from Access via Automation rather than trying to get it from Access while operating Excel. This way, you don't have to worry about whether or not the Access form is loaded, and since that seems to be a critical part of the operation, it makes perfect sense to me. You don't even have to worry about whether or not Excel is open. There are tons of examples on the net that deal with this approach.