Toggling save warning message on or off

[c1802362]

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

 

[GS]

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

 

[witek]

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.

 

[witek]

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.

 

[witek]

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.

 

[c1802362]

Some food for thought...

I'll look into setting up the class as you've described

Art

 

[c1802362]

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

 

[witek]

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.

 

[c1802362]

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

 

[witek]

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