Error Handling

[GMan]

OK, just as I think I am getting good and projects start working like I think they should. I find the next wall.

I have the following code:

On Error GoTo ErrorHandler
Workbooks.Open Filename:=stDirectory & Format(stRptDate, "yyyymmdd") & ".csv"

When the file is not found goes to the ErrorHandler:

ErrorHandler:
' Check for error, then show message.
If Err.Number = 1004 And stRptDate = stDate Then
    msg = "Error: The File can not be opened. " & _
    vbNewLine & " - Click OK to Continue to the next File" & vbNewLine & _
    " - Click CANCEL to Stop Processing"
    msg = MsgBox(msg, vbOKCancel, "ERROR:")
    If msg = vbOK Then
        Err.Clear
        GoTo SkipFile
    End If
Else
    msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox msg, , "ERROR:"
End If

The idea is if the file is not found. You can continue the process (skipping the file). This works great the first time. But after that I just get the debugging popup box and is stops on the OPEN statement.

How do I make the ON ERROR command work for more than 1 error in the execution?

[stanl]

In the particular situation you outlined, you might consider a more preventative approach. Using a simple function like below allows you prevent going to an error handler in the first place, so you could code like

If FileExists(fname) Then processfile
Else
Skipfile
End If

.02 Stan

Private Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function

[GMan]

Thanks for the suggestion Stan. I have done just that in previous projects. However, the file is not being opened from a local or network drive. The file is actually being opened from a web page. I guess I should have mentioned that in the original post.

The variable stDirectory has something along the lines of http://utilreports/Daily. So the full path name of the file would be http://utilreports/Daily20060927.csv.

If I use the DIR command I get an invalid file name.

[stanl]

The variable stDirectory has something along the lines of http://utilreports/Daily. So the full path name of the file would be http://utilreports/Daily20060927.csv.

If I use the DIR command I get an invalid file name.

For web files, you can cook something up with the Shell, or the FSO.

;pseudo-code
url = "http://utilreports/Daily"
file = "Daily20060827.csv"
set objShell = CreateObject("Shell.Application")
set objFolder = objShell.NameSpace(url)
        if (not objFolder is nothing) then
            dim objFolderItem
 
            set objFolderItem = objFolder.ParseName(file)
 
            if (not objFolderItem is nothing) then
                'process file here
            end if
        end if

[johnske]

OK, just as I think I am getting good and projects start working like I think they should. I find the next wall.

I have the following code:

On Error GoTo ErrorHandler
Workbooks.Open Filename:=stDirectory & Format(stRptDate, "yyyymmdd") & ".csv"

When the file is not found goes to the ErrorHandler:

ErrorHandler:
' Check for error, then show message.
If Err.Number = 1004 And stRptDate = stDate Then
    msg = "Error: The File can not be opened. " & _
    vbNewLine & " - Click OK to Continue to the next File" & vbNewLine & _
    " - Click CANCEL to Stop Processing"
    msg = MsgBox(msg, vbOKCancel, "ERROR:")
    If msg = vbOK Then
        Err.Clear
        GoTo SkipFile
    End If
Else
    msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
   MsgBox msg, , "ERROR:"
End If

The idea is if the file is not found. You can continue the process (skipping the file). This works great the first time. But after that I just get the debugging popup box and is stops on the OPEN statement.

How do I make the ON ERROR command work for more than 1 error in the execution?

As to this question: Replace the GoTo SkipFile with Resume SkipFile

[GMan]

Thanks Stan for the reply. I am going to work on this today and hope to have it resolved.

Also, thank you John. I will certainly remember this for future projects. I guess that problem with being self taught in VBA strickly by trial and error means I miss some easy things from time to time.

Don't know what I would do without my friends from VBAExpress!