Huibert
09-19-2019, 05:07 AM
Hi to all,
I am an engineer and not a programmer, but I don't like to do things repetively, so I learned some programming by searching forums like this. But in this instance I can't find my solution.
The problem should be simple to solve imho, but yet it's solution keeps eluding me.
I have a program that solves (a specific) higher order differential equation, this is an executable (.exe). This executable is nothing more than a C++ script that opens cmd, get's some input from 3 .dat files, performs it's magic, generates 3 output .txt files, and waits for a keystroke to terminate cmd.
My idea is to have an excelsheet that containts serveral input variables (up to 200 cases). Within this excel i wrote a VBA routine that loops through all the seperate cases; generate the required input .dat files; run the executable; closes the cmd by a forced keystroke; read the output files; and finally store the important results back into excel.
ps. both the excelworkbook, the executable and the input and output files are currently in the same folder
Everything works like a charm,..... as long as I have all the required files in the default cmd folder. C:\users\name.
As soon as this is not the case the .exe script won't read the input .dat files and my idea comes to a halt.
So the question:
how can I set the cmd to a temporary default map, or how can I append the shell command so that the .exe starts looking into the current filepath for it's input.
I have posted my code that handles a single instance, the porblem is in the -4 line from the end.
Sub Stokes_Solver()'' all values have to be generalized
LATmin = Excel.ActiveWorkbook.Sheets("wave data").Range("L19").Value
surge = Excel.ActiveWorkbook.Sheets("wave data").Range("J26").Value
H = Excel.ActiveWorkbook.Sheets("wave data").Range("D30").Value
T = Excel.ActiveWorkbook.Sheets("wave data").Range("D31").Value
d = LATmin - surge
Hgen = Round(H / d, 4)
Tgen = Round(T * Sqr(9.81 / d), 4)
'' writing the data into a string
datastr = "Automated output from Excel VBA script" & vbNewLine _
& Hgen & vbNewLine _
& "Period" & vbNewLine _
& Tgen & vbNewLine _
& 1 & vbNewLine _
& 0 & vbNewLine _
& 20 & vbNewLine _
& 1 & vbNewLine & "FINISH"
datastr = Replace(datastr, ",", ".")
'write the generated string into a data.dat file in the same folder as the workbook
Filepath = Application.ThisWorkbook.Path
Filename = "Data.dat"
Open Filepath & "\" & Filename For Output As #1
Print #1, datastr
Close #1
' Application.Wait (Now + 0.00003)
'run the .exe file in the filepath location
Filepath = Application.ThisWorkbook.Path
testcase = Shell(Filepath & "\solver.exe", vbNormalFocus)
Application.Wait (Now + 0.00002)
SendKeys "1", True
End Sub
I am an engineer and not a programmer, but I don't like to do things repetively, so I learned some programming by searching forums like this. But in this instance I can't find my solution.
The problem should be simple to solve imho, but yet it's solution keeps eluding me.
I have a program that solves (a specific) higher order differential equation, this is an executable (.exe). This executable is nothing more than a C++ script that opens cmd, get's some input from 3 .dat files, performs it's magic, generates 3 output .txt files, and waits for a keystroke to terminate cmd.
My idea is to have an excelsheet that containts serveral input variables (up to 200 cases). Within this excel i wrote a VBA routine that loops through all the seperate cases; generate the required input .dat files; run the executable; closes the cmd by a forced keystroke; read the output files; and finally store the important results back into excel.
ps. both the excelworkbook, the executable and the input and output files are currently in the same folder
Everything works like a charm,..... as long as I have all the required files in the default cmd folder. C:\users\name.
As soon as this is not the case the .exe script won't read the input .dat files and my idea comes to a halt.
So the question:
how can I set the cmd to a temporary default map, or how can I append the shell command so that the .exe starts looking into the current filepath for it's input.
I have posted my code that handles a single instance, the porblem is in the -4 line from the end.
Sub Stokes_Solver()'' all values have to be generalized
LATmin = Excel.ActiveWorkbook.Sheets("wave data").Range("L19").Value
surge = Excel.ActiveWorkbook.Sheets("wave data").Range("J26").Value
H = Excel.ActiveWorkbook.Sheets("wave data").Range("D30").Value
T = Excel.ActiveWorkbook.Sheets("wave data").Range("D31").Value
d = LATmin - surge
Hgen = Round(H / d, 4)
Tgen = Round(T * Sqr(9.81 / d), 4)
'' writing the data into a string
datastr = "Automated output from Excel VBA script" & vbNewLine _
& Hgen & vbNewLine _
& "Period" & vbNewLine _
& Tgen & vbNewLine _
& 1 & vbNewLine _
& 0 & vbNewLine _
& 20 & vbNewLine _
& 1 & vbNewLine & "FINISH"
datastr = Replace(datastr, ",", ".")
'write the generated string into a data.dat file in the same folder as the workbook
Filepath = Application.ThisWorkbook.Path
Filename = "Data.dat"
Open Filepath & "\" & Filename For Output As #1
Print #1, datastr
Close #1
' Application.Wait (Now + 0.00003)
'run the .exe file in the filepath location
Filepath = Application.ThisWorkbook.Path
testcase = Shell(Filepath & "\solver.exe", vbNormalFocus)
Application.Wait (Now + 0.00002)
SendKeys "1", True
End Sub