Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Initiate perl script in linux from a Windows VBA program

by bwreath (Initiate)
on Jun 02, 2004 at 16:08 UTC ( #359532=perlquestion: print w/replies, xml ) Need Help??

bwreath has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I would appreciate help with the following. I would like to be able to initiate a perl script that is residing on a linux server from an Excel workbook. I am working on the Excel workbook in Windows. I would appreciate any pointers, sample code for accomplishing this in VBA or some other method. The perl script that I am trying to call will take the information from a mysql database and update the Excel sheet. The script will also take information from the Excel sheet and update the database. thanks very much.
  • Comment on Initiate perl script in linux from a Windows VBA program

Replies are listed 'Best First'.
Re: Initiate perl script in linux from a Windows VBA program
by castaway (Parson) on Jun 02, 2004 at 16:26 UTC
    It sounds like you're trying to do this the complicated way. Is there a good reason the perl script is running on a linux machine, updating your Excel, presumably on the Windows machine, or a shared drive? Did you know there are versions of Perl for Windows? Go look at http://www.activestate.com for one.

    Also, I would suggest just using Perl for all of it, depending on what the rest of the VBA code is doing.

    Anyhow. Calling perl installed on the Windows machine is realtively easy, use the VBA 'Shell' function to run the executable, and pass it whichever parameters you want with the script name. Calling perl on a remote machine involves knowing how you would do it by hand. You can either install it as a CGI, and use the HTTP VB objects to simulate surfing there. Or you attempt to run the perl executable 'by hand', by logging in remotely, with telnet for example. (VB might have a control for that, or you'll need the socket control).

    You could also Shell out to plink.exe (part of the putty suite of ssh tools) and run a remote command that way. docs

    Other than that, its hard to give advice.. To programmatically do something like this, you first have to know how you would do it by hand..

    C.

      If you go with the Active Perl Method, you should give Spreadsheet::ParseExcel and Spreadsheet::WriteExcel a look. They are both available from the ActiveState PPM and CPAN. The only drawback to WriteExcel is that it can not write to an existing file, so you will have to write the entire workbook each time.

      Greg W

      Thanks to everyone for their replies.

      It seems to me that there are two main branches of solutions for me. One is to set up a web server on the linux box and use my perl script as a CGI. I would appreciate it if somebody could provide some sample code on using the HTTP VB objects within VBA to call a script on the linux box.
      I think one question that I need to find out is how do I go about finding out if I am able to make http requests from within VBA?

      After, being able to proceed through this CGI path what points or examples should I consider when thinking about security?

      This CGI option seems to be suitable for me since I already have a linux web server set up and one of the reasons I want to keep perl on the linux server is that users of the application do not need to have perl installed on their computer.

      The other option that seems to be popular is the use of ODBC. I have found from some sources that if I go through this ODBC route that I would need to install perl on the windows side. Is this true?


      What would be the deciding factors in figuring out which route to go whether it be using CGI or ODBC? Basically in the application I want to develop would allow a user within Excel will have a worksheet and be able to press a button to start a process where one column in the worksheet will be sent to query the mysql database to retrieve about six columns of data to update the Excel worksheet. As well, about two of the columns will be used to update the mysql database. Sometimes the columns will be about 30 rows but it is possible that I will be working with about 10 000 rows. I am concerned about speed but I think that the slowness could possibly be obviated by precomputing the sql queries and placing the values in a table since I already know what the queries will be most of the time.

      thanks very much

generic linux command from Win32 VBA program: HOWTO
by wufnik (Friar) on Jun 03, 2004 at 17:10 UTC
    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 --
Re: Initiate perl script in linux from a Windows VBA program
by Ninthwave (Chaplain) on Jun 02, 2004 at 16:37 UTC

    Easiest way is set up and ODBC driver to connect to the mysql database. VBA lets you query ODBC so you can program in the queries and bypass the perl script.

    If there are functions in the perl script you can not do in VBA you could install Active State Perl on the Windows Machine and use perl to via ODBC to connect to the Linux Box, process the data and use Win32::OLE to control Excel, or to use Excel::Template moduels to create the data structure. It all really depends on what you are doing with the data.

    I think the description you have your best bet would be to use the mySQL ODBC drivers and use straight vba.

    I feel sick from uttering those words.

    "No matter where you go, there you are." BB

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://359532]
Approved by sunadmn
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2022-10-04 10:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (16 votes). Check out past polls.

    Notices?