Prevent "[File] now available for editing" popup

[Glenn]

Hi

I have some code that opens a file that MUST be writable so that it
can update some data and save it. To ensure it is writable I do a
check for Readonly = True, and if this is the case (ie someone else
has it open), I start a loop of closing the file waiting some time and
opening it again. This resolves conflicts that I expect only to last a
few seconds.

This works well. The loop exists when it finds ReadOnly = False and
the rest of the code executes.

However Excel then pops up the standard "The file is now available for
editing" dialog with a couple of options. I don't want the user to see
this. is there anyway i can prevent Excel being 'helpful' and stopping
the dialog box appearing? Application.screenupdating is no good as
this happens after my code has executed.

Cheers
Glenn

 

[Jim Cone]

Try setting the "Notify" parameter to False in the Open method code.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Glenn"
wrote in message
Hi
I have some code that opens a file that MUST be writable so that it
can update some data and save it. To ensure it is writable I do a
check for Readonly = True, and if this is the case (ie someone else
has it open), I start a loop of closing the file waiting some time and
opening it again. This resolves conflicts that I expect only to last a
few seconds.
This works well. The loop exists when it finds ReadOnly = False and
the rest of the code executes.
However Excel then pops up the standard "The file is now available for
editing" dialog with a couple of options. I don't want the user to see
this. is there anyway i can prevent Excel being 'helpful' and stopping
the dialog box appearing? Application.screenupdating is no good as
this happens after my code has executed.
Cheers
Glenn

 

[Glenn]

Thanks for your response Jim. I'm not sure what you mean however.
There are no forms associated with this. The code actually executes
when the spreadsheet is opened; its purpose is to open another
spreadsheet to grab a unique ID into a variable, add 1 to the ID on
that sheet, then save and close the file (hence the need to be
writable), which takes less than a second. But if it found that the ID
file was read only it goes through the loop as described in my
previous post until it is not read only.

The problem occurs after the loop finishes executing (assuming it was
required) and after the rest of the code has completed. My code is no
longer running at this point, but after a short pause Excel displays
the File Available message. This is a standard Excel message that
applies to files that you opened as read only to alert you that they
are now available. In this case the user does not need to see this,
and in fact one of the options it presents will open the file in
question for the user to edit! and this should only ever happen in
code for a brief moment and then be closed again.

Is there a display notifications or displayalerts option for the Excel
App as a whole that I cold run on startup or someother way of
disabling this notification?

 

[Jim Cone]

From the Excel VBA help file for the "Open" method...
'-- Quote:
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin,
Delimiter, Editable, Notify, Converter, AddToMRU)

expression: Required. An expression that returns a Workbooks or RecentFile object.

Notify: Optional Variant. If the file cannot be opened in read/write mode,
this argument is True to add the file to the file notification list.
Microsoft Excel will open the file as read-only, poll the file notification list,
and then notify the user when the file becomes available.
If this argument is False or omitted, no notification is requested,
and any attempts to open an unavailable file will fail.
'-- End quote.
It appears to me, that contrary to the above, the default value for "Notify" is True.

Also, Application.DisplayAlerts = False may do what you want.
Make sure to return it to true before exiting the code.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Glenn" <[email protected]>
wrote in message
Thanks for your response Jim. I'm not sure what you mean however.
There are no forms associated with this. The code actually executes
when the spreadsheet is opened; its purpose is to open another
spreadsheet to grab a unique ID into a variable, add 1 to the ID on
that sheet, then save and close the file (hence the need to be
writable), which takes less than a second. But if it found that the ID
file was read only it goes through the loop as described in my
previous post until it is not read only.

The problem occurs after the loop finishes executing (assuming it was
required) and after the rest of the code has completed. My code is no
longer running at this point, but after a short pause Excel displays
the File Available message. This is a standard Excel message that
applies to files that you opened as read only to alert you that they
are now available. In this case the user does not need to see this,
and in fact one of the options it presents will open the file in
question for the user to edit! and this should only ever happen in
code for a brief moment and then be closed again.

Is there a display notifications or displayalerts option for the Excel
App as a whole that I cold run on startup or someother way of
disabling this notification?

 

[Glenn]

Jim

Nailed it. Nice work. Thanks for your help!

Cheers
Glenn