Re: My .xla custom menu items doesn't show up when using 3rd party software

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" <mmalinsky@xxxxxxxxx> wrote in message
news:1160682961.791404.119720@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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("Work*** 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
news:ekVZJid7GHA.2248@xxxxxxxxxxxxxxxxxxxxxxx
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" <mmalinsky@xxxxxxxxx> wrote in message
news:1160596048.456821.192110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebr...@xxxxxxxxxxxx> 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" <mmalin...@xxxxxxxxx> wrote in
messagenews:1160518928.741819.308310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
@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" <rondebr...@xxxxxxxxxxxx>
wrote:
hi Michael

If you run Excel from another program code in Add-ins will not
run
(you see the add-ins checked in Tools>Add-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" <mmalin...@xxxxxxxxx> wrote in
messagenews:1160511426.655329.88050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 Tools>Add-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.






.