Following on from the previous post (running a compiled Fortran finite element analysis program from Excel), this post looks at the details of calling any command line routine from Excel, through VBA.  The important points that need to be addressed are:

• Writing any required data files
• Calling the program from the required directory, including any required arguments
• Ensuring that the VBA code waits until the external routine has finished before proceeding.
• Reading the output files, and transferring the required data to Excel, in the correct format

The PlateMC-exe spreadsheet uses the code below to write the data file:

Sub P64exe()
Dim P64Dat As Variant, FName As String, DatPath As String, i As Long, ExeFile As String, Res As Variant
Dim iErr As Variant, STime As Double, ResA() As Double, NumRows As Long, j As Long, Numcols As Long, k As Long, Off As Long
Dim DataA  As Variant, TolA As Variant, PropA As Variant, Nsrf As Long, SRFA As Variant, np_types As Long, Row As Long
Dim wsh As Object, waitOnReturn As Boolean, windowStyle As Integer

Const NProps As Long = 7, ExeName As String = "P64"
    STime = Timer

' Read data
    DataA = Range("Plate_Def").Value2
    TolA = Range("tolerance").Value2
    Nsrf = Int(TolA(1, 3))
    Range("srf").Resize(Nsrf, 2).Name = "srf"
    SRFA = Range("srf").Value2
    np_types = DataA(5, 1)

    Range("props").Resize(np_types, NProps).Name = "props"
    PropA = Range("props").Value2
    Numcols = NProps
    If Nsrf > NProps Then Numcols = Nsrf

    ReDim P64Dat(1 To 6 + np_types, 1 To Numcols)

    For i = 1 To 3
        P64Dat(1, i) = DataA(1, i)
    Next i
    For i = 4 To 5
        P64Dat(1, i) = DataA(2, i - 3)
    Next i
    P64Dat(2, 1) = DataA(3, 1)
    P64Dat(2, 2) = DataA(3, 2)
    P64Dat(2, 3) = DataA(4, 1)
    P64Dat(2, 4) = DataA(4, 2)
    P64Dat(3, 1) = DataA(5, 1)
    For i = 1 To np_types
        For j = 1 To NProps
            P64Dat(i + 3, j) = PropA(i, j)
        Next j
    Next i
    P64Dat(i + 3, 1) = TolA(1, 1)
    P64Dat(i + 3, 2) = TolA(1, 2)
    P64Dat(i + 4, 1) = Nsrf

    For j = 1 To Nsrf
    P64Dat(i + 5, j) = SRFA(j, 1)
    Next j

Note that:

• Data is read from four separate named ranges on the spreadsheet
• It is transferred to an array (P64Dat), with the data arranged in the format required by the external program (P64.exe)
• The array is then written to a text file (P64.Dat), in the same directory as the spreadsheet, with the code shown below (the commented out line shows an alternative of reading the required directory from a named worksheet range)

' Write data to text file
    DatPath = ThisWorkbook.Path & "\"
    ' DatPath = Range("datpath").Value2
    FName = DatPath & "P64.dat"
    iErr = PrinttoFile(P64Dat, FName)

The PrinttoFile function:

Function PrinttoFile(Dat As Variant, FileName As String)
Dim FNum As Long, TxtLine As String, i As Long, Row As Long, Numcols As Long

    If TypeName(Dat) = "Range" Then Dat = Dat.Value2
    Numcols = UBound(Dat, 2)
    On Error GoTo rtnerr
    FNum = FreeFile
    Open FileName For Output Access Write As FNum

    For Row = 1 To UBound(Dat)
        TxtLine = ""
        For i = 1 To Numcols
            If IsEmpty(Dat(Row, i)) = False Then
                TxtLine = TxtLine & Dat(Row, i) & "  "
            End If
        Next i
        Print #FNum, TxtLine
    Next Row
    Close FNum
    PrinttoFile = 0
    Exit Function
rtnerr:
    Close FNum
    PrinttoFile = Err.Description
End Function

P64.exe can then be called using the WScript.Shell object:

' Run P64.exe
    Set wsh = VBA.CreateObject("WScript.Shell")
    waitOnReturn = True
    windowStyle = 1
    ExeFile = "cmd /C  CD " & DatPath & "& " & ExeName & " p64"
    iErr = wsh.Run(ExeFile, windowStyle, waitOnReturn)

    If iErr <> 0 Then GoTo rtnerr

More details of the windowStyle options can be found at Shell Function

Information on using the scripting shell object is strangely scattered, but the format shown above succeeds in changing directory to the location specified in DatPath, then running the program with the argument p64.  Note that the variable ExeFile resolves to:

• cmd /C  CD  DatPath &  ExeName  p64

The “&” in the command line is required to combine the CD (change directory) command with the command to run the program named by ExeName.

With windowStyle set to 1 the program runs in a cmd window, as shown below:

When the external program is complete the command window closes and control returns to the VBA code, which can then read the results files, and return the required results to the spreadsheet:

' Read result files
    FName = DatPath & "P64.res"
    Res = ReadText(FName)
    Res = SplitText(Res, 5, , , , True)
    Range("res").ClearContents
    Range("res").Resize(5, UBound(Res)).Name = "res"
    Range("Res").Value2 = TranspV(Res)
    FName = DatPath & "P64.rs2"
    Res = ReadText(FName)
    Res = SplitText(Res, 3, , , , True)
    NumRows = UBound(Res) / Nsrf
    Numcols = Nsrf * 3
    ReDim ResA(1 To NumRows, 1 To Numcols)
    On Error Resume Next
    Off = 0
    Row = 2
    For i = 1 To Nsrf
        For j = 1 To NumRows
            For k = 1 To 3
                ResA(j, k + Off) = Res(Row, k)
            Next k
            Row = Row + 1
        Next j
        Off = Off + 3
    Next i
    Range("def").Resize(NumRows, Numcols).Name = "def"
    Range("def").Value2 = ResA

    FName = DatPath & "P64.msh"
    Res = ReadText(FName)
    Res = SplitText(Res, 2, , , , True)
    ReDim ResA(1 To UBound(Res), 1 To 2)
    For i = 1 To UBound(Res)
        ResA(i, 1) = CDbl(Res(i, 1))
        ResA(i, 2) = CDbl(Res(i, 2))
    Next i
    Range("g_coord").Resize(UBound(Res), 2).Name = "g_coord"
    Range("g_coord").Value2 = ResA

    Exit Sub
rtnerr:

    End Sub