How do I use VBA to open Console App and then enter data?

Question

I have made a simple console app that takes data entered into it, transforms the data into the correct format, and submits it to an online database. The console app just needs the data entered into it, and it will do the rest.

For example, I would type something like this: "Description text [Enter]", "Customer name [Enter]", etc., etc.

I have several Excel workbooks that I would like to use this app. But I don't know how to use VBA to do the following:

1. Run the Console App
2. Enter text into the console app from VBA for Excel.

How do I do this? Do I need to add additional references for Excel? Is this possible? If this isn't possible, what would you recommend doing instead?

Thank you in advance! This community has really helped so far.

Answer 1

Ideally, your console app would take arguments instead of reading lines. Then you could simply invoke your executable from VBA as explained here.

If you can't modify the console app, you can work around this by first creating a temporary text file using VBA (numerous examples throughout this site). This file would contain the inputs you'd normally provide from the keyboard. Then, you'd invoke your executable as described in the linked answer, but instead of providing parameters, you'd use the < character to feed the contents of the temporary text file to the program:

Public Sub StartExeWithInputFile()
    Dim strProgramName As String
    Dim strInputFile As String

    strProgramName = "C:\Program Files\Test\foobar.exe"
    strInputFile = "C:\Data\TempInputLines.txt" '<== You have to produce this file.

    Call Shell("""" & strProgramName & """ <""" & strInputFile & """", vbNormalFocus)
End Sub

EDIT Here's an example for creating the temporary input file mentioned above:

Sub CreateTempInputLines()
    Dim fso As Object
    Dim oFile As Object

    Set fso = CreateObject("Scripting.FileSystemObject")

    'See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/createtextfile-method.
    Set oFile = fso.CreateTextFile("C:\Data\TempInputLines.txt", True)

    oFile.WriteLine "Description text"
    oFile.WriteLine "Customer name"

    'Cleanup.
    oFile.Close
    Set oFile = Nothing
    Set fso = Nothing
End Sub