Solved: Using the MS Run Command in a VBS Application

[Tom]

Okay, this is the problem?I?m trying to make a VBA application include a FTP import/export piece that will require NO action on the part of the user except to hit a button. I?ve got it down to one line of code that connects with the remote site through as established profile in the FTP application (WS_FTP Professional). Now, if you push the ?START? button at the bottom of your screen, then select RUN and enter ?wsftppro -s BENEF:/home/in/KPay.txt -d local:c:\? it runs totally behind the curtain. My question is?How do I incorporate the START and RUN commands into the VBA code so I can run this one line? It?s not a BAT file and it?s not a SCP file either. SO?anybody know how I can pull this off?:banghead:

[Oorang]

Hi Tom,
See if this will work for you.
Option Explicit
Public Sub TestCommandLine()
Const lngCancelled_c As Long = 0
Dim strCmd As String
strCmd = VBA.InputBox("Enter DOS Command:", "Enter Command", "dir")
If VBA.LenB(strCmd) = lngCancelled_c Then
Exit Sub
End If
CommandLine strCmd, True
End Sub
Public Sub CommandLine(command As String, Optional keepAlive As Boolean = False, _
Optional windowState As VbAppWinStyle = VbAppWinStyle.vbHide)
'---------------------------------------------------------------------------------
' Procedure : CommandLine
' Author : Aaron Bush (Oorang)
' Date : 10/2/2007
' Purpose : Provides a simple interface to execute a command lines from VBA.
'---------------------------------------------------------------------------------
On Error GoTo Err_Hnd
Const lngMatch_c As Long = 0
Const strCMD_c As String = "cmd.exe"
Const strComSpec_c As String = "COMSPEC"
Const strTerminate_c As String = " /c "
Const strKeepAlive_c As String = " /k "
Dim strCmdPath As String
Dim strCmdSwtch As String
If keepAlive Then
If windowState = vbHide Then
windowState = vbNormalFocus
End If
strCmdSwtch = strKeepAlive_c
Else
strCmdSwtch = strTerminate_c
End If
strCmdPath = VBA.Environ$(strComSpec_c)
If VBA.StrComp(VBA.Right$(strCmdPath, 7), strCMD_c, vbTextCompare) <> _
lngMatch_c Then
strCmdSwtch = vbNullString
End If
VBA.Shell strCmdPath & strCmdSwtch & command, windowState
Exit Sub
Err_Hnd:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CommandLine of Module Module1"
End Sub

[Tom]

Won't work (but thanks for the input). The problem is that is you try to run the line from a DOS prompt, you'll get "wsfppro" is not a command. You can run it from the "Run" line (as in START>RUN>code line>ENTER) but not from a DOs line. Can you access the RUN utility with something like the you can use to run a string (example: like following an input string statement with "Application.Followhyperlink StrInput, ,TRUE")?

[Oorang]

Just prefix the command with "Start " (Start wsftppro -s BENEF:/home/in/KPay.txt -d local:c:\) It will run it as if from the run-box.

[Oorang]

You know Tom, we might be overengineering this... All you really need to do is shell straight to the exectuable. You need to know the path (but I think it's: C:\Program Files\WS_FTP Pro\wsftppro.exe) but that's about it. Try this:
VBA.Shell "C:\Program Files\WS_FTP Pro\wsftppro.exe -s BENEF:/home/in/KPay.txt -d local:c:\"
That should do it. I don't know what I was thinking:)

[Tom]

Oorang,
You got it, Amigo! I just had to tweek it a little for my system to run it! I just added "Call Shell to the front and lost the "VBA" and added 2 parentheses (sp?) to make it:


Call Shell ("C:\Program Files\WS_FTP Pro\wsftppro.exe -s BENEF:/home/in/KPay.txt -d local:c:\" )

And it works fantastic! Thanks, Man, for the info! I owe you a cup of coffee!