Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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


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; }
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'
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
where you will hopefully see no warnings caused by little editing mistakes above, and instead see the green light, namely:

Created 'xlsls.dll'
you now proceed to register the control, using:

regsvr32 xlsls.dll


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
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...
...wufnik

-- in the world of the mules there are no rules --

In reply to generic linux command from Win32 VBA program: HOWTO by wufnik
in thread Initiate perl script in linux from a Windows VBA program by bwreath

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2024-03-28 23:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found