Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

Hi Michael,

Try some simple code (like opening a message box) on the Auto_open code to
see if it is running correctly.

I remember we had the same problem and it was overcome by unloading and
loading the bar in VBA.

Thanks,

Simon



Michael Malinsky wrote:
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Michael Malinsky" wrote in message ups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

Dim xlApp As Application
Dim mwb As Workbook

Sub testStart()
Dim sName As String
sName = "C:\path\myAddin.xla"
Set xlApp = New Excel.Application
Set mwb = xlApp.Workbooks.Open(sName)
mwb.RunAutoMacros xlAutoOpen
xlApp.Workbooks.Add
xlApp.Visible = True

End Sub

Sub testQuit()
On Error Resume Next
mwb.RunAutoMacros xlAutoClose
Set mwb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

If using Late Binding change xlAutoOpen & xlAutoClose to their respective
constant values of 1 and 2

Regards,
Peter T

"Michael Malinsky" wrote in message
ups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

If you create the code you can use this for example to open Excel with add-ins
from another program.

But you use a 3rd party application.


This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Michael Malinsky" wrote in message ups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

On Oct 11, 12:42 pm, "Ron de Bruin" wrote:
If you create the code you can use this for example to open Excel with add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in oglegroups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing problems.
If I open a workbook through that software, the menu that is supposed
to be created with my add-in does not appear even though the add-in is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in, close
the Add-in dialog, open the Add-in dialog, check my add-in, then close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office, but
this is a sticking point for which I cannot seem to find a solution.

Thanks,
Mike.

When you create a new automated instance of Excel installed Addins don't
load, also if you add (ie load) any workbooks to the automated instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open routine

The routines differ in the way the new instance was created, mine used early
binding in as much as it was designed for testing in an already running
instance of Excel, with a module level reference attached to the instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up 'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name < .Fullname

For your purposes start your instance however and from where ever you intend
to, then decide if you want to open only your own addin or all addins, if
the latter handle possible errors as described above.

Regards,
Peter T

"Michael Malinsky" wrote in message
oups.com...
Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

On Oct 11, 12:42 pm, "Ron de Bruin" wrote:
If you create the code you can use this for example to open Excel with
add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu
item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with Excel
(essentially document management software) that is causing
problems.
If I open a workbook through that software, the menu that is
supposed
to be created with my add-in does not appear even though the add-in
is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in,
close
the Add-in dialog, open the Add-in dialog, check my add-in, then
close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office,
but
this is a sticking point for which I cannot seem to find a
solution.

Thanks,
Mike.

From Auto_Open should add a MyMacro button to the Tools menu and optionally
delete any old MyMacro that wasn't deleted in a previous Auto_Close (if
indeed you want to delete menu on close). It also adds a version id to the
button's tag property.

Sub Auto_Open()
MyMenu True, 123
End Sub

Sub Auto_Close()
MyMenu False
End Sub

Sub MyMenu(bCreate As Boolean, Optional ver)
Dim cbcTools As CommandBarControl
Dim cbcNew As CommandBarControl

On Error Resume Next
'set a ref to Tools
Set cbcTools = _
Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=30007)

'delete any/all MyMacro buttons
Do
cbcTools.Controls("MyMacro").Delete
Loop Until Err.Number

On Error GoTo 0

If bCreate Then
' you might not want temporary:=True
Set cbcNew = cbcTools.Controls.Add(Type:=1, temporary:=True)
cbcNew.Caption = "&MyMacro"
cbcNew.OnAction = "MyMacro"
cbcNew.Tag = "MyVer" & ver
End If

End Sub

Sub MyMacro()

MsgBox "MyMacro"
End Sub

Following to find & delete any menus with tag MyVer123 and replace with same
(only for testing of course)

Sub CheckMenu()
Dim bFoundCtrl As Boolean
Dim sTag As String
Dim cbc As CommandBarControl

sTag = "MyVer" & 123
On Error Resume Next
Do
Set cbc = Application.CommandBars.FindControl(Tag:=sTag)
If Not cbc Is Nothing Then bFoundCtrl = True
cbc.Delete
Loop Until Err.Number

If bFoundCtrl Then
MsgBox sTag & " found, will now re-create"
MyMenu True, 123
Else
MsgBox sTag & " not found, will now create new"
MyMenu True, True, 123
End If

End Sub

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
When you create a new automated instance of Excel installed Addins don't
load, also if you add (ie load) any workbooks to the automated instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open
routine

The routines differ in the way the new instance was created, mine used
early
binding in as much as it was designed for testing in an already running
instance of Excel, with a module level reference attached to the instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up 'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name < .Fullname

For your purposes start your instance however and from where ever you
intend
to, then decide if you want to open only your own addin or all addins, if
the latter handle possible errors as described above.

Regards,
Peter T

"Michael Malinsky" wrote in message
oups.com...
Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

