Error Loading Add-In

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

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?


"scott" <[email protected]> wrote in message
news:[email protected]...
>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
>

How about putting:

If Not ActiveWorkbook Is Nothing Then
...
End if

around your If InStr statement?

josh



"scott" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

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" <jsale@tril dot cod> wrote in message
news:uBX%[email protected]...
> How about putting:
>
> If Not ActiveWorkbook Is Nothing Then
> ...
> End if
>
> around your If InStr statement?
>
> josh
>
>
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>> "scott" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>>
>>
>>
>
>

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



"scott" <[email protected]> wrote in message
news:[email protected]...
> 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" <jsale@tril dot cod> wrote in message
> news:uBX%[email protected]...
>> How about putting:
>>
>> If Not ActiveWorkbook Is Nothing Then
>> ...
>> End if
>>
>> around your If InStr statement?
>>
>> josh
>>
>>
>>
>> "scott" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>>
>>>
>>> "scott" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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
>>>>
>>>
>>>
>>
>>
>
>

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



"Josh Sale" <jsale@tril dot cod> wrote in message
news:[email protected]...
> I'm just trying to help you avoid the error you said you were getting ...
> I haven't followed the whole thread.
>
>
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <jsale@tril dot cod> wrote in message
>> news:uBX%[email protected]...
>>> How about putting:
>>>
>>> If Not ActiveWorkbook Is Nothing Then
>>> ...
>>> End if
>>>
>>> around your If InStr statement?
>>>
>>> josh
>>>
>>>
>>>
>>> "scott" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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?
>>>>
>>>>
>>>> "scott" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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
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" wrote:

> 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
>
>
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:[email protected]...
> > I'm just trying to help you avoid the error you said you were getting ...
> > I haven't followed the whole thread.
> >
> >
> >
> > "scott" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <jsale@tril dot cod> wrote in message
> >> news:uBX%[email protected]...
> >>> How about putting:
> >>>
> >>> If Not ActiveWorkbook Is Nothing Then
> >>> ...
> >>> End if
> >>>
> >>> around your If InStr statement?
> >>>
> >>> josh
> >>>
> >>>
> >>>
> >>> "scott" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>> 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?
> >>>>
> >>>>
> >>>> "scott" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>>>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
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" wrote:

> 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
>
>
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:[email protected]...
> > I'm just trying to help you avoid the error you said you were getting ...
> > I haven't followed the whole thread.
> >
> >
> >
> > "scott" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <jsale@tril dot cod> wrote in message
> >> news:uBX%[email protected]...
> >>> How about putting:
> >>>
> >>> If Not ActiveWorkbook Is Nothing Then
> >>> ...
> >>> End if
> >>>
> >>> around your If InStr statement?
> >>>
> >>> josh
> >>>
> >>>
> >>>
> >>> "scott" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>> 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?
> >>>>
> >>>>
> >>>> "scott" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>>>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
> >>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>

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



"Simon Murphy" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>
>> "Josh Sale" <jsale@tril dot cod> wrote in message
>> news:[email protected]...
>> > I'm just trying to help you avoid the error you said you were getting
>> > ...
>> > I haven't followed the whole thread.
>> >
>> >
>> >
>> > "scott" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <jsale@tril dot cod> wrote in message
>> >> news:uBX%[email protected]...
>> >>> How about putting:
>> >>>
>> >>> If Not ActiveWorkbook Is Nothing Then
>> >>> ...
>> >>> End if
>> >>>
>> >>> around your If InStr statement?
>> >>>
>> >>> josh
>> >>>
>> >>>
>> >>>
>> >>> "scott" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>>> 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?
>> >>>>
>> >>>>
>> >>>> "scott" <[email protected]> wrote in message
>> >>>> news:[email protected]...
>> >>>>>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
>> >>>>>
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>>

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

--
Regards,
Tom Ogilvy

"scott" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> "Simon Murphy" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
> >> "Josh Sale" <jsale@tril dot cod> wrote in message
> >> news:[email protected]...
> >> > I'm just trying to help you avoid the error you said you were getting
> >> > ...
> >> > I haven't followed the whole thread.
> >> >
> >> >
> >> >
> >> > "scott" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <jsale@tril dot cod> wrote in message
> >> >> news:uBX%[email protected]...
> >> >>> How about putting:
> >> >>>
> >> >>> If Not ActiveWorkbook Is Nothing Then
> >> >>> ...
> >> >>> End if
> >> >>>
> >> >>> around your If InStr statement?
> >> >>>
> >> >>> josh
> >> >>>
> >> >>>
> >> >>>
> >> >>> "scott" <[email protected]> wrote in message
> >> >>> news:[email protected]...
> >> >>>> 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?
> >> >>>>
> >> >>>>
> >> >>>> "scott" <[email protected]> wrote in message
> >> >>>> news:[email protected]...
> >> >>>>>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
> >> >>>>>
> >> >>>>
> >> >>>>
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>