Hi guys

im trying to use ftp to transfer a file from a server at work in vba to my C
drive.

normally I go into CMD, type "ftp sysd"
username is asked for, so I type and press enter.
password is asked for, so I type and press enter.
then I type the "get" command followed by the filename - for example:
get 'zh.test.sas(testfile)' C:/testfile.txt
then quit, then close.

Ive used:
Shell "command.com /c ftp sysd"
which opens the ftp in command, however im unsure how to send the rest of
the commands to the window.

Any help greatly appreciated.

Jamie

On windows go to Start button and select Help and Support. then type FTP.
Choose the selection FTP by itself.

"Jamie" wrote:

Hi guys

im trying to use ftp to transfer a file from a server at work in vba to my C
drive.

normally I go into CMD, type "ftp sysd"
username is asked for, so I type and press enter.
password is asked for, so I type and press enter.
then I type the "get" command followed by the filename - for example:
get 'zh.test.sas(testfile)' C:/testfile.txt
then quit, then close.

Ive used:
Shell "command.com /c ftp sysd"
which opens the ftp in command, however im unsure how to send the rest of
the commands to the window.

Any help greatly appreciated.

Jamie

Here's one that used to work for me (haven't used it in a few years, though).
And it used put (not get), but I bet you can experiment and change that.

This goes in a General module:

Option Explicit
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const STILL_ACTIVE As Long = &H103

Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long

''' Arguments: szCommandLine The command line to execute using Shell.
''' iWindowState (Optional) The window state parameter to pass
''' to the Shell function. Default = vbHide.
''' See Shell function help for other options.
Sub ShellAndWait(ByVal szCommandLine As String, _
Optional ByVal iWindowState As Integer = vbHide)

Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
Dim lResult As Long

''' Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

''' Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

''' Loop while the shelled process is still running.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled process
' is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

End Sub


==========
And this goes behind a userform that collected the mainframe's id and password:

Option Explicit
Private Sub CommandButton1_Click()

Dim myUserId As String
Dim myPassword As String
Dim intFileNum As Long
Const myFTPtxt_file = "c:\myftp.txt"
Const work_directory = "C:\"
Const strFile As String = "SomeMFFileNameHere.data"

myUserId = Trim(TextBox2.Text)
myPassword = Trim(TextBox3.Text)

On Error GoTo do_ftpError

intFileNum = FreeFile()
Close #intFileNum
Open myFTPtxt_file For Output As #intFileNum

'Create the FTP Command File.
Print #intFileNum, "open xxxxxxx"
Print #intFileNum, myUserId
Print #intFileNum, myPassword
Print #intFileNum, "put " & work_directory & strFile
Print #intFileNum, "bye"
Close #intFileNum

'Execute the FTP Commands to upload the file.
ShellAndWait "ftp.exe -v -s:" & myFTPtxt_file, vbMinimizedNoFocus

'Delete both temporary files.
Kill myFTPtxt_file

Application.StatusBar = False
Exit Sub

do_ftpError:
Close #intFileNum
MsgBox "FTP Failed: The VB Error Was As Follows:" & _
Chr(13) & Error(Err), vbCritical

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

Jamie wrote:

Hi guys

im trying to use ftp to transfer a file from a server at work in vba to my C
drive.

normally I go into CMD, type "ftp sysd"
username is asked for, so I type and press enter.
password is asked for, so I type and press enter.
then I type the "get" command followed by the filename - for example:
get 'zh.test.sas(testfile)' C:/testfile.txt
then quit, then close.

Ive used:
Shell "command.com /c ftp sysd"
which opens the ftp in command, however im unsure how to send the rest of
the commands to the window.

Any help greatly appreciated.

Jamie

--

Dave Peterson