Kill Excel Dialog Box (if it exists)

swjtx99
swjtx99 used Ask the Experts™
on
I am opening a file from a network drive, entering some data, then saving it with changes.

Randomly, the network will hang and a "uploading file to server...." dialog box will appear with a "cancel" button. What I want is if that box appears, autoclick the cancel button. The following code works, however, if that dialog box does not appear, sending the keystroke causes the next dialog box to be clicked through. I'd like to find a way to detect if the dialog box is there and only send the keystroke if it is.

Windows("MyFile.xlsx").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.SendKeys ("~") ' this works fine unless the dialog box doesn't appear, then it clicks through the next dialog box.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayAlerts = True
MsgBox ("Report Complete")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Set this line to false

Application.DisplayAlerts = True

Author

Commented:
Hi Professorjimjam,

Thanks for the reply. That didn't work. I need someway to not send that keystroke unless a dialog box is open or cancel the keystroke before it gets to the next dialog box.

Thanks
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Can you try this


indows("MyFile.xlsx").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.SendKeys ("~")
MsgBox ("Report Complete")
ActiveWorkbook.Close savechanges:=True
' this works fine unless
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayAlerts = True
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Hi Professorjimjam,

Thanks for your reply. Didn't work. It just clicks through the "report complete" dialog box and if the other one opens, it is displayed with a cancel button.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
I don't have your full code , why would you need to use the application send tilde that is used to press enter . Can you remove that line and try I mean the application send key

Author

Commented:
Hi ProfessorJimJam,

Let me explain. I am saving a file with changes to a network drive. Sometimes this "hangs up" and I get the dialog box. When that happens, I just want to automatically have the code click the default button (which is "cancel"). I don't want to send the tilde unless the dialog box is displayed.

If I remove that line and the "save" hangs up, I end up with a dialog box that says ""uploading file to server....".

Author

Commented:
Also, if I do not remove the line and it doesn't hang up, the keystroke will click through the next dialog box which I don't want to have happen.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Can try with putting this code on the "Thisworkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved
End sub

Author

Commented:
Another way to do this might be to only give the ActiveWorkbook.Close savechanges:=True 1 second to execute. If it takes longer that means the dialog box is open and the keystroke needs to be sent. if it finishes in under 1 sec, no keystroke is sent.

Maybe  do it with a time reference? Set a variable that is now and if the next line takes longer than Now + 1 second, send the keystroke? No idea if that is feasible.

Grasping at straws here :-)
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
is it possible to upload a dummy file with the complete code. so that i look where the problem is?

i feel like i did not understand the question/problem.  can you elabore and if possible upload the screenshot?

from what i see that you are trying to save multiple workbooks and if one of them get stuck then you cancel and move to another correct?

if that is the case, instead of activating workbook and saving it, you could use something simpler like this

Sub SaveAllwbks()
    Dim Wkb As Workbook
    For Each Wkb In Workbooks
        If Not Wkb.ReadOnly And Windows(Wkb.Name).Visible Then
            Wkb.Save
        End If
    Next
End Sub

Open in new window

Author

Commented:
Hi Professorjimjam,

I am opening just one file on a server, making an entry, then saving it with changes.

Sometimes that works with no issues. Sometimes it does not and a dialog box appears saying the file is uploading to the server. When that occurs, I want to autoclick the "Cancel" button.

Thanks,
Microsoft Excel Expert
Top Expert 2014
Commented:
ok

can you try now with this code and it has to work

Sub test()
Windows("MyFile.xlsx").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
MsgBox ("Report Complete")
Application.Quit
ThisWorkbook.Windows(1).Visible = True
ThisWorkbook.Save
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
My apologies. Nothing worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial