Using VBA to control another program completely

Question

I am currently working on simplifying the process at work. It includes the Chatillon DFIS Strength Meter, which uses a serial connection to transmit data. Data is transferred to the Chattillon program as text and can only be saved as a .dat file. I am trying to set up an Excel workbook that can simply automatically open a program and have different commands to put information directly into Excel. Teams will include changing units, zeroing the sensor, and transmitting.

I looked around a bit and found that the Shell function gives you access to open a file and should help you control it, but I did not find a way to call and manage the program through Excel. If anyone can help on this, that would be greatly appreciated.

Thanks in advance!

Nat

Chatillon program , mainly mouse click buttons

Answer 1

Excel and VBA can manage external applications if they have a COM interface, that is, if you can declare the application as an object, instantiate the object and view its methods and attributes.

If you can get a COM shell for your program, do it this way.

If you can’t ... You won’t like to do this using input / output streams and the Windows Shell object, because the DOS interfaces on the command line are not particularly convenient as a user interface, and they are more fragile than a break dance in a baking factory, when you try to use them as an API in VBA.

First, you need the "WshShell" object opened by the Windows Script operating system. You can declare and instantiate after late binding, as shown:

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

But the right method (which will give you Intellisense tooltips for properties and methods) is to use the "Tools: Links ..." dialog to create a link to the parent library, which is usually located in C: \ Windows \ System32 \ wshom.ocx

You can then declare a Shell object as follows:

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

Run command line executable and read I / O streams in VBA

:

This is an example that opens a command window and launches a command-line executable, giving it the command line switch '-s' and the option enclosed in double quotes.

Please note that the executable that I run is NOT 'regsvr32.exe' - my shell object executes cmd.exe, and it is the source and destination of the I / O streams.

You can, of course, run the application directly. It might work. But very often for the output stream, your calling function and its VBA thread block or hang when you call .StdOut.ReadLine or .StdOut.ReadAll - you were 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"  Chr (34)   .StdIn.WriteBlankLines 1  Do  .StdOut.AtEndOfStream    Debug.Print.StdOut.ReadLine     Do  .StdErr.AtEndOfStream    Debug.Print.StdOut.ReadLine     .Terminate   

Share and enjoy. And, as always, pay attention to line breaks inserted by your browser (or the StackOverflow text box interface) into the source code samples.