怎样在VC中调用Excel中的VBA程序??

abc1235 2004-06-07 03:29:51
我要在VC程序里调用Excel中的VBA程序,如何实现??
能操作Excel的菜单也行!
在VC里嵌入Excel后如何控制混合的菜单?



哪位高手清楚请指教!!!!!
有实现的例子吗?或实现的方法!
...全文
721 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
荒废的太阳 2004-06-27
  • 打赏
  • 举报
回复
(已经过去了这么多天了,再问一下gjd111686(数字金刚)大师不知道能否得到响应。)
大师在给出的源代码中写到了需要include word和vba的头文件,如此说来,这份源代码
还是必须要通过源代码来了解word的很多东西了,或者说,word需要实现idispatch接口。
这种理解是正确的吗?
(是不是提供COM控件的一定也要提供该控件的IDispatch实现?)
(刚看COM的东西,也许理解得非常错误,请大师给点指教)
abc1235 2004-06-10
  • 打赏
  • 举报
回复
????????????????????????
abc1235 2004-06-09
  • 打赏
  • 举报
回复
_Application m_excelApp;
Workbooks m_excelBooks;

m_excelBooks.ReleaseDispatch();
m_excelApp.m_bAutoRelease=true;

COleException e;
CString buf;
if(!m_excelApp.CreateDispatch("Excel.Application", &e))
{
buf.Format("创建Excel失败!%ld (%08lx)",e.m_sc, e.m_sc);
AfxMessageBox(buf, MB_SETFOREGROUND);
return;
}
m_excelBooks.Open("报表管理.xls");//?????????
m_excelApp.SetVisible(TRUE);
m_excelApp.Run("BBMStartModule.auto_open()");//??????
open()和run()的参数怎么写错了吗?编译不过去!
abc1235 2004-06-08
  • 打赏
  • 举报
回复
"VBE编辑器类型库,此类型库为:C:\Program Files\Common Files\Microsoft Shared\VBA "的什么文件?VBE6.DLL吗?加入所有类吗?
gjd111686 2004-06-08
  • 打赏
  • 举报
回复
控制菜单可以通过CommandBars 集合对象
gjd111686 2004-06-08
  • 打赏
  • 举报
回复
// 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**

gjd111686 2004-06-08
  • 打赏
  • 举报
回复
// ********************* 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
gjd111686 2004-06-08
  • 打赏
  • 举报
回复
如何利用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
#include "vbeext1.h"
#include "mso97.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;

3,245

社区成员

发帖
与我相关
我的任务
社区描述
ATL,Active Template Library活动(动态)模板库,是一种微软程序库,支持利用C++语言编写ASP代码以及其它ActiveX程序。
社区管理员
  • ATL/ActiveX/COM社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