ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   executing VBA macro in Excel from OLE (https://www.excelbanter.com/excel-programming/454255-executing-vba-macro-excel-ole.html)

Lynn McGuire[_3_] March 26th 19 05:47 PM

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

Lynn McGuire[_3_] March 26th 19 06:46 PM

executing VBA macro in Excel from OLE
 
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


Lynn McGuire[_3_] March 26th 19 07:47 PM

executing VBA macro in Excel from OLE
 
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


Adrian Caspersz March 26th 19 08:01 PM

executing VBA macro in Excel from OLE
 
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.

--
Adrian C

Lynn McGuire[_3_] March 26th 19 09:54 PM

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.


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

Lynn McGuire[_3_] March 26th 19 10:39 PM

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

Adrian Caspersz March 26th 19 11:58 PM

executing VBA macro in Excel from OLE
 
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

GS[_6_] March 27th 19 03:47 PM

executing VBA macro in Excel from OLE
 
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

Lynn McGuire[_3_] March 27th 19 09:30 PM

executing VBA macro in Excel from OLE
 
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

Adrian Caspersz March 28th 19 12:28 AM

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


All times are GMT +1. The time now is 10:34 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
ExcelBanter.com