Getting shell cmd output to Excel

Question

I have a macro in VBA (Excel 2007).
It opens an exe file with entering a HEX value as variable.
The exe gives the output (also a HEX number).
I do everything with "shell" command and the results is saved to a txt file. Then I write this to Excel.

retVal = Shell("cmd.exe /c C:\AABB\app.exe 0x5110 > C:\AABB\output.txt", vbNormalFocus)

It is complicated and time-consuming.
I would prefer getting the result directly to Excel, without an intermediate file like txt or similar.

When I use an output.xlsx as output destination, the file is created and the value is written. But I cant read it with Excel. I see the value when I open the xlsx with Notepad.

My questions are:

1) Is it possible to write the result directly to xlsx, especially a target cell e.g. A10

2) Why when I use xlsx as destination in shell command, I can't open it with Excel? It gives Error Message of "file-format or file-extension is not valid. Data might be corrupted".

Is this of any help to you? http://stackoverflow.com/questions/16240070/can-an-excel-macro-pull-output-from-a-command-line-directly-into-a-sheet — Teknix1982, Oct 16 '14 at 10:54
When you use "output.xlsx" as destination file it does not mean that the app.exe knows how to write an xlsx file. The app must support output in different formats otherwise it always will write the values in plain text no matter what extension you use for the destination. — Miguel Febres, Oct 16 '14 at 11:19

score 0 · Answer 1 · edited Aug 17 '17 at 10:27

I think you can't do that with shell object. you can do it with WSHExec with the function StdOut.ReadLine(). You have to go to reference and choose "Windows Script Host object model" so you can declare a WshExec object. than see the Method yourWshExecObject.StdOut.ReadLine().

To construct WshExec : First declare a WshShell Object and you construct it like that :

Dim WshShellObject as WshShell
Dim WshExecObject as WshExec

Set WshShellObject = New WshShell

Set WshExecObject = WshShellObject.Exec("your .exe filename").

The WshExecObject.StdOut TextStream will read everything you write in the console.
I did it with an .exe compilated with C++.

You can also use WshExecObject.StdOut.ReadAll to read all lines at once.

Hope that helps.

Getting shell cmd output to Excel

1 Answers1