perlexcelbatch-filemultiple-languagesvba

Passing Excel VBA variables to Perl?


Is it possible to pass variables to a perl script from excel without using Batch as a gateway (VBA -> Batch -> Perl), if not possible how can I send batch variables to Perl?


Solution

  • You can't pass variables to/between scripts, but you can pass values to/between processes.

    The number of ways of doing this is nearly infinite. The most common way to pass values from one process to another is through command-line arguments. This shows how to execute another program (such as a batch file) from VBA.

    What follows is an example of such a batch file passing arguments to a Perl script, which in turn prints them.

    >type a.bat
    @echo off
    echo Start of batch file
    perl a.pl %*
    echo End of batch file
    

    >type a.pl
    use strict;
    use warnings;
    use feature qw( say );
    say 'Start of Perl script';
    say for 0+@ARGV, @ARGV;
    say 'End of Perl script';
    

    >a.bat abc "123 456" def
    Start of batch file
    Start of Perl script
    3
    abc
    123 456
    def
    End of Perl script
    End of batch file