Posted to microsoft.public.excel.programming
|
|
executing VBA macro in Excel from OLE
On 27/03/2019 21:30, Lynn McGuire wrote:
On 3/26/2019 1:46 PM, Lynn McGuire wrote:
On 3/26/2019 12:47 PM, Lynn McGuire wrote:
How does one execute a VBA macro in Excel from OLE ?Â* I cannot get
the C++ code to work.
Thanks,
Lynn
BTW, I am using the C++ code from
Â*Â*Â* http://support.microsoft.com/kb/216686
I am calling AutoWrap with name of the VBA macro in the
visualBasicMacroName string.Â* I am getting an error that the
pDisp-GetIDsOfNames call in AutoWrap is not finding the VBA method.
Â*Â*Â*Â*Â*VARIANT result1;
Â*Â*Â*Â*Â*VariantInit ( & result1);
Â*Â*Â*Â*Â*std::string errorMsg = "Executing Visual Basic Macro, " +
visualBasicMacroName + " (ExecuteVisualBasicMacro)";
Â*Â*Â*Â*Â*WCHAR methodName [1000];
Â*Â*Â*Â*Â*charToWchar (visualBasicMacroName.c_str (), methodName, sizeof
(methodName) / sizeof (WCHAR));
Â*Â*Â*Â*Â*AutoWrap (DISPATCH_METHOD, & result1, pExcelWorkbooks,
methodName, errorMsg, 0);
Thanks,
Lynn
Found and fixed my several problems with getting Excel.Run to working.
The main problem was that the AutoWrap method needed to be L"Run" and
the name of the method needed to be in a VARIANT data structure.Â* Also,
the Run command needed to be executed as a function of the Excel
application itself.
Â*Â*Â*Â*VARIANT result1;
Â*Â*Â*Â*VariantInit ( & result1);
Â*Â*Â*Â*std::string errorMsg = "Executing Visual Basic Macro, " +
visualBasicMacroName + " (ExecuteVisualBasicMacro)";
Â*Â*Â*Â*VARIANT methodName;
Â*Â*Â*Â*VariantInit ( & methodName);
Â*Â*Â*Â*methodName.vt = VT_BSTR;
Â*Â*Â*Â*Â*Â*Â* //Â* UTF-8 to wide
Â*Â*Â*Â*std::wstring wstrMethodName;
Â*Â*Â*Â*UTF8toWide (visualBasicMacroName.c_str (), wstrMethodName);
Â*Â*Â*Â*Â*Â*Â* //Â* the _bstr_t does not work with Watcom C++
Â*Â*Â*Â*Â*Â*Â* //Â* _bstr_t notebookNameBstr = _bstr_t (wstrNotebookName.c_str
());
Â*Â*Â*Â*BSTR methodNameBstr = SysAllocString (wstrMethodName.c_str ());
Â*Â*Â*Â*methodName.bstrVal = methodNameBstr;
Â*Â*Â*Â*OLEMethod (DISPATCH_METHOD, & result1, pExcelApplication, L"Run",
errorMsg, 1, methodName);
Â*Â*Â*Â*if (result1.vt == VT_DISPATCH)
Â*Â*Â*Â*{
Â*Â*Â*Â*}
Â*Â*Â*Â*VariantClear ( & result1);
Â*Â*Â*Â*VariantClear ( & methodName);
Thanks for the update :)
--
Adrian C
|