如何将Excel中的VBA脚本中的参数作为参数传递给外部可执行文件(C#)? [英] How do you pass parameters from a VBA script in excel to an external executable (C#) as arguments?

查看:276
本文介绍了如何将Excel中的VBA脚本中的参数作为参数传递给外部可执行文件(C#)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在excel工作表中有一个嵌入式按钮,该按钮可以调用VBA脚本.在此脚本中,我正在读取当前目录,进行解析,并使用该目录生成值以将string [] args传递给外部C#可执行文件.我经历了很多迭代,并且确实调用了可执行文件,但是当C#.exe运行时,似乎传递的参数为null(空).我可以在其他程序中使用此C#.exe并传递参数,但是此VBA脚本无法正常工作.我也知道它正在传递正确的参数#,因为我没有从C#.exe中获得超出范围的异常.我目前正在将所有这些参数都转换为字符串,以尝试解决此问题,但这很可能不是必需的.随附的VBA代码:

I have an embedded button in an excel sheet that calls a VBA script. In this script I am reading the current directory, parsing, and using that to generate values to pass string[] args to an external C# executable. I have gone through many iterations of this and it does call the executable, but it appears that the parameters being passed are null(empty) when the C# .exe runs. I am able to use this C# .exe in other programs and pass the arguments, but this VBA script isn't working as expected. I also know that it is passing the correct # of parameters as I don't get an out of bounds exception from the C# .exe. I am currently converting all these arguments into strings in an attempt to troubleshoot this, but that most likely isn't necessary. VBA Code attached:

Sub ButtonSG1b7_Click()

Dim FileLocation
Dim ProgramName
Dim length

FileLocation = ActiveWorkbook.FullName
length = Len(FileLocation) - 5
ProgramName = Left(FileLocation, length)
ProgramName = Right(ProgramName, 10)
length = Len(FileLocation) - 15
FileLocation = Left(FileLocation, length)
length = Len(FileLocation) - 2
FileLocation = Right(FileLocation, length)

MsgBox "File Location : " & FileLocation & "    Program Name: " & ProgramName

Dim str0 As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String

str0 = "H:\StageGate\Administration\Scripts\GetLatestFileOpen.exe "
str1 = "H:"
str2 = FileLocation
str3 = "Common"
str4 = "\Market Feasibility "
str5 = ProgramName
str6 = ".xlsx"


MsgBox str0 & str1 & str2 & str3 & str4 & str5 & str6
Shell (str0 & str1 & str2 & str3 & str4 & str5 & str6)

End Sub

编辑(添加涉及SolidWorks EPDM库的prelim C#代码,该代码接受参数,将其转换为列表,并使用该列表提供字符串文件路径以在EPDM库中的文件夹上获取最新信息,然后打开更新的本地副本的文件).

Edit (adding prelim C# code involving Solidworks EPDM library that takes the arguments, turns it into a list, and uses the list to provide a string filepath to Get Latest on a folder in an EPDM vault and then open the updated local copy of the file.) :

using System;
using System.Diagnostics;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using EPDM.Interop.epdm;

class Program
{
static void Main(string[] args)
{

    IEdmFolder5 ppoRetParentFolder;
    List<string> filePath = new List<string>(args);

    if (filePath.Any())
    {
        filePath.RemoveAt(0);  //really need to just stop having the script call passing this argument.
    }


    if (Directory.Exists(@"C:\StageGate")) {
        filePath.Insert(0,"C:");
    }
    else if (Directory.Exists(@"H:\StageGate"))
    {
        filePath.Insert(0,"H:");
    }
    else
    {
        MessageBox.Show("StageGate not found.");
    }

    string newFilePath = string.Join("", filePath.ToArray());
    filePath.RemoveAt(5);
    filePath.RemoveAt(4);
    filePath.RemoveAt(3);
    string folderPathstr = string.Join("", filePath.ToArray());
    //MessageBox.Show(folderPathstr);

    //Have to create vault object to work with BatchGet
    EdmVault5 vault = new EdmVault5();
    //Replace My_Vault with your vault name
    vault.LoginAuto("StageGate", 0);
    //Set the 2 here equal to how many folders you want to get (not counting subfolders)
    EdmSelItem[] folderArray = new EdmSelItem[1];
    IEdmBatchGet bg = (IEdmBatchGet)vault.CreateUtility(EdmUtility.EdmUtil_BatchGet);

    //Create and array element for eachf older you want to get, replace folder locations with your folders
    folderArray[0].mlDocID = 0;
    folderArray[0].mlProjID = vault.GetFolderFromPath(folderPathstr).ID;
    //fa[1].mlDocID = 0;
    //fa[1].mlProjID = vault.GetFolderFromPath("C:\\My_Vault\\FolderPath").ID;

    bg.AddSelection(vault, folderArray);
    bg.CreateTree(0, (int)EdmGetCmdFlags.Egcf_IncludeAutoCacheFiles);  //Egcf_IncludeAutoCacheFiles will get latest version of file
    bg.GetFiles(0, null);

    if (File.Exists(newFilePath))
    {

        Process process = Process.Start(newFilePath); //can't just open the file.  Need to create new windows process to have the file open in the default application(process)
        //File.Open(newPath, FileMode.Open);
    }
    else
    {
        MessageBox.Show("File not found.");
    } 

推荐答案

阅读C#代码后,我看到您的代码至少需要6个参数

After reading your C# code, I see your code expects at least 6 arguments

此VBA代码转义了路径中可能出现的所有空格,并将它们作为命令行参数传递给了

This VBA code escapes any spaces that might appear in your path and passes them as command-line arguments:

Sub ButtonSG1b7_Click()

Dim FileLocation
Dim ProgramName
Dim length

FileLocation = ActiveWorkbook.FullName
length = Len(FileLocation) - 5
ProgramName = Left(FileLocation, length)
ProgramName = Right(ProgramName, 10)
length = Len(FileLocation) - 15
FileLocation = Left(FileLocation, length)
length = Len(FileLocation) - 2
FileLocation = Right(FileLocation, length)

MsgBox "File Location : " & FileLocation & "    Program Name: " & ProgramName

Dim args(0 To 6) As String
Dim cmdln As String, i as Integer

args(0) = "H:\StageGate\Administration\Scripts\GetLatestFileOpen.exe"
args(1) = "H:"
args(2) = FileLocation
args(3) = "Common"
args(4) = "\Market Feasibility "
args(5) = ProgramName
args(6) = ".xlsx"

cmdln=arg(0)
For i = 1 To 6
cmdln=cmdln & " """ & args(i) & """"
Next i
MsgBox "VBA Code Writes: " & cmdln
Shell (cmdln)

End Sub

现在,您的C#代码应读取以下参数:

Now your c# code should read those parameters:

class Program
{
static void Main(string[] args)
{
Console.WriteLine("C# Code Reads: "+String.Join(" ", args));
}
}

如果2个过程返回相同的命令行字符串,则您在以下各行中可能遇到的任何异常都与VBA-C#通信无关

If the 2 proccesses return the same command-line string, then any exceptions you may encounter in the following lines are not related to the VBA-C# communications

这篇关于如何将Excel中的VBA脚本中的参数作为参数传递给外部可执行文件(C#)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