From: phil oakleaf on
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
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
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
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
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