View Single Post
  #10   Report Post  
Old March 28th 19, 12:28 AM posted to microsoft.public.excel.programming
Adrian Caspersz Adrian Caspersz is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 11
Default 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