使用 excel 2010 更新批處理文件中的變量 (use excel 2010 to update variables in batch file)

問題描述

使用 excel 2010 更新批處理文件中的變量 (use excel 2010 to update variables in batch file)

我正在嘗試使用在 perl 中運行代碼的 batch 文件中使用的 excel 2010 vba 更新變量。下面是更新每個變量的 VBA 和沒有變量的 perl 代碼是有效的代碼。使用變量，批處理文件會快速打開和關閉，因此語法似乎是錯誤的。即使我刪除 @echo off 我也無法查看錯誤。謝謝 :)。

VBA

'Update values and call perl
Dim var1 As String
Dim var2 As String
Dim var3 As String
Dim var4 As String

var1 = MyDirectory
var2 = MyDirectory & "sample_descriptor.txt"
var3 = "C:\cygwin\home\cmccabe\test_probes8.txt"
var4 = MyDirectory & "output.txt"
Set wshell = CreateObject("wscript.shell")
wshell.Run Chr(34) & "C:\Users\cmccabe\Desktop\EmArray\Design\spikein.bat"

perl 代碼（無變量）

perl get_imagene_spikein_probe_values.pl "N:\1_DATA\MicroArray\NexusData\555_7‑2‑2015" "sample_descriptor.txt" < test_probes8.txt > "N:\1_DATA\MicroArray\NexusData\555_7‑2‑2015\output.txt"

perl 代碼（帶變量）

perl get_imagene_spikein_probe_values.pl "%var1%" "%var2%" < %var3% > "%var4%"

批處理文件

參考解法

方法 1:

Your code fails because it confuses VBA local variables with Windows environment variables. The variables that you are setting in your VBA macro are not visible to the batch file, because the batch file is totally unaware of the VBA environment.

You could fix this (for example by making system calls in the VBA script that set environment variables); however, the bigger problem is that your design makes no sense. There is no reason to involve VBA and a batch file in the task of running a Perl script, and doing so adds unnecessary complexity.

As others have suggested, why not simply use Perl for the task?

If your Windows users are not comfortable with command line arguments, simply make the script prompt for each file name that is needed. I would also make your script work with input and output files directly, rather than requiring STDIN and STDOUT to be redirected. If you do this, then you could create a shortcut for your script and have a very easy way of accessing it.

If some users will want to use the script on the command line, you can accept both modes of input:

my $directory;
my $descriptor;
my $input_file;
my $output_file;

#
if (@ARGV == 4)
{
    $directory = $ARGV[0];
    $descriptor = $ARGV[1];
    ...etc.
}
else
{
    print "Enter directory name: "
    $directory = <>;
    chomp $directory;  #remove newline
    ...etc.
}

(by justaguy、user1919238)

參考文件

1. use excel 2010 to update variables in batch file (CC BY‑SA 2.5/3.0/4.0)