Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Translate Excel's macro to embedded Excel functions

Posted on 1999-06-29
12
Medium Priority
?
556 Views
Last Modified: 2013-11-20
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?
0
Comment
Question by:AVaulin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:nil_dib
ID: 1308331
there is a VB section in EE!
0
 
LVL 4

Author Comment

by:AVaulin
ID: 1308332
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.
0
 
LVL 4

Expert Comment

by:nil_dib
ID: 1308333
Ahh ok, I see its OLE Automation ...
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 4

Author Comment

by:AVaulin
ID: 1308334
Adjusted points to 300
0
 
LVL 1

Expert Comment

by:SergioL
ID: 1308335
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;
            if(FAILED(::CLSIDFromProgID(L"Excel.sheet",&clsid)))
...

...

            // Get Class ID for Excel sheet.
            // This is used in creation.
            CLSID clsid;
            if(FAILED(::CLSIDFromProgID(L"Excel.application",&clsid)))
...


0
 
LVL 23

Expert Comment

by:naveenkohli
ID: 1308336
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
 
http://support.microsoft.com/support/kb/articles/q194/9/06.asp
0
 
LVL 4

Author Comment

by:AVaulin
ID: 1308337
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.
0
 
LVL 4

Author Comment

by:AVaulin
ID: 1308338
I can accept an example how to open file into embedded Excel
0
 
LVL 23

Expert Comment

by:naveenkohli
ID: 1308339
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


SUMMARY
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.



MORE INFORMATION
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
   duplications.

      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),
                  covFalse((short)FALSE),
                  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.");
       return;
      }

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

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

      oDoc.AttachDispatch(oDocs.Add(
                           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
      myCFile->Close();

      // 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,
                                         LOCALE_SYSTEM_DEFAULT,
                                         &dispID);
      if(FAILED(hr))
      {
       sprintf(buf,"Failed to GetIDsOfNames() :(... Error = %08lx",
          (long)hr);
       AfxMessageBox(buf,MB_SETFOREGROUND);
      }

      // 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
        DISPATCH_METHOD | DISPATCH_PROPERTYGET,
        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
             );

      oNewBar.SetVisible(TRUE);

      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.SetCaption("EndXL");
      oNewButton2.SetTooltipText("Close Excel");
      oNewButton2.SetVisible(TRUE);
      oNewButton2.SetState(0);  // msoButtonUp
      oNewButton2.SetFaceId((long) 2186);
      oNewButton2.SetOnAction("CloseExcel");

      CommandBarButton oNewButton1 =
             oNewControls.Add(vType,   // Type = msoControlButton
             covOptional,  // Id
             covOptional,  // Parameter
             COleVariant((long)1),  // Before
             covTrue       // Temporary
             );
      oNewButton1.SetStyle(3);  // msoButtonIconAndCaption
      oNewButton1.SetCaption("Macro");
      oNewButton1.SetTooltipText("Run Macro");
      oNewButton1.SetVisible(TRUE);
      oNewButton1.SetState(0);  // msoButtonUp
      oNewButton1.SetFaceId((long) 186);  // commented for temporary test
      oNewButton1.SetOnAction("TestMacro");
      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
      oModule.ReleaseDispatch();
      oComponent.ReleaseDispatch();
      oComponents.ReleaseDispatch();
      oProject.ReleaseDispatch();
      oNewButton1.ReleaseDispatch();
      oNewButton2.ReleaseDispatch();
      oNewControls.ReleaseDispatch();
      oNewBar.ReleaseDispatch();
      oBar.ReleaseDispatch();
      cbs.ReleaseDispatch();

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

      //**End of Sample**


REFERENCES
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


0
 
LVL 4

Author Comment

by:AVaulin
ID: 1308340
To naveenkohli: leave empty answer. I'll accept it.
0
 
LVL 23

Accepted Solution

by:
naveenkohli earned 600 total points
ID: 1308341
Did it help you cause i am also interested in getting to know this stuff.....
i have started this Office automation stuff very recently


0
 
LVL 4

Author Comment

by:AVaulin
ID: 1308342
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.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question