Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
Hey all:
I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
You can't tell if the macros are causing the problem. It doesn't matter if
they are event driven or not event driven. If the are UDF functions then they may make a difference. For example if you have the Date() function on you workbook. Everytime the book is opened your workbook the sheets will be recalculated and the UDF functions will get executed. "Harry" wrote: Hey all: I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
There are no UDF's. I found a couple of DATE functions, and converted them to
static dates. Still, no difference. I have some volatile functions (OFFSET, ROW, COLUMN). However, the workbook is in manual calculation mode. Could that be causing it to take as long as it is? "joel" wrote: You can't tell if the macros are causing the problem. It doesn't matter if they are event driven or not event driven. If the are UDF functions then they may make a difference. For example if you have the Date() function on you workbook. Everytime the book is opened your workbook the sheets will be recalculated and the UDF functions will get executed. "Harry" wrote: Hey all: I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
If it is only one workbook that you are having problems with then it is
usally because you have a lot of formulas on the worksheet. Earlier this week somebody suggested the excel thinks some of your worksheet have the wrong Lastcell making the sheet much larger than it actually is. What was recommended was to go to each worksheet and type CNTL-END and check if the last cell is beyong the end of your data. If it is then delete the rows and columns between your data the the last cell. The workbook size should decrease and you should get a speed improvement. "Harry" wrote: There are no UDF's. I found a couple of DATE functions, and converted them to static dates. Still, no difference. I have some volatile functions (OFFSET, ROW, COLUMN). However, the workbook is in manual calculation mode. Could that be causing it to take as long as it is? "joel" wrote: You can't tell if the macros are causing the problem. It doesn't matter if they are event driven or not event driven. If the are UDF functions then they may make a difference. For example if you have the Date() function on you workbook. Everytime the book is opened your workbook the sheets will be recalculated and the UDF functions will get executed. "Harry" wrote: Hey all: I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
I ran FastExcel on the file and wasn't able to get much of a speed
improvement (i used the clean excess range option). I also ran some of Debra Dalgesh's code to change the pivot table cache to the same PT. somehow, after saving those changes and moving the file to another PC, the file opened right up. Thanks for your help! "joel" wrote: If it is only one workbook that you are having problems with then it is usally because you have a lot of formulas on the worksheet. Earlier this week somebody suggested the excel thinks some of your worksheet have the wrong Lastcell making the sheet much larger than it actually is. What was recommended was to go to each worksheet and type CNTL-END and check if the last cell is beyong the end of your data. If it is then delete the rows and columns between your data the the last cell. The workbook size should decrease and you should get a speed improvement. "Harry" wrote: There are no UDF's. I found a couple of DATE functions, and converted them to static dates. Still, no difference. I have some volatile functions (OFFSET, ROW, COLUMN). However, the workbook is in manual calculation mode. Could that be causing it to take as long as it is? "joel" wrote: You can't tell if the macros are causing the problem. It doesn't matter if they are event driven or not event driven. If the are UDF functions then they may make a difference. For example if you have the Date() function on you workbook. Everytime the book is opened your workbook the sheets will be recalculated and the UDF functions will get executed. "Harry" wrote: Hey all: I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook opening takes 2 minutes
I wonder if you just moved the file to another PC if that fixed the problem.
It may be an indication that the file is fragmented and you need to run a deframenation on the original PC. "Harry" wrote: I ran FastExcel on the file and wasn't able to get much of a speed improvement (i used the clean excess range option). I also ran some of Debra Dalgesh's code to change the pivot table cache to the same PT. somehow, after saving those changes and moving the file to another PC, the file opened right up. Thanks for your help! "joel" wrote: If it is only one workbook that you are having problems with then it is usally because you have a lot of formulas on the worksheet. Earlier this week somebody suggested the excel thinks some of your worksheet have the wrong Lastcell making the sheet much larger than it actually is. What was recommended was to go to each worksheet and type CNTL-END and check if the last cell is beyong the end of your data. If it is then delete the rows and columns between your data the the last cell. The workbook size should decrease and you should get a speed improvement. "Harry" wrote: There are no UDF's. I found a couple of DATE functions, and converted them to static dates. Still, no difference. I have some volatile functions (OFFSET, ROW, COLUMN). However, the workbook is in manual calculation mode. Could that be causing it to take as long as it is? "joel" wrote: You can't tell if the macros are causing the problem. It doesn't matter if they are event driven or not event driven. If the are UDF functions then they may make a difference. For example if you have the Date() function on you workbook. Everytime the book is opened your workbook the sheets will be recalculated and the UDF functions will get executed. "Harry" wrote: Hey all: I have a workbook that takes 2 minutes to open once I enable macros. The macros are not workbook event driven (they are unrelated to the problem). I think the problem stems from an excessive # of pivot tables and pivot charts. I have 25 pivot tables, all using the same source data (the data is sliced and diced in different ways). I am also using a lot of database functions (DAVERAGE, DCOUNT, DCOUNTA) but I don't think this is adding to the problem. I am using a few functions from the analysis toolpack (NETWORKDAYS is the big one). The analysis toolpak is showing up as an external link (user is prompted to update links when workbook is open). Finally, I have a lot of VLOOKUPS that are essential to pulling data and putting it into charts, Can anyone offer some guidance? I am looking at knocking down the workbook opening time (2 minutes is too long). thanks, Harry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel now takes three minutes to load a file - didn't yesterday | Excel Discussion (Misc queries) | |||
It takes two full minutes to open existing files in Office 2003 | Excel Discussion (Misc queries) | |||
Vista, Onecare. It takes 5 minutes to open a file. Too long. | Excel Discussion (Misc queries) | |||
Takes Minutes to open | Excel Discussion (Misc queries) | |||
File takes 40 minutes to load (auto-calculation) | Excel Discussion (Misc queries) |