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

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

Re: executing VBA macro in Excel from OLE

Show all headers | View raw

On 3/26/2019 3:01 PM, Adrian Caspersz wrote:
> On 26/03/2019 18:46, 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);
>>
> 
> Is the VBA method in a 'module', i.e. not a sheet.
> 
> As a visibility test, can you call it from somewhere else, say PowerShell?
> 
> https://stackoverflow.com/questions/19536241/calling-excel-macros-from-powershell-with-arguments 
> 
> 
> FWIW, these days I'm a fan of controlling Excel from PowerShell instead 
> of VBA[1] inside, as I can reach out to a lot more connected systems. 
> That C++ looks scary, unless you have other reasons for being in that 
> environment.
> 
> [1] event handlers being an exception.

Cool, I got it to work with the following and my macro was run:

PS C:\Users\lmc> $E = New-Object -ComObject Excel.Application
PS C:\Users\lmc> $workbook = $E.Workbooks.Open("C:\dii\spreadsheet2.xlsm")
PS C:\Users\lmc> $E.Run("testmacro")
PS C:\Users\lmc> $workbook.save()
PS C:\Users\lmc> $workbook.close()

But I could not get Excel to quit:

PS C:\Users\lmc> $workbook.quit()
Method invocation failed because [System.__ComObject] doesn't contain a 
method named 'quit'.
At line:1 char:15
+ $workbook.quit <<<< ()
     + CategoryInfo          : InvalidOperation: (quit:String) [], 
RuntimeException
     + FullyQualifiedErrorId : MethodNotFound

Thanks !

Lynn

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