Kill Excel Dialog Box (if it exists)

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")
swjtx99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
Set this line to false

Application.DisplayAlerts = True
swjtx99Author 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 ExpertCommented:
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 SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

swjtx99Author 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 ExpertCommented:
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
swjtx99Author 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....".
swjtx99Author 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 ExpertCommented:
Can try with putting this code on the "Thisworkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved
End sub
swjtx99Author 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 ExpertCommented:
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

swjtx99Author 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,
Professor JMicrosoft Excel ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
swjtx99Author Commented:
My apologies. Nothing worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.