greetings bwreath,

nothing beats an example imho when it comes glueing excel to linux. here is how to do it. ahem, one way to do it.

to be precise, i will show how to get the output from a unix ls -al appearing in an excel msgbox.

"but 'ls -al'" is not perl, i hear you say. indeedy. what i am giving is simply how to call any unix command remotely from excel.

you might decide to call ps, for instance. replace the string "ls -al" with "/usr/bin/perl miscriptpath" to taste, for your perl script. or "generic unix command".

plz forgive assorted inelegancies in the following code, and while you are at it, you may as well forgive the relative (in)security of the solution described.

First: Ingredients.

you are going to need activestate, as suggested by my esteemed colleague castaway. in particular, you are going to need PerlCtrl . i am assuming you have this. go and get it if you do not. another thing you are going to need is the module Net::Telnet. can't do scripts without it, sorry. preferably, you'd use modules for SSHing, but - plz excuse - i am afraid i have not tested these, and i have Net::Telnet, so Net::Telnet it is.

Second: Cook up your basic .ctrl file

do this by:

PerlCtrl -t > xlsls.ctrl
[download]

Third: Add spice to .ctrl file

...now that you are the proud owner of a remarkably ugly .ctrl file, you need to add some stuff before the pod. let us make what you add the following:

package MyControl;

use Net::Telnet;

sub doit {
    my $t = new Net::Telnet(Timeout=>500);
    $t->open("lin.ux.box.ip") or die $!; # 
    $t->login("user id","password");
        # replace ls -al below with /usr/bin/perl as reqd
    chomp(my @oldls = $t->cmd("ls -al")); 
    my $lsstr = join "\n", map { $_ . ") " . $oldls[$_] } (0  .. $#old
+ls);
    $t->close();
    return $lsstr;
}
[download]

the above is where we use Net::Telnet, which does all the hard work.

however, there are yet more things to replace. hope you are following carefully or this will most certainly go wrong. do not blame me if it does. replace all of the following attributes exactly as described. apologies for the indescribably trite use of MyThisThatTheOther...

    PackageName     => 'MyControl',
    ControlName     => 'MyVeryOwnControl',
    ControlVer      => 1,  # increment if new 
    ProgID          => 'My.Control',
    LCID            => 0,
    DefaultMethod   => 'doit',
    Methods         => {
        'doit' => {
        RetType             =>  VT_BSTR,
        TotalParams         =>  0,
        NumOptionalParams   =>  0,
        ParamList           =>[ ]
        }
    }, # end of 'Methods'
    Properties => {
        
    }, # end of 'Properties'
[download]

the VT_BSTR holds the results of our "ls -al". or STDOUT from your script, if you like.

Fourth: Register your dish (a .dll)

ok, we are just about done, believe it or no.

create the dll by:

PerlCtrl xlsls.ctrl
[download]

where you will hopefully see no warnings caused by little editing mistakes above, and instead see the green light, namely:

Created 'xlsls.dll'
[download]

you now proceed to register the control, using:

regsvr32 xlsls.dll
[download]

Fifth: Embed your dish in a tasty vb wrapper

well, maybe not so-tasty...

open excel, hit alt-f11, and insert the following in a vba module: apologies for even the mention of this appalling language in the monastery...

Sub unrun()
    Dim objMyControl
    Set objMyControl = CreateObject("My.Control")
    'testing the result of our unix listing/command
    MsgBox objMyControl.doit
End Sub
[download]

while you are in the editor, hit F8, step through the code, and lo! the contents of your unix home directory should appear before all the rest. you are done. go home. stare wide eyed with disbelief at the news.

hope that helps...