CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Excel Automation

  1. #1
    Join Date
    Dec 2001
    Posts
    52

    Question Excel Automation

    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

  2. #2
    Join Date
    Apr 2003
    Posts
    1,755

    Smile

    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.
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)