Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
Is there a way to prevent calculations from running when opening a file? I
can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
Try to set Calculation to Manual before opening this workbook
(ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
I'm using Excel 2007 (which has this in a different place), but I already
tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
right click on a sheet tab
go to View Code in Project - VBA Project window double click on ThisWorkbook select Workbook in a window where (General) is shown, select Open form the window to the right of it this is what you should see: Private Sub Workbook_Open() End Sub then in between those a/m 2 lines insert the following: Application.Calculation=xlCalculationManual the whole procedure should read as follows: Private Sub Workbook_Open() Application.Calculation=xlCalculationManual End Sub on next opening of the file the calculation will be set to manual pls click YES if this was helpful On 17 Mar, 15:18, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? *I can't get the file to open at all because the calculations take hours and hours. *I need to prevent them from running as I open the file.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
First of all...............what size of workbook and what types of
calculation take hours and hours? Maybe you should re-design? Secondly............Excel takes the calculation mode of the first workbook opened. Are you opening another workbook set to automatic calc before opening the offending workbook? If that's the case, setting the offender to manual calc means nothing. Gord Dibben MS Excel MVP On Tue, 17 Mar 2009 07:18:01 -0700, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
This would probably work if I could get the file open in the first place.
The problem is that I can't get it to finish opening the document since it's trying to run the calculations first. "Jarek Kujawa" wrote: right click on a sheet tab go to View Code in Project - VBA Project window double click on ThisWorkbook select Workbook in a window where (General) is shown, select Open form the window to the right of it this is what you should see: Private Sub Workbook_Open() End Sub then in between those a/m 2 lines insert the following: Application.Calculation=xlCalculationManual the whole procedure should read as follows: Private Sub Workbook_Open() Application.Calculation=xlCalculationManual End Sub on next opening of the file the calculation will be set to manual pls click YES if this was helpful On 17 Mar, 15:18, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
The file has ~54,000 rows and 10 columsn of data, and the formula is a 4 part
COUNTIFS. That seemed like a good idea at the time (I think I was tired). But now I can't get the file to finish opening (it's hogging both processors completely for hours). I've tried setting the currently open workbook ("Book1") to manual, then open the file - no luck. I've tried using a macro that first sets Application.Calculation to xlManual, then it opens the file. Still no luck. I basically need a way for Excel to open the file without trying to make sense of those formulas. "Gord Dibben" wrote: First of all...............what size of workbook and what types of calculation take hours and hours? Maybe you should re-design? Secondly............Excel takes the calculation mode of the first workbook opened. Are you opening another workbook set to automatic calc before opening the offending workbook? If that's the case, setting the offender to manual calc means nothing. Gord Dibben MS Excel MVP On Tue, 17 Mar 2009 07:18:01 -0700, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
I think what Gord is saying is open any other Excel file... set the mode to
Manual... save... close Excel... Open this file and then open your file... "littlefb" wrote: The file has ~54,000 rows and 10 columsn of data, and the formula is a 4 part COUNTIFS. That seemed like a good idea at the time (I think I was tired). But now I can't get the file to finish opening (it's hogging both processors completely for hours). I've tried setting the currently open workbook ("Book1") to manual, then open the file - no luck. I've tried using a macro that first sets Application.Calculation to xlManual, then it opens the file. Still no luck. I basically need a way for Excel to open the file without trying to make sense of those formulas. "Gord Dibben" wrote: First of all...............what size of workbook and what types of calculation take hours and hours? Maybe you should re-design? Secondly............Excel takes the calculation mode of the first workbook opened. Are you opening another workbook set to automatic calc before opening the offending workbook? If that's the case, setting the offender to manual calc means nothing. Gord Dibben MS Excel MVP On Tue, 17 Mar 2009 07:18:01 -0700, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
I'd try it again.
littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
I've tried it this way (with an existing file rather than the unsaved
"Book1"). It's still trying to process that calculation when it opens. I've had the processors nearly maxed out for over 20 minutes (and I'm trying it on a faster computer than before). I think I'm giving up and seeing if I can import just the data itself through MSAccess. Any other ideas that don't involve additional variations of "set calculation to manual" would be appreciated! "Sheeloo" wrote: I think what Gord is saying is open any other Excel file... set the mode to Manual... save... close Excel... Open this file and then open your file... "littlefb" wrote: The file has ~54,000 rows and 10 columsn of data, and the formula is a 4 part COUNTIFS. That seemed like a good idea at the time (I think I was tired). But now I can't get the file to finish opening (it's hogging both processors completely for hours). I've tried setting the currently open workbook ("Book1") to manual, then open the file - no luck. I've tried using a macro that first sets Application.Calculation to xlManual, then it opens the file. Still no luck. I basically need a way for Excel to open the file without trying to make sense of those formulas. "Gord Dibben" wrote: First of all...............what size of workbook and what types of calculation take hours and hours? Maybe you should re-design? Secondly............Excel takes the calculation mode of the first workbook opened. Are you opening another workbook set to automatic calc before opening the offending workbook? If that's the case, setting the offender to manual calc means nothing. Gord Dibben MS Excel MVP On Tue, 17 Mar 2009 07:18:01 -0700, littlefb wrote: I'm using Excel 2007 (which has this in a different place), but I already tried and it didn't work. "Stefi" wrote: Try to set Calculation to Manual before opening this workbook (ToolsOptionsCalculation tab)! Regards, Stefi littlefb ezt *rta: Is there a way to prevent calculations from running when opening a file? I can't get the file to open at all because the calculations take hours and hours. I need to prevent them from running as I open the file. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent calculation on open
The import into MSAccess worked beautifully. I was able to salvage all my
data (and even the values from the misguided COUNTIFS formulas). None of the methods suggested to get Excel to open this file worked. Thanks for the help anyway! "littlefb" wrote: I've tried it this way (with an existing file rather than the unsaved "Book1"). It's still trying to process that calculation when it opens. I've had the processors nearly maxed out for over 20 minutes (and I'm trying it on a faster computer than before). I think I'm giving up and seeing if I can import just the data itself through MSAccess. Any other ideas that don't involve additional variations of "set calculation to manual" would be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent negative numbers from changing to positive in calculation | New Users to Excel | |||
Prevent EXCEL from rounding the result of a calculation | Excel Discussion (Misc queries) | |||
how TO prevent reviewing toolbar from popping up when I open A wks | Excel Discussion (Misc queries) | |||
Prevent Access To already open file | New Users to Excel | |||
Files open when starting-How to prevent | Setting up and Configuration of Excel |