Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. "Bob Jeffery" wrote: 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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Opening Excel Workbook from Access
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Opening Shared Workbook | New Users to Excel | |||
Opening an Excel workbook with VBA from Access | Excel Worksheet Functions | |||
Workbook slow opening & saving | Excel Discussion (Misc queries) | |||
Why is workbook so slow opening? | Excel Discussion (Misc queries) | |||
Slow opening Excel Workbook with over 50 Worksheets | Excel Discussion (Misc queries) |