I'm using Visual Studio 2008 Profession and programming in Visual Basic. I have a 2003Excel Spreadsheet provided by a 3rd party with a menu item (one level down) that when clicked generates a bunch of cells with SQL script based on information on other cells.

I've written code that does some error checking, correction of errors, and insertion of missing items by comparing aprevious version with thecurrent version of the spreadsheet. After all that is done I would like to execute the 3rd party menu item from within my Visual Basic code. Is there a way to do this?

Hi Joe,

1. If you can record Marco in Excel when you click this menuitem, you should can execute this task in code.

2. This forum mainly discusses how to do upgrades of VB6 code to VB.NET. Covers questions on interop features, Interop Forms Toolkit, Upgrade wizard, but NOT for questions about VB6. It is best to discuss this Office Dev question in the following forum:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.officedev&lang=en&cr=US

---

Best regards,
Riquel
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Sorry, should have stated in the original that my attempt to record a macro while executing the task did not provide a VBA example--no keystrokes were recorded.

Hi Joe,

It is best to discuss this Office Dev question in the following forum:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.officedev&lang=en&cr=US

Also please have a look at this article.
http://support.microsoft.com/kb/173604

You can try to use the FindControl Method to find the menu item. Then use Execute method to implement your requirement.

---

Best regards,
Riquel
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

I was premature in marking this as the answer. When I look through the controls for thecommand bars for Excel (130 count), I don't see the caption for the menu item that I am looking for, yet the menu item is there for me to click on. I notice, that if a second workbook is opened from this third party, another menu item appears that duplicates the menu item created when the first workbook was opened. Neither of these menu items appears in the collection of controls in the collection of command bars for Excel.

Is there some other collection, method option, etc.that willallow instantiation ofthe control, so that the control can be executed?

Hello Joe,

We can call CommandBarButton.Execute() to execute a button's or menuitem'sfunction. For example, if we want to execute Menu->File->Print, we can call VB codes like,

-------------------------------------------------------
Application.CommandBar("Menu Bar").Controls(&File).Controls(&Print...).Execute()
-------------------------------------------------------

So the problem is how to find specified ID for that 3rd party menu item. The trick is right click at a blank position near a commandbar or menubar. And then we click the customize option from the context menu. In state of the Customize dialog pops up, we can right click any menu item or commandbar button to get a poped up menu. In that menu, we can see its accurate Name.
http://msdn.microsoft.com/en-us/library/aa432774.aspx

Please give it a try and let me know if you need any future help on this. Have a nice day!

Best regards,
Ji Zhou
Microsoft Onine Community Support

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

The control does not show up in the collection of command bars and command-bar controls. Therefore the command similiar to the following does not work because no control has a caption of "&Build" and no control has a caption of "&Build Insert Statements".

xcApp.CommandBars("Window Menu Bar").Controls("&Build").Controls("&Build").Controls("&Build Insert Statements").Execute()

I output a list of command bars and their controls using the code in the code block. (written in Visual Basic from Visual Studio 2008 Professional).Similar code written in VBA as a module in the spreadsheet itself shows identical list andalso fails to show the 2 controls in the collections, but the 2 controls are available to click and execute.