0

How do i program in excel VBA to run an external program with the external program being able to output the data collected into a file? My external program, voltage.exe, when run normally (double click the program on the desktop screen) will output the data collected into a file data.txt. However when run with my code below, the file data.txt was not being created.

Sub Button1_Click() ' run logger
Dim path As String
path = ActiveWorkbook.path
path = path + "\Voltage Recording.exe"
retval = Shell(path, vbNormalFocus)
End Sub
Community
  • 1
  • 1
Deckdyl
  • 103
  • 1
  • 2
  • 12
  • Think your program will be launched with a current working directory of the caller, in this case Excell. I'm unsure the Shell command set the CWD to the workbook path, you may use `Shell("c:\windows\system32\cmd.exe", vbNormalFocus)` to see where the shell starts and confirm your file was created there for exemple. – Tensibai Dec 31 '14 at 10:19
  • `Shell` does not wait for the called program to finish before returning. Is "data.txt" never created or just not by the time your macro attempts to read it? – Tony Dallimore Dec 31 '14 at 10:32
  • @TonyDallimore data.txt was never created. – Deckdyl Dec 31 '14 at 10:35
  • @Tensibai I tried your method and it worked, but i am having trouble using VBA to enter commands into the cmd i called to run my program. – Deckdyl Dec 31 '14 at 10:35
  • The method to send something into it is `app = Shell("app",vbNormalFocus,1)` (the 1 at third parameter to tell Sheel to wait) and then use `SendKeys "what to send"`. But the idea was more to help you see where it starts the command and so where your file may have been created. See [this question](http://stackoverflow.com/questions/17956651/execute-a-command-in-command-prompt-using-excel-vba) for detailled exemple. So you may change directory and then execute your command to be sure where your data.txt file will be created. – Tensibai Dec 31 '14 at 10:42

1 Answers1

2

your file is created in the current directory, that you can get its path by function: CurDir() you can change the current directory by calling SetCurrentDirectory function:

Declare Function SetCurrentDirectory Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As Long
Sub Button8_Click()
    MsgBox ("Old Dir = " & CurDir())
    SetCurrentDirectory ActiveWorkbook.path
    MsgBox ("Current Dir = " & CurDir())
    Dim path As String
    path = ActiveWorkbook.path

    path = path + "\Voltage Recording.exe"
    MsgBox (path)
    retval = Shell(path, vbNormalFocus)

End Sub
houssam
  • 1,823
  • 15
  • 27