I'm trying to download a set of text files from a remote server using VBA and have been getting various errors. Latest is run-time error 430. Here's my code (which I lifted from an example on internet) which I hope someone can adjust to make work. Thanks in advance. 'servername', 'userid' and 'password' are not actual. Port number must be 22 when using FTP software packages. I have included msinet.ocx in References library and get clean compiles.
Code:
Sub ftptest()
Dim DidIGetIt As Boolean
DownloadFile "sftp.servername.com", "userid", "password", 22, _
"g:\home\mast.txt", _
"c:\Temp\mast.txt"
MsgBox DidIGetIt
End Sub
Function DownloadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal port As Integer, _
ByVal RemoteFileName As String, _
ByVal LocalFileName As String) As Boolean
Dim FTP As Inet
Set FTP = Nothing
Set FTP = New Inet
With FTP
.Protocol = icFTP
'.Protocol = 2
.RemoteHost = HostName
.UserName = UserName
.Password = Password
.RemotePort = 22
.Execute .URL, "Get " + RemoteFileName + " " + LocalFileName
Do While .StillExecuting
DoEvents
Loop
DownloadFile = (.ResponseCode = 0)
End With
Set FTP = Nothing
End Function