Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
This is more of an Excel question than a VBA question, but....
I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location.. The source code is located in a separate macro-enabled file (.xlsm) I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes. Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action. The system is running Windows 7/MS Office 2007 Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
Some food for thought...
There's a number of reasons why the alert is displayed when no user changes have occured. The most common is when cells contain formulas and CalculationMode is set to Automatic. Excel calcs these on opening and so considers the file has changed and needs to be saved. Editing cells is not the only action that can trigger this alert. For example, if a user expands/collapses an outline a change occured. Actions that fire certain events can trigger the alert as well. You could... ...have your XLSM code turn Application.DisplayAlerts off and set Application.Calculation to xlCalculationManual *before* it opens files. ...add an events handler class to your XLSM that handles all Excel events and workbook/worksheet events so as to control the behavior of your project under specific runtime conditions. This is fairly complex but once the class is set up it's reusable in other projects. This would be my preference because I can trap any workbook's BeforeClose event and run my own code regarding a file's 'Saved' state (or any other condition I choose). -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
c1802362 wrote:
This is more of an Excel question than a VBA question, but.... I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location. The source code is located in a separate macro-enabled file (.xlsm) I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes. Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action. The system is running Windows 7/MS Office 2007 Art open new excel file and write formula =now () next, save it, close , reopen and try to close. Do you see a message? That is why Excel asks you if you want to save changes. Values in your file have been modified. It does not have to be done by user. Formula can modified its value without user interaction. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
witek wrote:
c1802362 wrote: This is more of an Excel question than a VBA question, but.... I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location. The source code is located in a separate macro-enabled file (.xlsm) I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes. Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action. The system is running Windows 7/MS Office 2007 Art open new excel file and write formula =now () next, save it, close , reopen and try to close. Do you see a message? That is why Excel asks you if you want to save changes. Values in your file have been modified. It does not have to be done by user. Formula can modified its value without user interaction. the other reason can be if your program saved xlsx files not fully recalculated. It recalculates when somebody opens it next time. Check in your xlsm program if calculation mode is set to automatic or recalculate file manually before closing. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
witek wrote:
witek wrote: c1802362 wrote: This is more of an Excel question than a VBA question, but.... I recently wrote a large VBA program that retrieves excel files (.xlsx) from a network directory, updates each file with downloaded system data, reduces the data, then writes the updated excel file back to its source location. The source code is located in a separate macro-enabled file (.xlsm) I've written numerous programs like this in the past with no issues. In this instance, opening up any of the modified files to view the updates results in a 'Do you want to save the changes...' pop-up upon exiting the file, even if there have been no changes. Where in the system is this type of message being triggered from? I'm not finding any properties that flag this type of action. The system is running Windows 7/MS Office 2007 Art open new excel file and write formula =now () next, save it, close , reopen and try to close. Do you see a message? That is why Excel asks you if you want to save changes. Values in your file have been modified. It does not have to be done by user. Formula can modified its value without user interaction. the other reason can be if your program saved xlsx files not fully recalculated. It recalculates when somebody opens it next time. Check in your xlsm program if calculation mode is set to automatic or recalculate file manually before closing. and the last idea. Does it ask you to save changes every time you open it (as interactive user) or only first time? If it asks only once (during first opening after modification) check your xlsm program for calculation mode if it asks every time you open xlsx. Search xlsx file for volatile formulas. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
On Wednesday, February 13, 2013 12:27:13 PM UTC-5, GS wrote:
Some food for thought... I'll look into setting up the class as you've described Art |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
On Wednesday, February 13, 2013 12:50:53 PM UTC-5, witek wrote:
witek wrote: The target files, which always ask if you want to save (even if the file is opened then immediately closed) are static - there are no calculated ranges in the files,and no VBA code, but there are a number of conditionally formatted cells. The source code file already has an automatic recalc of the system (part of my standard utilites that reset the environment before exiting), and I've never had this issue before I erased all conditional formatting, saved the file, and it is still happening. I tried forcing a recalc, saved it, and nothing changed. Art |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
c1802362 wrote:
On Wednesday, February 13, 2013 12:50:53 PM UTC-5, witek wrote: witek wrote: The target files, which always ask if you want to save (even if the file is opened then immediately closed) are static - there are no calculated ranges in the files,and no VBA code, but there are a number of conditionally formatted cells. The source code file already has an automatic recalc of the system (part of my standard utilites that reset the environment before exiting), and I've never had this issue before I erased all conditional formatting, saved the file, and it is still happening. I tried forcing a recalc, saved it, and nothing changed. Art Does that also happen on another computer? Does that happen with new empty file? Try to delete sheet by sheet and see when it stops. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
yes
yes found it! I had given the blank data sheet to a colleague to format it in the required style of the report. Apparently he buried '=TODAY()' in a cell way off the working area. As you previously stated, this was forcing a recalc eveery time the workbook was opened... Ugggghhh! Art |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggling save warning message on or off
c1802362 wrote:
yes yes found it! I had given the blank data sheet to a colleague to format it in the required style of the report. Apparently he buried '=TODAY()' in a cell way off the working area. As you previously stated, this was forcing a recalc eveery time the workbook was opened... Ugggghhh! Art :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
warning message | Excel Discussion (Misc queries) | |||
Warning Message | Excel Programming | |||
Warning Message | Excel Discussion (Misc queries) | |||
Warning Message | Excel Worksheet Functions | |||
Pop up warning message! | Excel Programming |