On Oct 11, 12:42 pm, "Ron de Bruin" wrote:
If you create the code you can use this for example to open Excel with
add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu
item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with
Excel
(essentially document management software) that is causing
problems.
If I open a workbook through that software, the menu that is
supposed
to be created with my add-in does not appear even though the
add-in
is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in,
close
the Add-in dialog, open the Add-in dialog, check my add-in, then
close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office,
but
this is a sticking point for which I cannot seem to find a
solution.

Thanks,
Mike.

Thanks, Peter. After monkeying around with it in my existing add-in
with no success, I copied your code exactly as posted into a new xla
and got it to work. Now I just need to modify the code to make a new
menu item and populate it with menu items and cut and paste some code
from my original add-in.

Thanks for the help!

Peter T wrote:
From Auto_Open should add a MyMacro button to the Tools menu and optionally
delete any old MyMacro that wasn't deleted in a previous Auto_Close (if
indeed you want to delete menu on close). It also adds a version id to the
button's tag property.

Sub Auto_Open()
MyMenu True, 123
End Sub

Sub Auto_Close()
MyMenu False
End Sub

Sub MyMenu(bCreate As Boolean, Optional ver)
Dim cbcTools As CommandBarControl
Dim cbcNew As CommandBarControl

On Error Resume Next
'set a ref to Tools
Set cbcTools = _
Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=30007)

'delete any/all MyMacro buttons
Do
cbcTools.Controls("MyMacro").Delete
Loop Until Err.Number

On Error GoTo 0

If bCreate Then
' you might not want temporary:=True
Set cbcNew = cbcTools.Controls.Add(Type:=1, temporary:=True)
cbcNew.Caption = "&MyMacro"
cbcNew.OnAction = "MyMacro"
cbcNew.Tag = "MyVer" & ver
End If

End Sub

Sub MyMacro()

MsgBox "MyMacro"
End Sub

Following to find & delete any menus with tag MyVer123 and replace with same
(only for testing of course)

Sub CheckMenu()
Dim bFoundCtrl As Boolean
Dim sTag As String
Dim cbc As CommandBarControl

sTag = "MyVer" & 123
On Error Resume Next
Do
Set cbc = Application.CommandBars.FindControl(Tag:=sTag)
If Not cbc Is Nothing Then bFoundCtrl = True
cbc.Delete
Loop Until Err.Number

If bFoundCtrl Then
MsgBox sTag & " found, will now re-create"
MyMenu True, 123
Else
MsgBox sTag & " not found, will now create new"
MyMenu True, True, 123
End If

End Sub

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
When you create a new automated instance of Excel installed Addins don't
load, also if you add (ie load) any workbooks to the automated instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open
routine

The routines differ in the way the new instance was created, mine used
early
binding in as much as it was designed for testing in an already running
instance of Excel, with a module level reference attached to the instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up 'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name < .Fullname

For your purposes start your instance however and from where ever you
intend
to, then decide if you want to open only your own addin or all addins, if
the latter handle possible errors as described above.

Regards,
Peter T

"Michael Malinsky" wrote in message
oups.com...
Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

On Oct 11, 12:42 pm, "Ron de Bruin" wrote:
If you create the code you can use this for example to open Excel with
add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin" wrote:
hi Michael

If you run Excel from another program code in Add-ins will not run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom menu
item
on the standard menu bar. This works fine when opening Excel.

However, we use a 3rd party application that integrates with
Excel
(essentially document management software) that is causing
problems.
If I open a workbook through that software, the menu that is
supposed
to be created with my add-in does not appear even though the
add-in
is
checked in the ToolsAdd-ins dialog. If a uncheck and add-in,
close
the Add-in dialog, open the Add-in dialog, check my add-in, then
close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my office,
but
this is a sticking point for which I cannot seem to find a
solution.

Thanks,
Mike.

Hi Michael,

Er, looks like I posted a follow-up about creating menus intended for a
totally different thread after my comments about automated instances of
Excel & RunAutoMacros. I take it you are responding to that and not the
accidentally posted menu stuff. Either way glad one or other of those posts
has worked for you!

Guess I'd better re-post the menu stuff where it belongs (subject: Add-In
Keyboard Shortcut).

Regards,
Peter T

"Michael Malinsky" wrote in message
oups.com...
Thanks, Peter. After monkeying around with it in my existing add-in
with no success, I copied your code exactly as posted into a new xla
and got it to work. Now I just need to modify the code to make a new
menu item and populate it with menu items and cut and paste some code
from my original add-in.

Thanks for the help!

Peter T wrote:
From Auto_Open should add a MyMacro button to the Tools menu and
optionally
delete any old MyMacro that wasn't deleted in a previous Auto_Close (if
indeed you want to delete menu on close). It also adds a version id to
the
button's tag property.

Sub Auto_Open()
MyMenu True, 123
End Sub

Sub Auto_Close()
MyMenu False
End Sub

Sub MyMenu(bCreate As Boolean, Optional ver)
Dim cbcTools As CommandBarControl
Dim cbcNew As CommandBarControl

