Passing parameters between Excel and Access

[ebrooks54]

I thought I had a creative answer to a problem I was having, but it doesn't quite work:

I have a spreadsheet that retrieves external data from an Access Table. In the spreadsheet, I have a named range called "AsofDate", which I link to in the Access Database. In the query in Access, there is a criteria of >[AsofDate].AsofDate in the OnSaleDate field.

Access is actually not running as I do the following:

Open the spreadsheet; enter as of date; run a macro that refreshes the query that gets the external data.

Unfortunately, this does not seem to quite work. It appears to me that the Access link to the Excel spreadsheet is not 'live'. That is, if I want to pick up the data I typed in, I would first have to save the spreadsheet.

Is that true? Is there a way to establish a live link? Is there a better way to pass this value to my Access queries?

Thanks in advance...

 

[dhookom]

You can use the parameter right in the link definition. Start by editing the MS Query and enter a parameter like [As Of Date] as a criteria. You can then use the parameters of the query to point the [As Of Date] to a cell/range on your worksheet.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]