VBA Error Handling when trying to open Workbook

Question

I am trying to loop through all files in a folder, open them and remove document info. I am having trouble dealing with files that cannot be opened or when opened have a pop us regarding disabling macros. I tried to solve this using on error resume next and on error goto 0. But then I get a runtime failure because my workbook object (wb) has not been set when I was trying to close files that did open.

I have read the documentation on "On Error Resume Next" & "On error goto 0" but I do not believe I am using them correctly here. Any help is greatly appreciated, Thanks.

Option Explicit
Sub test_Scrubber_New()

Dim directory As String, fileName As String, i As Variant, wb As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'directory = "C:\Users\bayli\Desktop\Files for Testing\"
directory = "C:\Users\bayli\Desktop\excel files\"
fileName = Dir(directory & "*.xl??")

i = 0
Do While fileName <> ""
    On Error Resume Next
    Set wb = Workbooks.Open(directory & fileName)
    On Error GoTo 0
        'remove info
        ActiveWorkbook.RemoveDocumentInformation (xlRDIAll)
    wb.Close True
    i = i + 1
    fileName = Dir()
    Application.StatusBar = "Files Completed:  " & i
Loop

Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Complete"

End Sub

I updated my code to include: If Not wb Is Nothing Then remove the info as @PatricK suggested and it is working however it keeps stopping with a pop up about updating links. If I click "Do not update" my code continues working as needed but is there a way to handle this problem. I am looping through over 5k files so as you can imagine it is taking a while. The time it is taking is not a problem but currently I am sitting here having to click "dont update" quite a few times. I thought Application.DisplayAlerts = False would prevent these pop ups however it is not.

Answer 1

OK, so there are a couple questions here. First, regarding the error handling. When you're using inline error handling (On Error Resume Next), the basic pattern is to turn off the automatic error handling, run the line of code that you want to "catch" the error for, then test to see if the Err.Number is zero:

On Error Resume Next
ProcedureThatCanError
If Err.Number <> 0 Then
    'handle it.
End If
On Error GoTo 0

---

The rest of the questions deal with dialogs you can encounter when you're opening workbooks. Most of this is documented on the MSDN page for Workbook.Open, but you'll want to change the Application.AutomationSecurity property to deal with the macro prompts as appropriate. For the updates, you should pass the appropriate UpdateLinks parameter. I'd also recommend specifying IgnoreReadOnlyRecommended, Notify, and CorruptLoad. Something like this should work (untested), or at least get you a lot closer:

---

Sub TestScrubberNew() 'Underscores should be avoided in method names.

    Dim directory As String, fileName As String, i As Variant, wb As Workbook
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim security As MsoAutomationSecurity
    security = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable

    directory = "C:\Users\bayli\Desktop\excel files\"
    fileName = Dir(directory & "*.xl??")

    i = 0
    Do While fileName <> vbNullString
        On Error Resume Next
        Set wb = Workbooks.Open(fileName:=directory & fileName, _
                                UpdateLinks:=0, _
                                IgnoreReadOnlyRecommended:=True, _
                                Notify:=False, _
                                CorruptLoad:=xlNormalLoad)
        If Err.Number = 0 And Not wb Is Nothing Then
            On Error GoTo 0
            wb.RemoveDocumentInformation xlRDIAll
            wb.Close True
            i = i + 1
            Application.StatusBar = "Files Completed:  " & i
            fileName = Dir()
        Else
            Err.Clear
            On Error GoTo 0
            'Handle (maybe log?) file that didn't open.
        End If
    Loop

    Application.AutomationSecurity = security
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Complete"

End Sub