PDA

View Full Version : Workbooks.Open Does not Error if Workbook is not Present



YellowLabPro
09-21-2007, 09:58 AM
In my block of code below, if the Workbook is not present in the directory, I would have expected it to error. But it does not, it continues to process it.
I have put a check into the block of code to see if the file is present. But I think there are few things wrong w/ my approach.
How can I put a Debug.Print in there to see if there is an error ocurring and where would I place this to see?

Original code:

err.Clear
On Error Resume Next
Set wsSource = Workbooks(wbn).Sheets(wsn)
iStatus = err
If iStatus Then
Workbooks.Open wPath & wbn
On Error GoTo 0
Sheets(wsn).Activate
Else
Workbooks(wbn).Sheets(wsn).Activate
End If


New Code w/ error checking:

err.Clear
On Error Resume Next
Set wsSource = Workbooks(wbn).Sheets(wsn)
If IsNull(wPath & wbn) Then
MsgBox wbn & "Is Not Present in the Directory" + vbCritical
Exit Sub
End If
iStatus = err
If iStatus Then
Workbooks.Open wPath & wbn
On Error GoTo 0
Sheets(wsn).Activate
Else
Workbooks(wbn).Sheets(wsn).Activate
End If

Norie
09-21-2007, 10:01 AM
Why would you expect that when you are telling the code to ignore errors here?

On Error Resume Next

By the way I don't actually see how that 2nd block of code checks if a file exists.

All it appears to do is check if a string is null.

To see if a file exists use Dir.

YellowLabPro
09-21-2007, 10:06 AM
Norie,
I guess because I was thinking that it might not actually be an error, but rather it could not find something to process... which I guess an error in thought.
I will look up Dir for its intended use and example,
thanks

rory
09-22-2007, 05:57 AM
Doug,
As a matter of interest, why are you using On Error Resume Next there? IMO, that's a bad idea in most circumstances - you should check and handle the errors or build in code to avoid them altogether (Or at most, have it on for one line, then reset error handling)

YellowLabPro
09-22-2007, 10:43 AM
Thanks Rory,
How would you handle it then? I am curious to improve on this....

Norie
09-22-2007, 10:47 AM
Doug

I think what rory is suggesting is using something like Dir to verify the file exists.

As far as I'm concerned using something like On Error Resume Next when doing something like this isn't particularly good practice.

Not that I'm saying it doesn't have it's uses and is in fact sometimes the only solution in some circumstances.

rory
09-22-2007, 12:28 PM
The problem, to my mind, of using it as you are, is that you don't know if the error is because the workbook is not open, or because the sheet does not exist in the workbook. If you use generic functions to check both, you don't need the error trapping:
Function IsWorkbookOpen(strWbkName As String) As Boolean
Dim wbk As Workbook
IsWorkbookOpen = False
For Each wbk In Application.Workbooks
If StrComp(strWbkName, wbk.Name, vbTextCompare) = 0 Then
IsWorkbookOpen = True
Exit For
End If
Next wbk
End Function
Function IsSheetInWorkbook(ByVal wbk As Workbook, strSheet As String) As Boolean
Dim wks As Object
IsSheetInWorkbook = False
For Each wks In wbk.Sheets
If StrComp(strSheet, wks.Name, vbTextCompare) = 0 Then
IsSheetInWorkbook = True
Exit For
End If
Next wks
End Function