Slow Opening Excel Workbook from Access

[Skip Bisconer]

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.

 

[Bob Jeffery]

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]

Thanks for responding Bob,

I'm not positive I understand what you are telling me. This is just a select
query and it's for a single user. Do you mean to just open a new workbook
from Access instead of opening a specific workbook? As I am a novice in
Access are you saying i can create a macro to run in Excel in Access? Sorry I
am such a dunce.

 

[Bob Jeffery]

Skip,

Try the following first and see if you can get this to work.

In Excel click on:
Data
Import External Data
New Database Query

Find the Database on the users machine and double click it
Next, scroll through the list and find the query you run in Access. i.e.
qryYourQuery
Once you see the table, double click on the * this will select all fields in
the query
Next, click on File then Return data to Microsoft Excel

If all works, you should see all the data in Excel showing the result of the
Access Query.

Regards,

Bob

 

[Skip Bisconer]

Bob,

I have tried this and yes it is much faster, however, it's not seamless from
Access. I have to do the spreadsheet transfer in Access then close it then
open Excel and do the import external data. I am trying to make this work for
people who know Access but don't know Excel. I have VBA to handle the process
in the Excel file including the import but because the user to leave Access
goes counter to what I am trying to accomplish. Maybe I can't do what I want
in Access in a reasonable amount of time. I just thought someone may have a
way to speed the opening of an Excel sheet with a macro in the Workbook_Open.

 

[Tim Zych]

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.

 

[Skip Bisconer]

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]

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"

 

[Skip Bisconer]

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.