使用 VBA 完全控制另一个程序 [英] Using VBA to control another program entirely

问题描述

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

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.

我环顾四周，发现 Shell 功能可以让您访问打开文件，应该可以帮助您控制它，但我还没有找到通过 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.

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

Chatillon Program, basically buttons to click with a mouse

推荐答案

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

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.

如果您有可能为您的程序获取一个 COM 包装器，那就这样做吧.

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

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

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.

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

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:

<代码>将 objWshell 调暗为对象设置 objWshell = CreateObject("WScript.Shell")

然后您可以将 Shell 对象声明为:<上一页><代码>将 objWshell 调暗为 IWshRuntimeLibrary.WshShell设置 objWshell = 新 IWshRuntimeLibrary.WshShell
You can then declare the Shell object as:

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

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

这是一个打开命令窗口并运行命令行可执行文件的示例，向其提供命令行开关-s"和封装在双引号中的参数.
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.
请注意，我正在运行的可执行文件不是regsvr32.exe"——我的 shell 对象正在执行 cmd.exe，它是 I/O 流的源和接收器.
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.
当然，您可以直接运行您的应用程序.它可能会起作用.但是，当您调用 .StdOut.ReadLine 或 .StdOut.ReadAll 时，输出流锁定或挂起"您的调用函数及其 VBA 线程是很常见的 - 您已收到警告.
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.
<代码>使用 objWshell.Exec("CMD/K")     .StdIn.WriteBlankLines 3    .StdIn.WriteLine "C:"    .StdIn.WriteLine "CD C:"     .StdIn.WriteLine "regsvr32.exe -s " &Chr(34) &"%LIBDIR%FXPricer.dll" &铬(34)    .StdIn.WriteBlankLines 1     直到 .StdOut.AtEndOfStream        Debug.Print.StdOut.ReadLine    循环    直到.StdErr.AtEndOfStream        Debug.Print.StdOut.ReadLine    循环    .终止结束于


                        
这篇关于使用 VBA 完全控制另一个程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！