Need to access non-native menu commands in VBA

[jkupfer]

Hi All,

I have an interesting problem I have not been able to find any references to anywhere, so here I am....

I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.

Anyway, I hope I've explained that clearly enough. I'm just trying to figure out how I can call the command for a non-native menu bar item. You can't record a macro to access it. Surely there is some way. Any other ideas?

Thanks in advance.

[rylo]

Hi

Does this thing add an addin, or a reference in the VBE?

rylo

[jkupfer]

No, neither unfortunately.

[shg]

Andy Pope recently posted this code to list all command bar controls:

Please Login or Register  to view this content.

Once you find it in the list, you can execute it. For example, type this in the Immediate window:

Please Login or Register  to view this content.

[jkupfer]

Great idea, and very close, but no cigar.

Here are the first few lines of what I get. #1 Sterling Trader Pro is actually the name of the menu bar that has the refresh command I need to access, but the actual command (nor the other commands in that menu bar) are listed anywhere in the list generated by that macro.

Please Login or Register  to view this content.

[shg]

No further ideas, sorry.

Have you contacted Sterling?

[jkupfer]

I have, but long story short they would much rather not go down the road of giving out that code. They have another two solutions, but one is almost worse than the problem and the other we have not gotten working yet.

Thanks for trying.

[shg]

I'll ask Andy to stop by this thread,

[Andy Pope]

You will need to try and locate the ID value.

so for example this will list the sub menu items of the Help menu

Please Login or Register  to view this content.

You will need to replace "&Help" with "Sterling Trader Pro"

[jkupfer]

Andy (& Others),

Thanks for the help so far. I modified your code a bit and here is what I got:
Quote Server... 1
Position Server... 1
Recalc Symbol 1
Recalc Symbol/Account 1
Settings... 1
Refresh Links 1
About... 1

Since all the ID values are "1", can I execute "Refresh Links" by calling the name instead of the ID? Or what about calling the 6th item in the list?

Here is the code I used:

Please Login or Register  to view this content.

[Leith Ross]

Hello jkupfer,

If the program is using DDE then the menu was probably added using menu API calls. This is separate from the Office CommandBar menu and does not expose any properties, methods, or objects through VBA. It would require using API calls to retrieve the information from the menu. Once the information is retrieved, executing the refresh command will probably require more API calls.

Sincerely,
Leith Ross

[Andy Pope]

If it's going to work this should do it.

Please Login or Register  to view this content.

But Leith's comments would suggest that even knowing the reference will not be enough.