Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110874

Re: executing VBA macro in Excel from OLE

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-26 12:47 -0500
  Re: executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-26 13:46 -0500
    Re: executing VBA macro in Excel from OLE Adrian Caspersz <email@here.invalid> - 2019-03-26 20:01 +0000
      Re: executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-26 16:54 -0500
      Re: executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-26 17:39 -0500
        Re: executing VBA macro in Excel from OLE Adrian Caspersz <email@here.invalid> - 2019-03-26 23:58 +0000
        Re: executing VBA macro in Excel from OLE GS <gs@v.invalid> - 2019-03-27 11:47 -0400
    Re: executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-27 16:30 -0500
      Re: executing VBA macro in Excel from OLE Adrian Caspersz <email@here.invalid> - 2019-03-28 00:28 +0000
  Re: executing VBA macro in Excel from OLE Lynn McGuire <lynnmcguire5@gmail.com> - 2019-03-26 14:47 -0500

csiph-web