• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Translate Excel's macro to embedded Excel functions

I've wrote VBA Excel macro:

Set bOpen = Application.CommandBars( "Standard" ).FindControl( Type:=msoControlButton, Id:=23)
bOpen.Visible = False

And I use embedded Excel like described at http://support.microsoft.com/support/kb/articles/Q184/6/63.asp (thanks to chensu).

What do I have to do to "translate" Excel macro into embedded Excel functions?
  • 6
  • 3
  • 2
  • +1
1 Solution
there is a VB section in EE!
AVaulinAuthor Commented:
I meant c++ functions from generated "excel8.h" and "excel8.cpp" files when said "embedded Excel functions". I gave link to MS support where you can see how they use those functions. I think you did not look there.
Ahh ok, I see its OLE Automation ...
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

AVaulinAuthor Commented:
Adjusted points to 300
This is too easy to see in VB:
Dim obj As New Excel.Application
Dim obj2 As Object
Set obj2 = obj.CommandBars("Standard").FindControl(msoControlButton, 23)
obj2.Visible = True

Now in C++, repleace (and re-code):


            // Get Class ID for Excel sheet.
            // This is used in creation.
            CLSID clsid;


            // Get Class ID for Excel sheet.
            // This is used in creation.
            CLSID clsid;

Here is URL for microsoft article which i think may be of some help to solve your problem..

HOWTO: Add and Run a VBA Macro Using Automation from MFC
AVaulinAuthor Commented:
To SergioL: it's not an answer. I know how for example fill cell A1 (see link I gave). The question was about "translating" next line:
Set bOpen = Application.CommandBars( "Standard" ).FindControl( Type:=msoControlButton, Id:=23)

To naveenkohli: that article is empty.
AVaulinAuthor Commented:
I can accept an example how to open file into embedded Excel
Thats interesting that URL did not have any conetents. May be you want to try again or here are the contenets of that article ..

HOWTO: Add and Run a VBA Macro Using Automation from MFC
The information in this article applies to:
Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0

Developers occasionally need to call an Automation client, such as Microsoft Excel 97, from another client, such as Microsoft Word 97, when automating Word from a Visual C++ client application.

This article discusses an approach to using the Microsoft Foundation Class (MFC) Library installed with Microsoft Visual C++ versions 5.0 and 6.0 to add a VBA macro to Word from a Visual C++ client. The program also adds two CommandBar Control buttons to Word's CommandBars: one to run the macro, instantiating Excel, and one to close Excel. The program also removes the macro from Word, and closes Word.

With adaptation, the Visual C++ code in the article can be used in your application, but the purpose of the article is to help you learn, both by walking through the code and by running the program.

For a more complete discussion of modifying the Office 97 CommandBars object, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q180625
   TITLE     : HOWTO: Use Automation to Modify the Office 97 Menu

Steps to Create the Project
1. Follow steps 1 through 12 in the following Microsoft Knowledge Base

   article to create a sample project, but change the type library used
   in step 9 to MSWord8.olb:

      ARTICLE-ID: Q178749
      TITLE     : HOWTO: Create an Automation Project Using MFC and a
                  Type Library

2. Repeat steps 8, 9, and 10 of the above article to add the typelib for
   Microsoft Office 97 to the project. The typelib for Microsoft Office 97
   is in the file Mso97.dll (default location is as follows):

      C:\Program Files\Microsoft Office\mso97.dll.

   NOTE: Select all of the components of that typelib. You will generate
   and add to your project the files Mso97.h and Mso97.cpp.

3. Next, repeat steps 8, 9, and 10 of the above article to add the
   typelib for the VBE Editor (Visual Basic for Applications Extensibility
   Library) to the project. That typelib is vbeext1.olb, and its default
   location is as follows:

      C:\Program Files\Common Files\Microsoft Shared\VBA

   NOTE: Select all of the components of that typelib. You will generate
   and add to your project the files vbeext1.h and vbeext1.cpp.

   As a result, you will get many duplicates in the COleDispatchDriver
   wrapper classes. These result from the duplication of IDispatch names
   in Microsoft Word 97, Microsoft Office 97 and the VBE Editor.

