Error Loading Add-In

[scott]

I have an add-in loaded all the time that checks any opened workbook name
for a specific string. The below code does this except when excel is opened
with no workbook (i.e. from Programs Menu). This code resides in the
App_WorkbookOpen event in the add-in.

How can I suppress the error generated when no workbook is opened when excel
opens and is loading my add-in with below code? The
Application.ActiveWorkbook.Name object is null and that's causing the error.

If InStr("History by Length", Application.ActiveWorkbook.Name) > 0 Then
MsgBox "it is our file"
Call CreateMenu
End If

 

[scott]

actually i've discovered my code in my previous post is failing even when i
open a workbook.

error is

Object variable or With block variable not set

what's wrong with that syntax?

 

[Josh Sale]

How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh

 

[scott]

If I open a file named "History by Length.xls", shouldn't my below code
fire?

If InStr("History by Length", Application.ActiveWorkbook.Name) > 0
Then
MsgBox "it is our file"
Call CreateMenu
End If

 

[Josh Sale]

I'm just trying to help you avoid the error you said you were getting ... I
haven't followed the whole thread.

 

[scott]

If my workbook is named "My Book.xls" why doesn't the INSTR function work
below?

Dim bk As Workbook

For Each bk In Application.Workbooks

If InStr("My Book", bk.Name) > 0 Then
MsgBox "it is our file"
End If

Next

 

[Guest]

Scott
I think it may be an inconsistency depending on whether the book name has
any extended chars in it (like space) or not.
this seems to work through (adding the .xls)
Sub test()

Dim bk As Workbook

For Each bk In Application.Workbooks
Debug.Print bk.Name
If InStr("book test.xls", bk.Name) > 0 Then
MsgBox "it is our file"
End If

Next

End Sub


cheers
Simon

 

[Guest]

Scott
I think it may be an inconsistency depending on whether the book name has
any extended chars in it (like space) or not.
this seems to work through (adding the .xls)
Sub test()

Dim bk As Workbook

For Each bk In Application.Workbooks
Debug.Print bk.Name
If InStr("book test.xls", bk.Name) > 0 Then
MsgBox "it is our file"
End If

Next

End Sub


cheers
Simon

 

[scott]

The below code works because I use the whole file name, but won't work if I
just use "Length" or any other part. Why won't INSTR find a word within a
bk.Name?



My problem is that parts of the file name may vary from month to month. Any
idea why INSTR acts this way?



CODE:



If InStr("FIN039 Product History by Length.xls", bk.Name) > 0 Then

MsgBox "it is our file"

End If

 

[Tom Ogilvy]

Arguments to instr are probably backwards and you should make it case
insensitive.