Shell( .exe with input files)

[Huibert]

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

[Kenneth Hobs]

If you need to check for the files existing Dir() or fso can be used.

You might try this without the SendKeys():

Shell "cmd /c " & """" & Filepath & "\solver.exe" & """", vbNormalFocus)

There is ShellExecuteAndWait() program that can be used for kind of thing too. http://www.cpearson.com/excel/ShellAndWait.aspx

[snb]

Waarom maak je de berekening niet in Excel/VBA zelf ?
Een beetje minder code kan ook.

Sub Stokes_Solver()
  Open ThisWorkbook.Path & "\Data.dat" For Output As #1
    with sheets("Wave data")
      Print #1, replace(join(array("Automated output from Excel VBA script",round(.cells(30,4)/(.cells(19,11)-.cells(26,10)),4),"Period",Round(.cells(31,4) * Sqr(9.81 /(.cells(19,11)-.cells(26,10)), 4),1,0,20,1,"FINISH"),vblf),",",".")
    end with
  Close

  shell "cmd /c " & ThisWorkbook.Path & "\solver.exe",0
End Sub

[Huibert]

het verschil tussen wat proberen en weten wat je doet.

Stokes 5de orde differentiaal is me nog gelukt (onder randvoorwaarden, met hulp). Maar dit script doet het ook tot de 20ste orde als dat nodig is. Mijn kennis van programmeren (C++ => VBA) en 20ste orde differentiaalvergelijkingen is te beperkt om dat 'even' om te zetten.

bedankt.

[Huibert]

It seem this solved the problem, thanks.

[Huibert]

Well it didn't solve the problem....

the strange thing is that this code does something (although strange). I runs the program fourier.exe, in the filepath location. But it will get it's imput from what appears to be a default path of cmd: "C:\users\huibert\documents" in my case. (but something different on a different PC).

Shell "cmd /k " & """" & ThisWorkbook.Path & "\Fourier.exe" & """", vbNormalFocus

This code gives runtime error 5. while it should behave similarly to the code above without the cmd window popping up (as far as my knowlegde goes)

Shell "cmd /c " & """" & ThisWorkbook.Path & "\Fourier.exe" & """", 0

It seems I have to somehow change the default path, define the path for the input files and/or path for the output files.......... but how?

[snb]

If your pathnames contain spaces, it's hard to get rid of them.
Begin with removing all spaces in pathnames.
After that this will suffice

Shell replace("cmd /c ~\Fourier.exe","~",Thisworkbook.path), 0

[Huibert]

Your suggestion runs if I change "cmd /c" to "cmd /k" (it gives a runtime error 5 if using "cmd c/")

but the problem of trying to get it's input from a different location is still a mistery, If I do the whole procedure manually it works only with all files in one folder, As soon as I script it the cmd will start to mess up pathlocation, in a yet for me unpredictable way.

i might have something to do with the fact that the executable also uses/opens the cmd to print some intermediate results.

If I run the suggested script with vbfocusnormal, I end up with cmb on the filepath that contains the results. Unfortunately the program only produces results if this specific filepath (that's shown visually in the cmd after I succesfully run my script) contained the data.dat file to begin with.

runing the script as a test case to get this filepath and then run in again with the data.dat file in that filepath might work, but a bit clumsy/inefficient.
(and I still need to know how I can read the current active filepath from the cmd)

(ps. there are no space in the filepath atm. but thanks for the future warning)

[Huibert]

Okay just pass the answer to those who have a comparible question:

   ChDir ThisWorkbook.Path  'this was my main problem
    Shell """" & ThisWorkbook.Path & "\Fourier.exe" & """", vbNormalFocus    'because the .exe file opened the cmd upon completion the "cmd /c" generated errors, while cmd /k left me with an open cmd
    Application.Wait (Now + 0.00002)   'just give the script some time, I know ShellandWait() but this works for now.
    SendKeys "{enter}"                        'the .exe ends with a request for a keystroke throught cmd to notify the user it has done it's job.

Everyone thanks for the help.

ps. how do I set this thread to [solved]?

[Kenneth Hobs]

http://www.vbaexpress.com/forum/faq...._new_faq_item2