Call tool from VBA

[Tobias Weber]

Hi,
my aim is to open WikiLinks from Excel X cells.

I have a working solution using AppleScript, FastScripts and perl, but
it breaks down when the selection is discontinuous, probably because of
a bug in (this version of) Excel or its dictionary:

set theTexts to {}
tell application "Microsoft Excel"
set theRange to Selection
set theSize to count Cells of theRange
repeat with cellNumber from 1 to theSize
set theCell to Cell cellNumber of theRange
set end of theTexts to theCell's text
end repeat
end tell

So I turned to VBA, and indeed the following works with discontinuous
selections:

BASE = "http://127.0.0.1:8000/"
For Each Cell In Selection
ActiveWorkbook.FollowHyperlink Address:=BASE & Cell.Value,
NewWindow:=True
Next

However I get an error when replacing the ip with "localhost". Why? How
to make it work?

Anyway, I'd rather call a unix programm (perl script) instead of opening
the URL with Excel, since I need more logic and have no desire
whatsoever to write that in VBA.

I did get the "Shell" function to work with command line arguments (for
passing the cell content) on Windows but keep getting strange errors on
the Mac. Can someone point me to an example?

 

[gimme_this_gimme_that]

OK ...

set theTexts to {}
tell application "Microsoft Excel"
set theRange to Selection
set theSize to count Cells of theRange
repeat with cellNumber from 1 to theSize
set theCell to Cell cellNumber of theRange
set end of theTexts to theCell's text

There's a mistake on the above line. You need to replace "text" with
"value" as in

set end of theTexts to theCell's value

end repeat
end tell

Here is a Perl script using MacPerl::AppleScript that works for me.
Let me know how it compares what what you've been using...

I selected A1:A2,C1:C2 then executed this script ...

#!/usr/bin/perl
use MacPerl::AppleScript;

$fetch_selections=[
"set theTexts to {}",
"set theRange to selection",
"set theSize to count cells of theRange",
"repeat with cellNumber from 1 to theSize",
" set theCell to cell cellNumber of theRange",
" set end of theTexts to theCell's value",
"end repeat",
"theTexts"];

my $app = MacPerl::AppleScript->new("Microsoft Excel");
$app->execute("launch");
my $vc= $app->execute($fetch_selections);

foreach my $elem (@{$vc}) {
print "$elem ";
}

Off hand, I wondered why, if you're using Perl, that you end up going
through a selection.

I can't help with your localhost question except to ask: Can you
telnet to localhost?

Once you've figured out how to do that you'll have answered this
question yourself.

 

[gimme_this_gimme_that]

It annoys me when people post questions and then request that their
messages be deleted from the archive.

 

[Tobias Weber]

There's a mistake on the above line. You need to replace "text" with
"value" as in

As mentioned the cells will contain WikiLinks, which are text, so it
makes no difference which of those two properties I use.

Here is a Perl script using MacPerl::AppleScript that works for me.
Let me know how it compares what what you've been using...

That has at least 4 dependencies I'd have to install, so please tell me
again if it works with Excel *X*, as it looks no different to my
version. As mentioned I presume it's a bug and likely fixed in Excel
2004.

Off hand, I wondered why, if you're using Perl, that you end up going
through a selection.

I need to concatenate the cell contents to return them to perl. Is there
an easier way?

I can't help with your localhost question except to ask: Can you
telnet to localhost?

Of course I can.

ActiveWorkbook.FollowHyperlink seems to do some checks before launching
the default browser, one of which involves DNS, maybe through OS calls
so ancient that they don't yet respect the mapping of localhost to the
loopback ip (done in /etc/hosts?).

 

[gimme_this_gimme_that]

Tobias writes, regarding his mistake :

As mentioned the cells will contain WikiLinks, which are text, so it
makes no difference which of those two properties I use.

I'm using 2004 and it makes the difference between getting results and
not getting results in Script Editor.

Regarding dependancies:

That has at least 4 dependencies I'd have to install, so please tell me
again if it works with Excel *X*, as it looks no different to my
version. As mentioned I presume it's a bug and likely fixed in Excel
2004.

You're funny.

You may have to install 15 dependencies, but you should have the
entire thing going in less than half an hour. If you view this task as
time consuming or difficult then you should stop using Perl.

If you want to hook up Perl with AppleScript (and visa versa) you have
to get the tool for the job.

I need to concatenate the cell contents to return them to perl. Is there
an easier way?

The example I posted returns the value of all selected cells back to
Perl in an array reference.

ActiveWorkbook.FollowHyperlink seems to do some checks before launching
the default browser, one of which involves DNS, maybe through OS calls
so ancient that they don't yet respect the mapping of localhost to the
loopback ip (done in /etc/hosts?).

I fathom this is an issue associated with a port closed by default
with OS X usually dealt with in Preferences,Sharing,Advanced.

I asked, can you telnet to localhost? No response.

 

[Tobias Weber]

I'm using 2004 and it makes the difference between getting results and
not getting results in Script Editor.

With Excel X and a continuous selection there is no difference at all.
With a discontinuous selection the script aborts before getting even
close to this part.

Since you're using a different version I suppose there is little point
in comparing results.

You may have to install 15 dependencies, but you should have the
entire thing going in less than half an hour. If you view this task as

I was reluctant to invest time without knowing that your setup is
comparable.

Now I know it isn't, and I still decided to try MacPerl::AppleScript.
After just 8 dependencies I can say: doesn't work. Aynthing else would
have been surprising, since the bug is in Excel X and not Script Editor,
which I had been using.

If you want to hook up Perl with AppleScript (and visa versa) you have
to get the tool for the job.

Calling bin/osascript works fine, too.

The example I posted returns the value of all selected cells back to
Perl in an array reference.

And it did it by going through a selection, so how come you "wondered
why, if you're using Perl, that you end up going through a selection" ??

I fathom this is an issue associated with a port closed by default
with OS X usually dealt with in Preferences,Sharing,Advanced.

If the port was the problem it wouldn't work with 127.0.0.1, either. But
it does, and I said so in the original post.

I asked, can you telnet to localhost? No response.

I wrote "Of course I can". What more do you want?

 

[gimme_this_gimme_that]

Tobias writes:

What do you mean by different version?

Excel X is the Excel that comes in "Microsoft Office 2004" right? I'm
using the Student version with the latest upgrades.

Well, for me both the Applescript script and the Perl script work fine
with discontiguous selections.

 

[Tobias Weber]

What do you mean by different version?

I don't believe this...

Excel X is the Excel that comes in "Microsoft Office 2004" right? I'm

No, that's called Excel 2004. Mine came with "Microsoft Office v. X" in
2001.

 

[gimme_this_gimme_that]

Get Office 2004.

Get your solution.

"(e-mail address removed)" <[email protected]>

wrote:

I don't believe this...


No, that's called Excel 2004. Mine came with "Microsoft Office v. X" in
2001.

 

[Tobias Weber]

Get Office 2004.

Finally something useful from you. Unfortunately I cannot afford both
2004 now and 2008 in a few months.

So maybe we can get back to the original question: how to use the
"Shell" VBA function on the Mac to call a unix program?

 

[Tobias Weber]

I came up with this monstrous hack:

Thanks for the suggestion, might try it on sunday.

Purchased OmniGraffle not too long ago. My latest work is called
"Odyssey". I love Microsoft products.

http://bp2.blogger.com/_zPf3Cww-Z_M/RnL6hLXiGvI/AAAAAAAAAAU/OGM9Rfcvy7A/s
1600-h/ecmdm.png