You might try to open a new workbook, data, new query. Point it to the
qryYourQuery in the Access DB, then run your logic.

If it's faster, I would use this method. If it's a parameterized query in
Access, I think it should still work in MS Query using Excel. You would have
to tuck your €˜RefreshQuery logic in the OnOpenWorkbook event, after that,
call your subtotal routine.

If the DB is on a shared drive, you should not have any issues with your
users refreshing. But, you will have to configure a ODBC driver if they do
not already have one installed.

Bob

"Skip Bisconer" wrote:

I am exporting an Access query to Excel and opening that file to manipulate
the data then copy back to an Access Linked. I was told to put this question
here by the Access MVP's.

My problem here is that just to export the query and open the file, takes
around 7 seconds. That's good. But if I put in a macro to run when the Excel
workbook opens it takes 3 minutes and makes the user respond to a halt
warning of potential viruses. As in takes the macro around 3 minutes to sort,
subtotal and save to the linked file the total time between the two is
excessive and requires user input on the Access side. I am trying to make
this seamless from Access to Excel to Access.

I have looked at doing the reverse importing the specific query from Access
which has a speed that is acceptable to me but it requires the Access
database to be closed which is unacceptable because it requires more user
interface than I think I want. I hope I have clearly stated the issue. I
would appreciated any input you my may have. I am working with Office 2003 on
this one.