Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 26th 19, 05:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2019
Posts: 6
Default 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

  #2   Report Post  
Old March 26th 19, 06:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2019
Posts: 6
Default 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

  #3   Report Post  
Old March 26th 19, 07:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2019
Posts: 6
Default 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

  #4   Report Post  
Old March 26th 19, 08:01 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 11
Default 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
  #5   Report Post  
Old March 26th 19, 09:54 PM posted to microsoft.public.excel.programming
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.


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


  #6   Report Post  
Old March 26th 19, 10:39 PM posted to microsoft.public.excel.programming
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
  #7   Report Post  
Old March 26th 19, 11:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 11
Default 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
  #8   Report Post  
Old March 27th 19, 03:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,027
Default 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
  #9   Report Post  
Old March 27th 19, 09:30 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2019
Posts: 6
Default 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
  #10   Report Post  
Old March 28th 19, 12:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 11
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing a .BAT file using excel macro Vasanth Excel Discussion (Misc queries) 1 May 19th 12 05:11 PM
Executing a macro from an add-in in Excel 2007 OzJan Excel Programming 0 October 3rd 08 01:28 PM
Executing a VBA macro from within Excel 97 script RickT Excel Programming 1 April 24th 07 11:44 PM
Excel Macro Stop Executing Agustus Excel Programming 0 March 27th 06 05:11 PM
Executing an older excel 4.0 macro pgerrish Excel Programming 0 April 22nd 04 05:41 PM


All times are GMT +1. The time now is 12:28 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017