使用 VBA 完全控制另一个程序

Using VBA to control another program entirely

提问by ncs4180

I'm currently working on simplifying a process at work. It involves a Chatillon DFIS Force Meter which uses a serial connection to transmit data. The data gets sent to the Chattillon program as text and can only be saved as a .dat file. I'm trying to set up an Excel workbook that can just automatically open the program and have different commands to put the information straight into Excel. The Commands would involve changing the units, zeroing the sensor, and transmitting.

我目前正在努力简化工作流程。它涉及使用串行连接传输数据的 Chatillon DFIS 力计。数据以文本形式发送到 Chattillon 程序，并且只能保存为 .dat 文件。我正在尝试设置一个 Excel 工作簿，它可以自动打开程序并使用不同的命令将信息直接放入 Excel。命令将涉及更改单位、将传感器归零和传输。

I've done some looking around and found that the Shell feature gives you access to opening the file and should help allow you to control it but I haven't found a way to call and manipulate the program through Excel. If anyone could help with this issue that'd be greatly appreciated.

我环顾四周，发现 Shell 功能可让您访问打开文件，并且应该可以帮助您控制它，但我还没有找到通过 Excel 调用和操作程序的方法。如果有人可以帮助解决这个问题，将不胜感激。

Thanks in advance!

提前致谢！

Nate

内特

Chatillon Program, basically buttons to click with a mouse

Chatillon 程序，基本上是用鼠标单击的按钮

回答by Nigel Heffernan

Excel and VBA can control external applications if they have a COM interface- that is to say, if you can declare the application as an object, create an instance of the object, and see its methods and attributes.

如果 Excel 和 VBA具有 COM 接口，则它们可以控制外部应用程序- 也就是说，如果您可以将应用程序声明为对象，创建该对象的实例，并查看其方法和属性。

If you can possibly get hold of a COM wrapper for your program, do it that way.

如果您可以为您的程序获得一个 COM 包装器，那么就这样做。

If you can't... You won't enjoy doing it using I/O streams and a Windows Shell object, because command-line DOS interfaces aren't particularly friendly as a User Interface, and they are flakier than breakdancing in a pastry factory when you try to use them as an API in VBA.

如果你不能......你不会喜欢使用 I/O 流和 Windows Shell 对象来做这件事，因为命令行 DOS 界面作为用户界面并不是特别友好，而且它们比街舞中的霹雳舞更脆弱当您尝试将它们用作 VBA 中的 API 时，糕点工厂。

Firstly, you need the 'WshShell' object exposed by the Windows Script Host Object Model. You can declare and instantiate it by late binding as shown:

首先，您需要 Windows 脚本宿主对象模型公开的“WshShell”对象。您可以通过后期绑定声明和实例化它，如下所示：

Dim objWshell As Object
Set objWshell = CreateObject("WScript.Shell")

但是正确的方法（它将为您提供属性和方法的 Intellisense 下拉列表）是使用“工具：引用...”对话框创建对父库的引用，该引用通常位于 C:\Windows \System32\wshom.ocx

You can then declare the Shell object as:

然后，您可以将 Shell 对象声明为：

Dim objWshell As IWshRuntimeLibrary.WshShell
Set objWshell = New IWshRuntimeLibrary.WshShell

Running a command-line executable and reading the I/O streams in VBA

在 VBA 中运行命令行可执行文件并读取 I/O 流

：

This is an example that opens a command window and runs a command-line executable, feeding it a command-line switch '-s' and a parameter encapsulated in double quotes.

这是一个打开命令窗口并运行命令行可执行文件的示例，为其提供命令行开关“-s”和封装在双引号中的参数。

Note that the executable I'm running is NOT 'regsvr32.exe' - my shell object is executing cmd.exe, and that is the source and sink of the I/O streams.

请注意，我正在运行的可执行文件不是“regsvr32.exe”——我的 shell 对象正在执行 cmd.exe，它是 I/O 流的源和接收器。

You can, of course, run your application directly. It might work. But it is very common for the output stream to lock or 'hang' your calling function and its VBA thread when you call .StdOut.ReadLine or .StdOut.ReadAll - you have been warned.

当然，您可以直接运行您的应用程序。它可能会起作用。但是，当您调用 .StdOut.ReadLine 或 .StdOut.ReadAll 时，输出流锁定或“挂起”您的调用函数及其 VBA 线程是很常见的 - 您已被警告。

With objWshell.Exec("CMD /K")

  

       .StdIn.WriteBlankLines 3

    .StdIn.WriteLine "C:"

    .StdIn.WriteLine "CD C:\"

 

    .StdIn.WriteLine "regsvr32.exe -s " & Chr(34) & "%LIBDIR%\FXPricer.dll" & Chr(34)

    .StdIn.WriteBlankLines 1

 

    Do Until .StdOut.AtEndOfStream

        Debug.Print .StdOut.ReadLine

    Loop


     Do Until .StdErr.AtEndOfStream

        Debug.Print .StdOut.ReadLine

    Loop


    .Terminate


End With

分享和享受。而且，与往常一样，请注意源代码示例中由浏览器（或 StackOverflow 的文本框界面）插入的换行符。