On Error Resume Next
'set a ref to Tools
Set cbcTools = _
Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=30007)

'delete any/all MyMacro buttons
Do
cbcTools.Controls("MyMacro").Delete
Loop Until Err.Number

On Error GoTo 0

If bCreate Then
' you might not want temporary:=True
Set cbcNew = cbcTools.Controls.Add(Type:=1, temporary:=True)
cbcNew.Caption = "&MyMacro"
cbcNew.OnAction = "MyMacro"
cbcNew.Tag = "MyVer" & ver
End If

End Sub

Sub MyMacro()

MsgBox "MyMacro"
End Sub

Following to find & delete any menus with tag MyVer123 and replace with
same
(only for testing of course)

Sub CheckMenu()
Dim bFoundCtrl As Boolean
Dim sTag As String
Dim cbc As CommandBarControl

sTag = "MyVer" & 123
On Error Resume Next
Do
Set cbc = Application.CommandBars.FindControl(Tag:=sTag)
If Not cbc Is Nothing Then bFoundCtrl = True
cbc.Delete
Loop Until Err.Number

If bFoundCtrl Then
MsgBox sTag & " found, will now re-create"
MyMenu True, 123
Else
MsgBox sTag & " not found, will now create new"
MyMenu True, True, 123
End If

End Sub

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
When you create a new automated instance of Excel installed Addins
don't
load, also if you add (ie load) any workbooks to the automated
instance
their 'auto macros' don't automatically run.

Both posted routines have in common
- create a new Excel instance
- load one or more addins and use RunAutoMacros to run the Auto_Open
routine

The routines differ in the way the new instance was created, mine used
early
binding in as much as it was designed for testing in an already
running
instance of Excel, with a module level reference attached to the
instance.

Dim xlApp As Application
though would be clearer to have declared as Excel.Application
Normally the instance should be closed using the testClose routine in
particular to destroy the object ref 'xlApp'. You say Excel locked up
'the
way you used it' but you didn't say how.

Ron's / KeepitCool's would be late binding if used outside Excel.

My routine only attempted to open only your own addin (none of any
other
installed addins). Providing you managed to start the new instance and
supply the correct path to your addin I don't know why that should
fail
(other than errant code in the auto open routine of your addin).

Ron's / KeepitCool's attempts to open all installed addins and on the
same
line run their auto open's. Possible reasons for the error you got
- an installed addin is an Excel bundled xll, eg Analys32.xll
- sometimes the bundled xla's don't return the full path with fullname

If you want to load all installed addins, and assuming your own addin
is
installed, either force through under
On Error Resume Next
or check the addin's extentension is "xla" and verify .Name <
..Fullname

For your purposes start your instance however and from where ever you
intend
to, then decide if you want to open only your own addin or all addins,
if
the latter handle possible errors as described above.

Regards,
Peter T

"Michael Malinsky" wrote in message
oups.com...
Well I couldn't get either of the above two ideas to work. Maybe I
don't understand placement of the code. Peter T.'s code, at least
how
I used it, caused Excel to lock up.

Ron, your code generated an error at the line:

xl.Workbooks.Open(ai.FullName).RunAutoMacros 1

The error was "Unable to get the Open Property of the Workbooks
class."

Maybe this has to do with where I put the code, which was in the
Auto_Open of the add-in.

What am I missing?

Thanks.

On Oct 11, 12:42 pm, "Ron de Bruin" wrote:
If you create the code you can use this for example to open Excel
with
add-ins
from another program.

But you use a 3rd party application.

This was suggested by KeepItCool and should work:

Code from KeepItCool (Oct 21, 2004)

Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
@smw226: I'll give that a try...thanks.

@Ron: Any workaround suggestions other than what was already
suggested?

Thanks.

On Oct 10, 4:45 pm, "Ron de Bruin"
wrote:
hi Michael

If you run Excel from another program code in Add-ins will not
run
(you see the add-ins checked in ToolsAdd-ins)

Run this in Start Run in Windows with Excel closed and you
see it

excel.exe /s

--
Regards Ron de Bruinhttp://www.rondebruin.nl

"Michael Malinsky" wrote in
oglegroups.com...
Long subject line...sorry.

Anyway, I have created an .xla add-in that creates a custom
menu
item
on the standard menu bar. This works fine when opening
Excel.

However, we use a 3rd party application that integrates with
Excel
(essentially document management software) that is causing
problems.
If I open a workbook through that software, the menu that is
supposed
to be created with my add-in does not appear even though the
add-in
is
checked in the ToolsAdd-ins dialog. If a uncheck and
add-in,
close
the Add-in dialog, open the Add-in dialog, check my add-in,
then
close
the Add-in dialog, the menu appears like it should.

I would like to distribute this add-in to everyone at my
office,
but
this is a sticking point for which I cannot seem to find a
solution.

Thanks,
Mike.