Withing my macro I am trying to check, if another workbook contains specific sheet without opening the workbook.
I was following these two articles:
- Visual Basic, Check if a sheet exists in another workbook
- Read information from a closed workbook using VBA in Microsoft Excel 2010
The thing is, that the part ExecuteExcel4Macro (arg) should throw (in my case) Error 2023 (General evaluation error) I was trying to test it out, if it works properly. I was faking sheet names that are not present within this workbook.
Example: DataWorkbook contains 2 sheets: InputData and Notes MacroWorkbook contains this following macro
Private Function checkSheet(ByVal path As String, _
ByVal fileName As String, _
ByVal sheetName As Strgin) As Boolean
Dim arg = "'" & path & "[" & fileName & "]" & _
sheetName & "'!" & Range("A1").Address(True, True, xlR1C1)
ExecuteExcel4Macro(arg)
If Err = 0
checkSheets = True
Else
Err.Raise 1
End If
End Function
I call the function with fake sheetname (let's say DataFake as sheetName). Then when I break the execution before ExecuteExcel4Macro(arg) and within immediate window I call ExecuteExcel4Macro(arg) it gives me Error 2023 (that's right because there is no such sheet called DataFake) but I run macro again (I assume Error 2023 was raised) my second part of function should raise new Error with Number 1 (this error is handeled afterwards). Problem is, that it does not end up in Else part. For some reason Err = 0 (I also tried Err.Number = 0) both with same result. It says there is no error even if it should shout there is an error.
Any ideas?
