Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Executing a .BAT file using excel macro | Excel Discussion (Misc queries) | |||
Executing a macro from an add-in in Excel 2007 | Excel Programming | |||
Executing a VBA macro from within Excel 97 script | Excel Programming | |||
Excel Macro Stop Executing | Excel Programming | |||
Executing an older excel 4.0 macro | Excel Programming |