来自FTP的VBA下载文件url

Question

我试图创建VBA代码，将文件从直接FTP链接（异步首选）下载到特定路径。 我只找到代码使其与HTTP网址的工作，但对于FTP我得到这个错误：

“运行时错误‘-2146697210（800c0006）’： 系统找不到指定的对象”

对于这些第一次测试没有为ftp-server设置用户名或密码。

我的代码只工作了HTTP低于：

Sub DownloadFile() 

Dim myURL As String 
myURL = "ftp://xxx.xxx.xxx.xxx/test.txt" 

Dim WinHttpReq As Object 
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP") 
WinHttpReq.Open "GET", myURL, False, "username", "password" 
WinHttpReq.send 

myURL = WinHttpReq.responseBody 
If WinHttpReq.Status = 200 Then 
    Set oStream = CreateObject("ADODB.Stream") 
    oStream.Open 
    oStream.Type = 1 
    oStream.Write WinHttpReq.responseBody 
    oStream.SaveToFile "C:\FTP\file.txt", 2 ' 1 = no overwrite, 2 = overwrite 
    oStream.Close 
End If 

End Sub 

Answer 1

您将需要一个模块添加到您的项目，以获得FTP功能。 Sub FTPdownload有示例代码。从http://experts-exchange.com/Networking/Protocols/Q_23627204.html

Private Const FTP_TRANSFER_TYPE_UNKNOWN  As Long = 0 
Private Const INTERNET_FLAG_RELOAD   As Long = &H80000000 

Private Declare Function InternetOpenA Lib "wininet.dll" (_ 
    ByVal sAgent As String, _ 
    ByVal lAccessType As Long, _ 
    ByVal sProxyName As String, _ 
    ByVal sProxyBypass As String, _ 
    ByVal lFlags As Long) As Long 

Private Declare Function InternetConnectA Lib "wininet.dll" (_ 
    ByVal hInternetSession As Long, _ 
    ByVal sServerName As String, _ 
    ByVal nServerPort As Long, _ 
    ByVal sUsername As String, _ 
    ByVal sPassword As String, _ 
    ByVal lService As Long, _ 
    ByVal lFlags As Long, _ 
    ByVal lcontext As Long) As Long 

Private Declare Function FtpGetFileA Lib "wininet.dll" (_ 
    ByVal hConnect As Long, _ 
    ByVal lpszRemoteFile As String, _ 
    ByVal lpszNewFile As String, _ 
    ByVal fFailIfExists As Long, _ 
    ByVal dwFlagsAndAttributes As Long, _ 
    ByVal dwFlags As Long, _ 
    ByVal dwContext As Long) As Long 

Private Declare Function InternetCloseHandle Lib "wininet" (_ 
    ByVal hInet As Long) As Long 


Sub FtpDownload(ByVal strRemoteFile As String, ByVal strLocalFile As String, ByVal strHost As String, ByVal lngPort As Long, ByVal strUser As String, ByVal strPass As String) 
    'usage 
    'FtpDownload "/TEST/test.html", "c:\test.html", "ftp.server.com", 21, "user", "password" 
    Dim hOpen As Long 
    Dim hConn As Long 

    hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1) 
    hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2) 

    If FtpGetFileA(hConn, strRemoteFile, strLocalFile, 1, 0, FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, 0) Then 
     Debug.Print "done" 
    Else 
     Debug.Print "fail" 
    End If 

    InternetCloseHandle hConn 
    InternetCloseHandle hOpen 

End Sub