VBA - Error handling routine which stores filename of file which caused error

[Stepheny2k2]

Morning guys,

I have written a VBA procedure which loops through excel files in an array: in turn it opens the file, refreshes the data, saves the file and closes. I would like to write an error handler which stores the file which errored (copied from the loop iteration) and the error description to an array. The error handler should then close the file and continue with the next iteration - e.g.

If ActiveWorkbook.ReadOnly Then
     ActiveWorkbook.Close
Next i

*Although a generic if error may be better.

My idea is that all files which errored in some way will be stored in an array which I can automatically print/send to a user so they can manually figure out the problem. (This element falls outside the range of this query)

Does anyone have an idea how i can do this?

Below is a simple example of my efforts which only contains one file in the array to make things simpler: the error handler section won't work, it's just there to show my thinking

Sub Refresh_CRIS()
    On Error GoTo Errorhandler
Dim routepath As String
routepath = "[URL="file://\\nch\dfs\SharedArea\Private\Test"]\\nch\dfs\SharedArea\Private\Test[/URL]"
ChDir routepath
'
Dim CRISPiv As Variant 'Includes Dailies
Dim Failed As Variant
Dim i
                       
CRISPiv = Array("Waiting-List-Diagnostic.xls")
                        
For Each i In CRISPiv
    Workbooks.Open Filename:=routepath & i
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
    Application.DisplayAlerts = False
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    
Next i
Exit Sub

Errorhandler:
On Error Resume Next
Failed = Array(i)
If ActiveWorkbook.ReadOnly Then 'Just an example for testing
     ActiveWorkbook.Close
Else
MsgBox "Pivots which failed to refresh:" & Failed '& ", ", 0, "Debug" 'Test with msgbox
End If
End Sub

Using Excel 2010

 

[Stepheny2k2]

Slight edit to my code. Tried to add an index to my array (Failed) and altered my error handler to take account of the index. Getting closer I think.

Sub Refresh_CRIS()
    On Error GoTo Errorhandler
Dim routepath As String
routepath = "[URL="file://\\nch\dfs\SharedArea\Private\BIS\Groups\KPI-Group\Pivots\"]\\nch\dfs\SharedArea\Private\BIS\Groups\KPI-Group\Pivots\[/URL]"
ChDir routepath
'
Dim CRISPiv As Variant 'Includes Dailies
Dim Failed As Variant
Dim i
Dim FailedIndex As Integer
FailedIndex = 0
                       
CRISPiv = Array("Waiting-List-Diagnostic.xls")
                        
For Each i In CRISPiv
    Workbooks.Open Filename:=routepath & i
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
    Application.DisplayAlerts = False
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    
Next i
MsgBox "Pivots which failed to refresh:" & Failed '& ", ", 0, "Debug"
Exit Sub
Errorhandler:
On Error Resume Next
Failed(FailedIndex) = CRISPiv(i)
FailedIndex = FailedIndex + 1
End Sub

 

[Norie]

How are the files failing?

 

[Stepheny2k2]

Sorry Norie,

My array was called 'Failed'. You can see this in the code. I can see how this could confuse now.

Anyway,

I have made some progress on my code and I have got it to work if the error is that the file is already open. Each instance of an open file will be written to an array and (for the moment) outputted in a a msgbox.

Issue 1: I can't get it to work with other errors such as the error where ActiveWorkbook.RefreshAll would overwrite a cell.

The reason my 'File already open' code works is that it doesn't hit the error handler. Can anyone see why other errors are not caught? I specifically tested the overwrite cell error - the excel dialog box comes up asking if the user wants to overwrite or not but then it doesn't matter what option is chosen, the pop-up message "All Weekly Pivots refreshed" is displayed.

Issue 2: How do i get my error handler to go to the next iteration of i instead of just resuming next?

Issue 3: I want to disable the excel pop-up and just record the error in my VBA code instead. Application.DisplayAlerts = False doesn't disable the pop-up

Thanks in advance guys.

Function isFileOpen(ByVal fstr As String) As Boolean
    On Error Resume Next
    isFileOpen = Len(Workbooks(fstr).Name) > 0
End Function
Sub Refresh_CRIS()
    On Error GoTo Errorhandler
Dim routepath As String
routepath = "[URL="file://\\nch\dfs\SharedArea\Private\BIS\Groups\KPI-Group\Pivots\"]\\nch\dfs\SharedArea\Private\BIS\Groups\KPI-Group\Pivots\[/URL]"
ChDir routepath
'
Dim CRISPiv As Variant 'Includes Dailies
Dim Failed(0 To 100) As String
Dim i
Dim failedIndex As Integer
Dim errorText As String
Dim outputMsg As String
Dim x
failedIndex = 0
                       
CRISPiv = Array("Waiting-List-Diagnostic.xls", "SW-TESTING.xls")
                        
For Each i In CRISPiv
    If isFileOpen(i) = False Then
    Workbooks.Open Filename:=routepath & i
    ActiveWorkbook.RefreshAll
    Application.CalculateFull
    'Application.DisplayAlerts = False
    ActiveWorkbook.Save
    'Application.DisplayAlerts = True
    ActiveWorkbook.Close
    Else
    errorText = i
    Failed(failedIndex) = errorText
    failedIndex = failedIndex + 1
    End If
Next i
If failedIndex = 0 Then
    MsgBox "All Weekly Pivots refreshed"
Else
    
    For x = 0 To failedIndex - 1
    outputMsg = outputMsg + Failed(x) & ", "
    Next x
MsgBox "Pivots which failed to refresh:" & outputMsg
End If
Exit Sub
Errorhandler:
errorText = i
Failed(failedIndex) = errorText
failedIndex = failedIndex + 1
Resume Next
End Sub

 

[Norie]

I didn't actually see an array called Failed.

What I really meant was how is the code 'failing' for the files, is it for a particular reason? For example the file isn't found, or it's already open.

 

[Stepheny2k2]

It doesn't fail for already open, when this occurs it writes the filename to my 'Failed' array as designed.

If it can't find the file then a pop-up saying file cannot be found is displayed (issue 3 in my code above) and then once OK selected my code doesn't break so it saves and closes the ActiveWorkbook which ends up being the workbook running my code! Heh (This issue is Issue 2 in my reply above)

For other errors I have simulated it doesn't catch an error and just runs to the end

i've never written an error handler before so this is new ground for me