Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent "[File] now available for editing" popup
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent "[File] now available for editing" popup
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent "[File] now available for editing" popup
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent "[File] now available for editing" popup
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" 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent "[File] now available for editing" popup
Jim
Nailed it. Nice work. Thanks for your help! Cheers Glenn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing an "IF" function across a worksheet/excel file | Excel Worksheet Functions | |||
"Locked for Editing" error on a non-protected file | Excel Discussion (Misc queries) | |||
"File in Use" Popup does not show | Excel Discussion (Misc queries) | |||
Prevent "XYZ.doc is locked for editing" message | Excel Programming | |||
disable the popup messege "this file contain macro" | Excel Programming |