Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected file change
I have a .xls file, opened by Excel 2003.
When I open it, either by clicking it, or from a shortcut, or by opening it from Excel, or even in safe mode, it is changed and I get "Do you want to save the changes you made to 'X.xls'?" I can open other files without changing them. I have a workaround. Public Sub auto_open() ActiveWorkbook.Saved = True End Sub I would like to know what sets ActiveWorkbook.Saved = False before auto_open is run. On a second 2003 installation, I see the same behaviour. I also see it in 2010. ;) -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected file change
"Walter Briscoe" wrote:
I have a .xls file, opened by Excel 2003. When I open it, either by clicking it, or from a shortcut, or by opening it from Excel, or even in safe mode, it is changed and I get "Do you want to save the changes you made to 'X.xls'?" I can open other files without changing them. This can happen when opening files in Automatic calculation mode that contain formulas that reference "volatile" functions. Some obvious "volatile" functions that do change with each recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you don't use them, because I infer that you do not expect the file to actually change. Some less-obvious "volatile" functions are OFFSET and INDIRECT. But I found that this situation (prompted to save when no apparent change was made) can also happen for uses of INDEX, which is not a "volatile" function. However, it does not always happen. And some uses of some objects cause this, but again not always. For example, some charts, IIRC. You might or might not find more information about this by reading http://www.decisionmodels.com/calcsecretse.htm and exploring other links there. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected file change
In message of Sat, 28 Jul 2012 09:35:05 in
microsoft.public.excel.programming, joeu2004 writes "Walter Briscoe" wrote: I have a .xls file, opened by Excel 2003. When I open it, either by clicking it, or from a shortcut, or by opening it from Excel, or even in safe mode, it is changed and I get "Do you want to save the changes you made to 'X.xls'?" I can open other files without changing them. This can happen when opening files in Automatic calculation mode that contain formulas that reference "volatile" functions. Some obvious "volatile" functions that do change with each recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you don't use them, because I infer that you do not expect the file to actually change. Some less-obvious "volatile" functions are OFFSET and INDIRECT. [snip] Thanks for a helpful reply. I thought about volatile functions after posting. I had considered NOW, but would not have thought of INDIRECT. I trimmed my workbook to a single example "constant" formula =INDIRECT("Sheet1!J"&201) In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is =MATCH($B$2,Sheet1!$A:$A,0) I can write some code to evaluate the formulae when required. Any other ideas? -- Walter Briscoe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected file change
In message of Sun, 29 Jul 2012
10:49:05 in microsoft.public.excel.programming, Walter Briscoe writes In message of Sat, 28 Jul 2012 09:35:05 in microsoft.public.excel.programming, joeu2004 writes "Walter Briscoe" wrote: I have a .xls file, opened by Excel 2003. When I open it, either by clicking it, or from a shortcut, or by opening it from Excel, or even in safe mode, it is changed and I get "Do you want to save the changes you made to 'X.xls'?" I can open other files without changing them. This can happen when opening files in Automatic calculation mode that contain formulas that reference "volatile" functions. Some obvious "volatile" functions that do change with each recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you don't use them, because I infer that you do not expect the file to actually change. Some less-obvious "volatile" functions are OFFSET and INDIRECT. [snip] Thanks for a helpful reply. I thought about volatile functions after posting. I had considered NOW, but would not have thought of INDIRECT. I trimmed my workbook to a single example "constant" formula =INDIRECT("Sheet1!J"&201) In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is =MATCH($B$2,Sheet1!$A:$A,0) I can write some code to evaluate the formulae when required. Any other ideas? Sorry to add a second reply. I have 17 sheets. In each, INDIRECT is used 13 times in a subrow. I had used B2 in each sheet to do MATCH and avoid repetition of the calculation. I NOW do D2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,4) E2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,5) .... P2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,16) In that, 1) I don't much like Sheet1!$A$1:$Z$512. I suppose I could use a named range for it. Is there a way to specify the used range in a worksheet? 2) I have 4 in my formula in cells(2,4), 5 in cells(2,5), ... 16 in cells(2,16). Is there any way to use the fact that the numbers match? My file now opens without changing itself and seems to open much faster. Sadly, I had not measured and did not save the file before change. -- Walter Briscoe |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected file change
In message of Mon, 30 Jul 2012
09:12:13 in microsoft.public.excel.programming, Walter Briscoe writes In message of Sun, 29 Jul 2012 10:49:05 in microsoft.public.excel.programming, Walter Briscoe writes In message of Sat, 28 Jul 2012 09:35:05 in microsoft.public.excel.programming, joeu2004 writes "Walter Briscoe" wrote: I have a .xls file, opened by Excel 2003. When I open it, either by clicking it, or from a shortcut, or by opening it from Excel, or even in safe mode, it is changed and I get "Do you want to save the changes you made to 'X.xls'?" I can open other files without changing them. This can happen when opening files in Automatic calculation mode that contain formulas that reference "volatile" functions. Some obvious "volatile" functions that do change with each recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you don't use them, because I infer that you do not expect the file to actually change. Some less-obvious "volatile" functions are OFFSET and INDIRECT. [snip] Thanks for a helpful reply. I thought about volatile functions after posting. I had considered NOW, but would not have thought of INDIRECT. I trimmed my workbook to a single example "constant" formula =INDIRECT("Sheet1!J"&201) In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is =MATCH($B$2,Sheet1!$A:$A,0) I can write some code to evaluate the formulae when required. Any other ideas? Sorry to add a second reply. I have 17 sheets. In each, INDIRECT is used 13 times in a subrow. I had used B2 in each sheet to do MATCH and avoid repetition of the calculation. I NOW do D2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,4) E2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,5) ... P2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,16) In that, 1) I don't much like Sheet1!$A$1:$Z$512. I suppose I could use a named range for it. Is there a way to specify the used range in a worksheet? 2) I have 4 in my formula in cells(2,4), 5 in cells(2,5), ... 16 in cells(2,16). Is there any way to use the fact that the numbers match? Indeed, there is! COLUMN() and ROW() have the useful feature of accessing the current cell, when given no argument. I have a named range called Stations and all the relevant cells are set to "=VLOOKUP($B$2,Stations,COLUMN())". My file now opens without changing itself and seems to open much faster. Sadly, I had not measured and did not save the file before change. -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unexpected change in "arrow keys" | Excel Discussion (Misc queries) | |||
unexpected file lock by user | Excel Worksheet Functions | |||
Unexpected message when first opening this 2007 Excel file | Excel Discussion (Misc queries) | |||
"unexpected file lock by . " error | Excel Discussion (Misc queries) | |||
Unexpected error message on closing an Excel file | Excel Discussion (Misc queries) |