使用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")Dim objWshell As Object Set objWshell = CreateObject("WScript.Shell")然后可以将Shell对象声明为:
将objWshell变暗为IWshRuntimeLibrary.WshShell 设置objWshell = New IWshRuntimeLibrary.WshShellYou 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'-我的外壳程序对象正在执行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 环路 ;.终止 结束于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这篇关于使用VBA完全控制另一个程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
