Initiate perl script in linux from a Windows VBA program

Discussion in 'Perl Misc' started by b wreath, May 17, 2004.

  1. b wreath

    b wreath Guest

    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 the
    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.
    b wreath, May 17, 2004
    #1
    1. Advertising

  2. b wreath

    Ben Morrow Guest

    Quoth (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

    --
    It will be seen that the Erwhonians are a meek and long-suffering people,
    easily led by the nose, and quick to offer up common sense at the shrine of
    logic, when a philosopher convinces them that their institutions are not based
    on the strictest morality. [Samuel Butler, paraphrased]
    Ben Morrow, May 17, 2004
    #2
    1. Advertising

  3. b wreath

    b wreath Guest

    Ben Morrow <> wrote in message news:<c8apvd$238$>...
    > Quoth (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



    Hi,

    Thanks for the detailed response and shedding some light on problem.
    I am interested in the first suggestion regarding if I am able to make
    a connection from within Excel. In th section of questions listed
    below.
    How would I go about finding if I have this capability listed below?

    Also, I would like to know where I could look up some sample code or
    find
    resources on the topics listed below.

    thanks very much

    > 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.
    b wreath, Jun 2, 2004
    #3
  4. b wreath

    Ben Morrow Guest

    Quoth (b wreath):
    > Ben Morrow <> wrote in message news:<c8apvd$238$>...
    > > Quoth (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.

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

    >
    > How would I go about finding if I have this capability listed [above]?


    Read the VBA documentation; ask in a VBA or Excel ng.

    Ben

    --
    If you put all the prophets, | You'd have so much more reason
    Mystics and saints | Than ever was born
    In one room together, | Out of all of the conflicts of time.
    The Levellers, 'Believers'
    Ben Morrow, Jun 2, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dpackwood
    Replies:
    3
    Views:
    1,767
  2. Jordan
    Replies:
    1
    Views:
    2,770
  3. sonic
    Replies:
    0
    Views:
    502
    sonic
    Jun 26, 2006
  4. Gary Chambers

    Perl Hacker, Python Initiate

    Gary Chambers, Feb 2, 2011, in forum: Python
    Replies:
    11
    Views:
    645
    Jorgen Grahn
    Feb 3, 2011
  5. NovasTaylor
    Replies:
    1
    Views:
    116
    NovasTaylor
    Aug 16, 2005
Loading...

Share This Page