View Single Post
  #6   Report Post  
Old March 26th 19, 10:39 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 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/...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