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

Groups > microsoft.public.excel.programming > #110860 > unrolled thread

executing VBA macro in Excel from OLE

Back to article view | Back to microsoft.public.excel.programming

Contents

  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

#110860 — executing VBA macro in Excel from OLE

How does one execute a VBA macro in Excel from OLE ?  I cannot get the 
C++ code to work.

Thanks,
Lynn

[toc] | [next] | [standalone]

#110862

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

[toc] | [prev] | [next] | [standalone]

#110864

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.

-- 
Adrian C

[toc] | [prev] | [next] | [standalone]

#110865

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.

Yes, the VBA method is in a module.  Huh, I could not get Excel to run 
from the PS command.  Wait, it is invisible and running.  Not cool.  But 
I cannot get it to save the spreadsheet so I do not know if it ran the 
macro.

Thanks,
Lynn

[toc] | [prev] | [next] | [standalone]

#110866

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

[toc] | [prev] | [next] | [standalone]

#110867

On 26/03/2019 22:39, Lynn McGuire wrote:
> On 3/26/2019 3:01 PM, Adrian Caspersz wrote:
> 
> 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()

$E.quit() should work.

-- 
Adrian C

[toc] | [prev] | [next] | [standalone]

#110869

So Lynn, are you done with C# or just spreading your wings?

An alternative would be to have a module named (say) "m_OpenClose" in your xlsm 
that has:

  Sub Auto_Open()
    'Startup code here executes when the workbook is opened
    Call testmacro
    ThisWorkbook.Save
    Application.Quit
  End Sub 'Auto_Open

  Sub Auto_Close()
    'Cleanup code here
  End Sub

In your C++ app:
  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> $workbook.RunAutoMacros 1

This enables your xlsm to do all the work just by opening it; - runs your 
macro, saves itself, shuts down Excel.

Note: If you put the code in the Workbook_Open event in the ThisWorkbook 
component you can skip the 3rd line of code in C++ app.

Optionally, your "testmacro" can save and quit as well so all you need to do is 
run it.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110873

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

[toc] | [prev] | [next] | [standalone]

#110874

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

[toc] | [prev] | [next] | [standalone]

#110863

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

And I am using Excel 2016.

Thanks,
Lynn

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web