Quoth (e-mail address removed) (b wreath):
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 the
from a mysql database and update the Excel sheet. The script will also take
information from the Excel sheet and update the database.
Well, the first question here is 'what ways are there of initiating a
connection with another machine in VBA?'. Can you open sockets? Can you
make HTTP requests? XMLRPC requests? Until we know this we can't help
with the perl side.
If you can make HTTP requests, then this is probably the easiest answer.
Set up a web server on the linux box, with your perl script as a CGI,
and have the VBA perform a POST request which sends the pertinant(sp?)
data from the spreadsheet and receives the data from the database back.
If you can't make any sort of connection from within Excel, then I
reckon the best way forward is to install Perl on the windows machine as
well, and have the VBA invoke a perl script. This script can then get at
the open workbook using Win32::OLE, and communicate with the other
machine in any way reasonable: say, you have inetd listen on a given
port and invoke your perl script on the linux box, and the script on the
windows machine can then open a socket and pass the data back and forth
as needed.
You will need to design a protocol for these transactions; make sure you
think hard about it, so that it's properly extensible when you need it
to be. You will also need to think about security: how is the windows
machine going to prove to the linux machine that it is who it claims it
is, and vice versa (just as important in a case like this)? Is the data
concerned sensitive, so that you need to use SSL or whatever to encrypt
the transaction?
Modules to look at:
Win32::OLE for talking to Excel
IO::Socket::INET for opening sockets
IO::Socket::SSL for opening encrypted SSL sockets
DBI and DBD::mysql for talking to MySQL
maybe Storable, for 'freezing' perl data structures so they can be sent
to another perl process on a different machine
CGI or CGI::Lite for writing CGI scripts
Ben
