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,
Lynn