Problem with Application.DisplayAlerts = False

[Gerald Higgins]

Thanks in advance for any help !

So I've got a master file, and am writing some code to open individual files one at a time for each of a number of other company locations, suck data from them into the master file, do some other changes to the individual location files, close each of them down and move on to the next location.

This basically works fine.

I now want to put some code in that handles errors, such as what to do if one of the individual location files can't be found.

I want to give the user the option to either bypass that one location and move on to the next location, OR terminate the whole process.

I'm showing a message box in the event that there is a problem opening the file, to give that option to the user.

For some reason this works fine for the first location, but not the second location.

Am I missing something ?

The code is essentially like this - the section in red bold is where the error appears.

'***** LOCATION 1 *****
On Error GoTo Location1BypassCheck
GoTo Location1DataPull

Location1BypassCheck:

YesNo = MsgBox("Unable to open LOCATION1 resource file. Do you want to bypass LOCATION1 and continue with other locations ?", vbYesNo)

If YesNo = vbYes Then

DepotFails = "LOCATION1 "

GoTo LOCATION2

Else

GoTo InvalidWeekNo

End If

Location1DataPull:

Application.DisplayAlerts = False

Workbooks.Open(Filename:= _
'***** omitted full directory path for the purposes of this post *****
LOCATION1 resource template.xlsm" _
).RunAutoMacros Which:=xlAutoOpen
ActiveSheet.Unprotect "PASSWORD"

Application.DisplayAlerts = True

'***** omitted code for doing stuff in the LOCATION1 file, for clarity in this post *****

GoTo LOCATION2

LOCATION2:

On Error GoTo Location2BypassCheck

GoTo Location2DataPull

Location2BypassCheck:

YesNo = MsgBox("Unable to open LOCATION2 resource file. Do you want to bypass LOCATION2 and continue with other locations ?", vbYesNo)
If YesNo = vbYes Then
DepotFails = DepotFails & "LOCATION2 "
GoTo LOCATION3
Else
GoTo InvalidWeekNo
End If

LOCATION3DataPull:

Application.DisplayAlerts = False

Workbooks.Open(Filename:= _
'***** omitted full directory path for the purposes of this post *****
LOCATION2 resource template.xlsm" _
).RunAutoMacros Which:=xlAutoOpen
ActiveSheet.Unprotect "PASSWORD"

Application.DisplayAlerts = True

Both the files for LOCATION1 and LOCATION2 do NOT exist when I run this test.

For location 1, no error message is shown, except for my user dialog box which correctly gives the option to bypass location 1, or terminate the process.
I then choose the option YES to bypass and move on to location 2.

HOWEVER, for location 2, it seems to ignore the Application.DisplayAlerts = False instruction, doesn't show the dialog box for option 2, and instead gives me a "Run-time error 1004" message saying the file for location 2 cannot be accessed.

I've checked what the status of the Application.DisplayAlerts setting is, and it appears to correctly be FALSE just before it errors out.

Am I missing something ?

How do I get it to suppress the "Run-time error 1004" message for location 2, and instead give me the dialog box for location 2, the same as it does correctly for location 1 ?

Thanks again in advance for any help !

 

[Gerald Higgins]

Sorry, there was a small typo in the code in post #1 .

Where it says
LOCATION3DataPull:

This should read

LOCATION2DataPull:

 

[RoryA]

Application.DisplayAlerts does not suppress error messages, only regular application dialogs. You should test for the existence of the file using something like Dir and then only open it if it exists.

 

[Gerald Higgins]

Thanks for the reply RoryA.

In that case why does Application.DisplayAlerts appear to suppress the error message for the first file, and go into the message box routine (which is exactly what I want it to do) ?

If I wanted to test for the existence of the file using something like Dir, how exactly would I do that ?

I should say, I want to test not only for the existence of the file, but also whether it is in use by another user, in which case treat the file as inaccessible.

 

[RoryA]

Dir won't check if it's in use.

Your error handler is what is suppressing the error message, not Application.Displayalerts. The reason it only works the first time is because you didn't reset the error condition with a Resume statement - see here: http://excelmatters.com/2015/03/17/on-error-wtf/

Looking at all the jumping around your code is doing (which is bad structure, IMO) you should refactor the code that opens the workbooks, runs the automacros and then changes protection into a separate routine with its own error handler and then call that for each workbook you need rather than using all those Goto statements.

 

[Gerald Higgins]

Thanks for the link RoryA, that's helpful !

I've tried inserting a RESUME statement at various points, but that doesn't work, it just keeps returning me to the dialog box for Location1.

I've also tried inserting a ON ERROR GOTO 0 statement at various points, but that doesn't seem to make any difference at all to anything.

I acknowledge your comment about the jumping around being a bad thing - I'm sure you're right, I just don't (currently!) have the skills to write slicker code

 

[RoryA]

On Error Goto 0 doesn't clear an active error.

I'd suggest you add something like this:

Function OpenOtherWorkbook(sFilepath As String) As Workbook
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks.Open(Filename:=sFilepath)
On Error GoTo 0
If Not wb Is nothinng Then
wb.RunAutoMacros Which:=xlAutoOpen
wb.ActiveSheet.Unprotect "PASSWORD"
Set OpenOtherWorkbook = wb
End If
End Function
[/code]

then you can call that for each location as required:

Dim wb As Workbook
'***** LOCATION 1 *****
Set wb = OpenOtherWorkbook("LOCATION1 resource template.xlsm")
If wb Is Nothing Then


    YesNo = MsgBox("Unable to open LOCATION1 resource file. Do you want to bypass LOCATION1 and continue with other locations ?", vbYesNo)
    
    If YesNo = vbYes Then
    
        DepotFails = "LOCATION1 "
        '***** LOCATION 2 *****
        Set wb = OpenOtherWorkbook("LOCATION2 resource template.xlsm")
        If wb Is Nothing Then
            YesNo = MsgBox("Unable to open LOCATION2 resource file. Do you want to bypass LOCATION2 and continue with other locations ?", vbYesNo)
            If YesNo = vbYes Then
                DepotFails = DepotFails & "LOCATION2 "
                '***** LOCATION 3 *****
                Set wb = OpenOtherWorkbook("LOCATION3 resource template.xlsm")
            End If

        End If
        
    End If
End If

This could probably be tidied up in the context of your full code, but hopefully gives you an idea.

 

[Gerald Higgins]

OK thanks very much, I'll go away and try that. Will probably take a while for me to test it but thanks in the meantime !

 

[Gerald Higgins]

RoryA - just to say, thanks again for your assistance.

I seem to have got this working, through a combination of Resume statements and hearty portions of spaghetti.

Longer term, I need to learn about writing my code much more cleanly, as you suggested, but in the short term, my solution is dirty but it seems to work.

Thanks again !