Call Excel Macros from Code

Question

I need a way to programmatically run a macro recorder in Excel and provide a name for the new macro that will be created.

It can be from VSTO or VBA or using Interop collections for Office.

Any ideas on how to do this?

Answer 1

In VBA:

Dim ctrlStart As CommandBarControl, ctrlStop As CommandBarControl Set ctrlStart = Application.CommandBars.FindControl(ID:=184) Set ctrlStop = Application.CommandBars.FindControl(ID:=2186) ctrlStart.Execute 'name part would go here, but first you have to deal with a modal dialog ctrlStop.Execute 

It appears that the Execute method in the RecordMacro control opens a modal dialog box. It is not possible to pass this parameter or do something like SendKeys. The only way I can see this is to write a subsection that will rename the macro after the fact. It will be a little difficult to determine what the name of the new macro is, and you will still have a dialog box to review.