View Single Post
  #9   Report Post  
Old March 27th 19, 09:30 PM posted to microsoft.public.excel.programming
Lynn McGuire[_3_] Lynn McGuire[_3_] is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2019
Posts: 6
Default executing VBA macro in Excel from OLE

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