automation Excel running macro in XLA
in [MFC]
|
Prev: Incorrect control background colors on CTabCtrl
Next: Setting the ColorDepth EncoderParameter to GDI+ Image::Save() ???
From: phil oakleaf on 1 Mar 2010 18:08 I have linked up the Automation code and can run an Excel macro from MFC - works really well What I'd like to do is put all my VBA code into an Excel XLA so that I'll always have the required macros available. But now when I try to run the macro it complains that it cant find it. Is there a special syntax for executing "Application.Run(...)" macros within an XLA - (is it even possible) Any help will be much appreciated
From: phil oakleaf on 2 Mar 2010 07:43 phil oakleaf wrote: > I have linked up the Automation code and can run an Excel macro from MFC > - works really well > > > What I'd like to do is put all my VBA code into an Excel XLA so that > I'll always have the required macros available. > > But now when I try to run the macro it complains that it cant find it. > > Is there a special syntax for executing "Application.Run(...)" macros > within an XLA - (is it even possible) > > Any help will be much appreciated Found a solution - It seems I have to specify the AddIn name with the macro Run(ColeVariant("\'C:\\Callidus\\CallidusXML.xla\'!CallidusXML")....); bit clunky but it does the job and works well Thanks
From: Joseph M. Newcomer on 2 Mar 2010 09:42 Generally, you should not be hardwiring a path into your code. For example, if the macro is delivered as part of your executable, you would normally use GetModuleFileName to obtain the path to your executable, then use some of the path support functions to strip off the filename.exe and add in the information you want. _tsplitpath_s, _tmakepath_s in VS2008 will do the job. Note that you can use NULL to specify arguments you don't need, e.g., to strip the filename, you declare variables for the device and path, but specify the file and ext parameters as NULL for _tsplitpath[_s]. To reassemble the filename you want, use _tmakepath[_s], specifying the dev and path you filled in, and supplying fhe filename and ext you want. joe On Tue, 02 Mar 2010 12:43:13 +0000, phil oakleaf <news(a)oakleafsoftware.co.uk> wrote: >phil oakleaf wrote: >> I have linked up the Automation code and can run an Excel macro from MFC >> - works really well >> >> >> What I'd like to do is put all my VBA code into an Excel XLA so that >> I'll always have the required macros available. >> >> But now when I try to run the macro it complains that it cant find it. >> >> Is there a special syntax for executing "Application.Run(...)" macros >> within an XLA - (is it even possible) >> >> Any help will be much appreciated >Found a solution - It seems I have to specify the AddIn name with the macro > >Run(ColeVariant("\'C:\\Callidus\\CallidusXML.xla\'!CallidusXML")....); > >bit clunky but it does the job and works well > >Thanks Joseph M. Newcomer [MVP] email: newcomer(a)flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.htm
From: phil oakleaf on 2 Mar 2010 11:16 Joseph M. Newcomer wrote: > Generally, you should not be hardwiring a path into your code. For example, if the macro > is delivered as part of your executable, you would normally use GetModuleFileName to > obtain the path to your executable, then use some of the path support functions to strip > off the filename.exe and add in the information you want. _tsplitpath_s, _tmakepath_s in > VS2008 will do the job. Note that you can use NULL to specify arguments you don't need, > e.g., to strip the filename, you declare variables for the device and path, but specify > the file and ext parameters as NULL for _tsplitpath[_s]. To reassemble the filename you > want, use _tmakepath[_s], specifying the dev and path you filled in, and supplying fhe > filename and ext you want. > joe > > On Tue, 02 Mar 2010 12:43:13 +0000, phil oakleaf <news(a)oakleafsoftware.co.uk> wrote: > >> phil oakleaf wrote: >>> I have linked up the Automation code and can run an Excel macro from MFC >>> - works really well >>> >>> >>> What I'd like to do is put all my VBA code into an Excel XLA so that >>> I'll always have the required macros available. >>> >>> But now when I try to run the macro it complains that it cant find it. >>> >>> Is there a special syntax for executing "Application.Run(...)" macros >>> within an XLA - (is it even possible) >>> >>> Any help will be much appreciated >> Found a solution - It seems I have to specify the AddIn name with the macro >> >> Run(ColeVariant("\'C:\\Callidus\\CallidusXML.xla\'!CallidusXML")....); >> >> bit clunky but it does the job and works well >> >> Thanks > Joseph M. Newcomer [MVP] > email: newcomer(a)flounder.com > Web: http://www.flounder.com > MVP Tips: http://www.flounder.com/mvp_tips.htm Joe I take the point about the path name. The main thing was learning that macros stored in an XLA appear to need the full path as seen within Excel for it to be found - leaving off the '.....\\CallidusXML.xls\'! bit seems to leave Excel unable to find the macro. Now, that I've got the VBA code into an XLA and the MFC program being able to execute it - it seems a neat solution and is working really well. Thanks Phil
From: Joseph M. Newcomer on 3 Mar 2010 09:59
See below... On Tue, 02 Mar 2010 16:16:02 +0000, phil oakleaf <news(a)oakleafsoftware.co.uk> wrote: >Joseph M. Newcomer wrote: >> Generally, you should not be hardwiring a path into your code. For example, if the macro >> is delivered as part of your executable, you would normally use GetModuleFileName to >> obtain the path to your executable, then use some of the path support functions to strip >> off the filename.exe and add in the information you want. _tsplitpath_s, _tmakepath_s in >> VS2008 will do the job. Note that you can use NULL to specify arguments you don't need, >> e.g., to strip the filename, you declare variables for the device and path, but specify >> the file and ext parameters as NULL for _tsplitpath[_s]. To reassemble the filename you >> want, use _tmakepath[_s], specifying the dev and path you filled in, and supplying fhe >> filename and ext you want. >> joe >> >> On Tue, 02 Mar 2010 12:43:13 +0000, phil oakleaf <news(a)oakleafsoftware.co.uk> wrote: >> >>> phil oakleaf wrote: >>>> I have linked up the Automation code and can run an Excel macro from MFC >>>> - works really well >>>> >>>> >>>> What I'd like to do is put all my VBA code into an Excel XLA so that >>>> I'll always have the required macros available. >>>> >>>> But now when I try to run the macro it complains that it cant find it. >>>> >>>> Is there a special syntax for executing "Application.Run(...)" macros >>>> within an XLA - (is it even possible) >>>> >>>> Any help will be much appreciated >>> Found a solution - It seems I have to specify the AddIn name with the macro >>> >>> Run(ColeVariant("\'C:\\Callidus\\CallidusXML.xla\'!CallidusXML")....); >>> >>> bit clunky but it does the job and works well >>> >>> Thanks >> Joseph M. Newcomer [MVP] >> email: newcomer(a)flounder.com >> Web: http://www.flounder.com >> MVP Tips: http://www.flounder.com/mvp_tips.htm > > >Joe > >I take the point about the path name. The main thing was learning that >macros stored in an XLA appear to need the full path as seen within >Excel for it to be found - leaving off the '.....\\CallidusXML.xls\'! >bit seems to leave Excel unable to find the macro. **** Yes. That sounds right. Excel is supplying this if you load it from within Excel, because you did a "file open" and picked the directory first. The "file open" dialog will do a SetCurrentDirectory to establish the local directory, so only the filename part is required. But when coming from outside, this doesn't happen because the directory is not set by the file open dialog, so you need to provide the full path explicitly. joe **** > >Now, that I've got the VBA code into an XLA and the MFC program being >able to execute it - it seems a neat solution and is working really well. > >Thanks >Phil Joseph M. Newcomer [MVP] email: newcomer(a)flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.htm |