PDA

View Full Version : [SOLVED] Error Handling



GMan
09-28-2006, 09:05 AM
OK, just as I think I am getting good and projects start working like I think they should. I find the next wall. :banghead:

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? :dunno

stanl
09-28-2006, 09:20 AM
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
09-28-2006, 09:39 AM
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
09-28-2006, 11:40 AM
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
09-28-2006, 02:25 PM
OK, just as I think I am getting good and projects start working like I think they should. I find the next wall. :banghead:

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? :dunnoAs to this question: Replace the GoTo SkipFile with Resume SkipFile

GMan
09-29-2006, 05:44 AM
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! :beerchug: