如何利用MFC添加VBA宏？有时程序员需要从一些应用程序如 Word调用自动化客户（如Microsoft Excel），下来我们就看看如何利用VC++来实现这一点。本文主要讨论如何使用MFC向Word中添加VBA宏。此程序同时也添加CommandBar控件按钮，此按钮用来运行此宏以实例化Excel。如果您需要对 Office的不同版本进行开发，请注意不同的类型库。下面我们针对Office97进行说明。Microsoft Office 97的类型库为：C:\Program Files\Microsoft Office\mso97.dll. 而 Microsoft Office 2000的类型库为Mso9.dll，对于XP，它的类型库则为C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.dll.除了要添加上面所说的类型库以外，还需要添加 VBE编辑器类型库，此类型库为：C:\Program Files\Common Files\Microsoft Shared\VBA现在，你会得到不许多 ColeDispatchDriver包装类，这些类均是刚才导入类型库得到的。为了解决重复的问题，我们引入"namespace"机制。在Mswordx.h文件开始处加入：namespace word { // that's an opening brace. 而在结尾处加入 "};"在 Mswordx.cpp文件中，在"#endif"和LPDISPATCH _Application:GetApplication之前加入 using namespace word;找到 LPDISPATCH Shapes::GetApplication()，在此行前加入using namespace word;在 Vbeext1.h file中找到class Window : public COleDispatchDriver，在此行前加入namespace ext { 在文件结尾补上另一半大括号。在 Vbeext1.cpp中，找到LPDISPATCH Window::GetVbe()，在此行前加入using namespace ext;下面我们就要在 AutoProjectDlg.cpp加入这些头文件了：#include "msword8.h" // or appropriate version's header 将下面的代码加入到 CAutoProjectDlg::OnRun()中（它位于AutoProjectDlg.cpp中）： // Sample Code
// -----------
//
// 代码完成下面的功能：
// 1. 启动Microsoft Word.
// 2. 加入"TestMacro"宏
// 3. 为上面的宏加入新按钮
// 4. 双击此按钮，启动Microsoft Excel.
// 6. 再次双击会关闭Excel.
//
#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"
// Declarations:
word::_Application oWord;
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.
));
/* // End of Word 97 code
/* 下面的代码是为Word 2002设计的 */
oDoc.AttachDispatch(oDocs.Add(
covOptional, // Template.
covFalse, // NewTemplate.
covOptional, // Document type
covTrue // Visible
));
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.
::MessageBox(NULL, "If you get the message \n\n"
"\"Programmatic access to Visual Basic Project is not trusted\"\n\n"
"adjust the security settings in Excel's Tools | Macro dialog.\n"
"On the \"Trusted Sources\" tab check the\n"
"\"Trust Access to Visual Basic Project\" property.",
"VBA 2002 Advisory",327744);
_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.
*/ End of Office 97 code
/* The following is for Office XP - Note the leading underscore */
_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** |