4. To resolve those duplications, for this exercise, use the "namespace"
   facility provided by Visual C++. These steps will resolve the

      a. At the very top of the Msword8.h header file, insert the line:

            namespace word {  // that's an opening brace.

      b. At the very bottom of that same file, add a line that contains
         only the closing brace and a semi-colon--specifically "};"
         (without the quotation marks).

5. At the beginning of the Msword8.cpp file, on a new line just after the
   compiler directive "#endif", and before the line that reads

      LPDISPATCH _Application:GetApplication

   add the following line:

      using namespace word;

6. Search for the line reading

      LPDISPATCH Shapes::GetApplication()

   Just before that line insert the following line:

      using namespace word;

7. In the Vbeext1.h file, search for the line reading (make sure it's the
   Window class ONLY):

      class Window : public COleDispatchDriver

   Immediately before this line, insert the line reading:

      namespace ext {  // that's an opening brace.

   At the end of that class, just after the closing brace/semi-colon,
   add a closing brace/semi-colon for the namespace (this is about line 85
   of the file).

8. In the Vbeext1.cpp file, search for the line reading:

      LPDISPATCH Window::GetVbe()

   Just before that line, insert the following line:

      using namespace ext;

9. Add the following #include statements at the top of the
   AutoProjectDlg.cpp program file:

      #include "msword8.h"
      #include "vbeext1.h"
      #include "mso97.h"

10. Add the following code to the CAutoProjectDlg::OnRun() event handler
   in the AutoProjectDlg.cpp file:

   Sample Code

      // To use a commandbar of a Microsoft Office application, when
      // the commandbar is to be used to run an out-of-process
      // application (executable), that command bar can only call
      // a macro or IMSOCommandTarget in a module of its own (local)
      // application. It cannot start an out-of-process executable.
      // The solution is to use it to call a macro in the local
      // application that uses COM to start the out-of-process executable.
      // The code below does the following:
      // 1. Starts Microsoft Word 97.
      // 2. Injects a new macro named "TestMacro" into Word.
      // 3. Places a commandbar toolbar on the default toolbar group
      //    of MSWord 97.
      // 4. When clicked, the toolbar's left-hand button calls TestMacro,
      //    which
      // 5. Starts Microsoft Excel 97.
      // 6. When clicked, the toolbar's right-hand button closes Excel.
      // Programmable access to the facilities of the Visual Basic for
      // Applications Editor is through the IDispatch interfaces declared
      // in the type library for Microsoft Visual Basic for Applications
      // Extensibility, // Vbeext1.olb.
      // First, here is a VBA macro, defined as text. This belongs in this
      // application code at this point.

      #define FUNCTION_TEXT "Dim oXL as Object\n\r" \
       "Dim oBook as Object\n\r" \
       "Public Sub TestMacro()\n\r" \
       "Set oXL = CreateObject(\"excel.application\")\n\r" \
       "oXL.Visible = True\n\r"  \
       "Set oBook = oXL.Workbooks.Add()\n\r" \
       "End Sub\n\r" \
       "Public Sub CloseExcel()\n\r" \
       "oBook.Saved = True\n\r" \
       "Set oBook = Nothing\n\r" \
       "oXL.Quit\n\r" \
       "Set oXL = Nothing\n\r" \
       "End Sub\n\r"
       // Be sure there are no spaces following the
       // line continuation character (\) on each line above.

      // Declarations:
      word::_Application oWord;  // using namespace
                                 // for Word typelib's members
      HRESULT hr;
      DISPID dispID = 0;
      LPDISPATCH lpDisp = 0;
      VARIANT vResult;  // A Struct with a pdispVal member
      long lCount = 0;
      char buf[1024];   // General purpose message buffer
      OLECHAR *strCBs = L"CommandBars";

      word::Shapes oShapes; // using namespace
      word::Shape oShape;

      // Convenient values declared as ColeVariants.
      COleVariant covTrue((short)TRUE),
                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      // Get the Word IDispatch pointer and
      //  attach it to the objWord object.
      if (!oWord.CreateDispatch("Word.Application"))
       AfxMessageBox("Couldn't get Word object.");

      oWord.SetVisible(TRUE);  //This shows the application.

      word::Documents oDocs(oWord.GetDocuments());
      word::_Document oDoc;

                           covOptional, // Template.
                           covFalse    // NewTemplate.

      VARIANT vIndex;
      vIndex.vt = VT_I4;
      vIndex.lVal = 2;

      // ********************* Load the macro ***********************
      // Create an empty VBA module for the document, then load
      // the text string defined as FUNCTION_TEXT into the module.
      _VBProject oProject = oDoc.GetVBProject();  // Return Word's
                                                  //  VBProject object
      _VBComponents oComponents = oProject.GetVBComponents(); // This is
                                                              // defined in
      // the typelib for Microsoft Visual Basic for Applications
      //  Extensibility.
      _VBComponent oComponent = oComponents.Add(1);      // ditto
      _CodeModule oModule = oComponent.GetCodeModule();  // ditto

      // Create a temporary file with the VBA code in it.
      CString strTempFile = "c:\\MacroTempFile.txt";  // Name of the file
      CFile *myCFile = new CFile;  //Ptr to file object saving VBA macro
      myCFile->Open(strTempFile, CFile::modeCreate|CFile::modeWrite);
      myCFile->Write(FUNCTION_TEXT, strlen(FUNCTION_TEXT)); // That is the
                                                            //  macro

      // Load the macro from the file into the VBA module
                                   // of the Word document.
      oModule.AddFromFile(strTempFile); // Load the macro into
                                        // the _CodeModule.
      myCFile->Remove(strTempFile); // Remove the file holding the macro.
      delete myCFile;  // Free the heap space for the CFile object.

      AfxMessageBox("Click Word's Title Bar to select Word,\n"
                    "Press Alt+F11 to see Word's VBA editor\n"
                    "and observe - there's your TestMacro.\n\n"
                    "Click the VBE's File/Close and Return menu\n"
                    "to restore Word, and then\n"
                    "Click this OK to resume processing");

      // ************** Build a new commandbar for Word **************
      // Add the commandbar to Word's commandbars collection.
      // Find dispID of &strCBs, (i.e. = L"CommandBars",
      // declared & assigned above)
      hr = oWord.m_lpDispatch->GetIDsOfNames(IID_NULL, &strCBs, 1,
       sprintf(buf,"Failed to GetIDsOfNames() :(... Error = %08lx",

      // Get a dispatch pointer to CommandBars! Use that of running
      //  application's (Word's) existing menu configuration.
      // "vResult" is a VARIANT. It's declared above.
      oWord.InvokeHelper(dispID,   // "Command Bars" in this case
        VT_VARIANT,      // Type of return value
       (void*)&vResult,  // Address of var receiving IDispatch of CmdBrs
       NULL              // Pointer to parameters string

      CommandBars cbs(vResult.pdispVal);  // Construct the CommandBars
                                          // object and attach the
                                          // IDispatch pointer to it.

      lCount = cbs.GetCount();   //  Word has 92!!??
                                 //  MSOffice reconfigures for each
                                 //  user-application.

      vResult.pdispVal = cbs.GetActiveMenuBar();  // Returns a LPDISPATCH
                            //  pointer of the CommandBar object that
                            //  represents the active menu bar in the
                            //  container application; that is, MS Office's
                            //  Word 8 Menu Bar Configuration.

      CommandBar oBar(vResult.pdispVal);  // Construct a new
                                          // CommandBar object
                                          // & attach the LPDispatch
                                          // of the active menu bar.

      VARIANT vName;
      vName.vt = VT_BSTR;
      vName.bstrVal = SysAllocString(L"MyNewCommandBar");
      // Variant for name of new bar

      VARIANT vPosition;
      vPosition.vt = VT_I2;
      vPosition.iVal = 1; // 4 = Floating;  0 = Left;
      // Variant for position of new bar

      AfxMessageBox("Now adding new bar to cbs collection");

      CommandBar oNewBar = cbs.Add(vName,  // const Variant Name =
                                           //  MyNewCommandBar
             vPosition, // const Variant Position = At top
             covFalse,  // const Variant (replace)MenuBar
             covTrue    // const Variant Temporary


      CommandBarControls oNewControls = oNewBar.GetControls();
                        // Object reference to collection

      VARIANT vType;
      vType.vt = VT_I4;
      vType.lVal = 1;
      // Control type is button

      CommandBarButton oNewButton2 =
             oNewControls.Add(vType,  // Type = msoControlButton
             covOptional,  // Id
             covOptional,  // Parameter
             covOptional,  // Before
             covTrue       // Temporary
      oNewButton2.SetStyle(3);  // msoButtonIconAndCaption
      oNewButton2.SetTooltipText("Close Excel");
      oNewButton2.SetState(0);  // msoButtonUp
      oNewButton2.SetFaceId((long) 2186);

      CommandBarButton oNewButton1 =
             oNewControls.Add(vType,   // Type = msoControlButton
             covOptional,  // Id
             covOptional,  // Parameter
             COleVariant((long)1),  // Before
             covTrue       // Temporary
      oNewButton1.SetStyle(3);  // msoButtonIconAndCaption
      oNewButton1.SetTooltipText("Run Macro");
      oNewButton1.SetState(0);  // msoButtonUp
      oNewButton1.SetFaceId((long) 186);  // commented for temporary test
      AfxMessageBox("Buttons in place. Click 'Macro' to start Excel");

      AfxMessageBox("Click the EndXL button to remove Excel,\n"
                    "Then Click OK here to remove macro.");
      oComponents.Remove(oComponent); // Remove the macro from the
                                      // document object.
      AfxMessageBox("Click Word's Title Bar to select Word,\n"
                    "Press Alt+F11 to see Word's VBA editor\n"
                    "and observe - there's no TestMacro.\n\n"
                    "Click the VBE's File/Close and Return menu\n"
                    "to restore Word, and then\n"
                    "Click this OK to resume processing");

      // Sanitation and cleanup

      oDoc.SetSaved(TRUE); // Avoid "Save As" dialog.
      oDoc.Close(covFalse, covOptional, covFalse);  // Close Word.
      oWord.Quit(covFalse, covTrue, covFalse);

      //**End of Sample**

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q178749
   TITLE     : HOWTO: Create an Automation Project Using MFC and a
               Type Library

   ARTICLE-ID: Q180625
   TITLE     : HOWTO: Use Automation to Modify the Office 97 Menu

AVaulinAuthor Commented:
To naveenkohli: leave empty answer. I'll accept it.
Did it help you cause i am also interested in getting to know this stuff.....
i have started this Office automation stuff very recently

AVaulinAuthor Commented:
Yes, it helped me. But this is not fisrt step. First step is described in http://support.microsoft.com/support/kb/articles/Q184/6/63.asp

Those 2 arcticles understanding gives possibility to do everything you want with Excel (I'm not need control MS Word now, but some tryings showed that MS Word is more correct program).

I give only "C" mark because you only showed right way but did not answer question. I found answers myself with your help.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now