I'm not exactly sure what is taking so long. I assume its the interface
between Access and Excel. I'm new at Access. If the same Excel file doesn't
have this macro in it the file opens in Excel in approximately 7 seconds. If
I put the macro in the file it take up to 4 minutes with Access on the screen
to get the a Microsoft halt screen warning about malicious macros. When I
respond OK to the halt it takes about 2 seconds for Excel to open, take the
focus and start the macro. I would like it to only take the 7 seconds to open
the workbook with the macro.

"Tim Zych" wrote:

Earlier in the thread you say:

I just thought someone may have a
way to speed the opening of an Excel sheet with a macro in the
Workbook_Open.

What, exactly, is taking a long time? The opening of the workbook? The macro
processing ? Calculation?

Are you saying you perform an action in Access, say xlApp.Worksbooks.Open
"filename", and wait 4 minutes for the book to open, before even the macro
does any processing? If not, how long should the macro take to run? Perhaps
4 minutes is correct based on how it's written, and you need to work on
optimizing the macro.

and have it work from Access
without any user input other than clicking a button in Access.

You shouldn't get prompted to Enable Macros when programmatically opening
the XL workbook from Access.

xlApp.Workbooks.Open "filename.xls"


--
Tim Zych
SF, CA

"Skip Bisconer" wrote in message
...
Thanks for responding Tim.

The problem is this. If I just open my Excel workbook WITHOUT a
Workbook_Open macro that automates the entire process from sorting,
subtotaling and saving to the Excel workbook thats linked to my Access
database table it takes 7 seconds but the user has to run the macros in
Excel.

When I put the macro in Excel to run WITH Workbook_Open it takes 3.5 to
4
minutes and the user must response to a halt warning about malicious
macros
just to get the workbook open and start the process.

As I said all of this in my original post, I don't know how I can make any
clearer. I want to do this faster than 4 minutes and have it work from
Access
without any user input other than clicking a button in Access.







"Tim Zych" wrote:

I don't get the explanation, except for the it takes long part, but
otherwise I don't follow what the exact problem is. I've automated XL
from
Access and had excellent success, and I'd like to help, but your
explanation
is kind of all over the place.


--
Tim Zych
SF, CA


"Skip Bisconer" wrote in message
...
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.