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

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

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

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

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

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

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

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

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

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