Excel Automation

[perrola]

Hello,

Using Vc++, I want to add at runtime a macro to an existing workbook, and run it.

How can I do this ?

Thanks...

Laurent

[rxbagain]

Here's my code that demonstrate the creation of excel macro and running it. In this sample, I created a new empty Excel worksheet. In your case, you can find many threads that deals with openning excel files. Be sure to call AfxOleInit first before using this code. Also, add the following classes from these OLB files.
excel9.olb - _Application, Workbooks and _Workbook
vb6ext.olb - _VBProject, _VBComponents, _VBComponent and _CodeModule.
As you can see, I'm using excel 9 (office 2000), you must use what is appropriate to your system.

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

_Application xls;
if (!xls.CreateDispatch("Excel.Application")) return;   
Workbooks xlws(xls.GetWorkbooks());
_Workbook xlw(xlws.Add(covOptional));
xlws.ReleaseDispatch();
_VBProject vbp(xlw.GetVBProject());   
_VBComponents vbcs(vbp.GetVBComponents());
_VBComponent vbc(vbcs.Add(1 /*vbext_ct_StdModule*/));
vbcs.ReleaseDispatch();
_CodeModule vbcm(vbc.GetCodeModule());
vbcm.AddFromString("Private Declare Function MessageBox Lib \"user32\" Alias \"MessageBoxA\" _\r\n"
   "   (ByVal hwnd&, ByVal lpText$, ByVal lpCaption$, ByVal wType&) As Long\r\n\r\n"
   "Sub MyMsgBox(ByVal hWndParent As Long)\r\n"
   "   Call MessageBox(hWndParent, \"MessageBox from Excel Macro\", \"Excel MyMsgBox\", 0)\r\n"
   "End Sub");
vbcm.DeleteLines(vbcm.GetCountOfLines(), 1);
vbcm.ReleaseDispatch();
vbc.ReleaseDispatch();
xls.Run(COleVariant("MyMsgBox"), COleVariant((long)this->m_hWnd), covOptional, 
   covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional,
   covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional);
xlw.Close(COleVariant((short)FALSE), covOptional, covOptional);
xlw.ReleaseDispatch();
xls.Quit();
xls.ReleaseDispatch();

Hope this will help you