我正在尝试以编程方式构建 Excel VBA,并希望它可以通过 FTP 命令行从服务器下载一些文件。我在家庭本地网络中设置的服务器(实验期)为互联网用户提供 FTP 服务。(稍后我应该将其移动到 VPS 以获得更稳定的运行)
我所做的包括:
- 设置 IIS FTP 服务,
- 服务器 PC 的防火墙,
- 路由器防火墙和转发服务;
我已测试成功的包括:
- 局域网PC FTP命令行下载FTP服务器的文件;
...
230 User logged in.
ftp> Get "\CalData\22-12-21.txt" "C:\Downloads\CalData\22-12-21.txt"
200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 1065 bytes received in 0.04Seconds 23.67Kbytes/sec.
- 广域网个人计算机Filezilla 客户端登录并下载FTP服务器的文件;
当我尝试执行以下操作时,最后一步失败: WAN PC FTP命令行下载FTP服务器的文件。
...
230 User logged in.
ftp> Get \CalData\22-12-21.txt C:\Downloads\CalData\22-12-21.txt
501 Server cannot accept argument.
150 Opening ASCII mode data connection. (...frozen here...)
Aborting any active data connections... (Ctrl+C to quit)
425 Cannot open data connection.
ftp> binary (Try again with Bin mode)
200 Type set to I.
ftp> Get \CalData\22-12-21.txt C:\Downloads\CalData\22-12-21.txt
150 Opening BINARY mode data connection. (...frozen still...)
Aborting any active data connections... (Ctrl+C to quit)
425 Cannot open data connection.
ftp> quote pasv (Try again with passive mode)
227 Entering Passive Mode (94,14,216,186,196,105).
ftp> Get \CalData\22-12-21.txt C:\Downloads\CalData\22-12-21.txt
150 Opening BINARY mode data connection. (...frozen still...)
Aborting any active data connections... (Ctrl+C to quit)
425 Cannot open data connection.
ftp> dir (Try list directory)
501 Server cannot accept argument.
150 Opening ASCII mode data connection. (...frozen still...)
Aborting any active data connections... (Ctrl+C to quit)
425 Cannot open data connection.
ftp?> quit
我尝试替换上述设置的某些部分,包括: 使用Filezilla Server替代IIS FTP,但是结果是一样的。
我的问题是什么样的原因可能导致失败?我该如何解决它? 注意到,WAN 电脑与 Filezilla 客户端访问配合良好。我怀疑我应该对 FTP 命令进行一些操作,以使 WAN 电脑能够正确访问 FTP 服务器。
任何建议都非常感谢。谢谢。
答案1
使用 InternetConnectA Lib“wininet.dll”的计划最终通过 WAN PC 使用 VBA 脚本访问 FTP 服务器。正如 Martin 所说,使用被动模式是关键。再次感谢 Martin。
当然,在局域网中也可以使用。下面是我使用的代码,仅供参考。
Private Const INTERNET_SERVICE_FTP As Long = &H1
Private Const INTERNET_FLAG_PASSIVE As Long = &H8000000
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
Public 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: Call FtpDownload("CSMDailyAll.txt", "C:\Downloads\FXData\CSMDailyAll.txt", "myftp.com", 21, "User", "Password")
'1.Delete Target File
Call DeleteFile(strLocalFile)
'2.Open Internet Shell, Check Availablility
Dim hOpen As Long: hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 0)
If hOpen = 0 Then MsgBox "Internet Connection Error...": GoTo End_Sub
'3.Build up FTP connection with Passvie mode
Dim hConn As Long: hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, INTERNET_SERVICE_FTP, INTERNET_FLAG_PASSIVE, 0)
If hConn = 0 Then MsgBox "FTP Connection Fails...": GoTo Cls_Opn
'4.Get Target File, with no cache (always Reload)
Dim hFGet As Long: hFGet = FtpGetFileA(hConn, strRemoteFile, strLocalFile, 1, 0, INTERNET_FLAG_RELOAD, 0)
If hFGet = 0 Then MsgBox "FTP Getting File Error...": GoTo Cls_Con
'5.Check,Wait FileTransfer Complete
Do
If FileExists(strLocalFile) Then Exit Do Else DoEvents 'Prevents Excel from being unresponsive
Application.Wait Now + TimeValue("0:00:01") 'Wait for OneSecond
Loop
'6.Close Connections
Cls_Con: InternetCloseHandle hConn
Cls_Opn: InternetCloseHandle hOpen
End_Sub: End Sub
Public Function FileExists(ByVal FileToTest As String) As Boolean 'CheckFile Existence
FileExists = (Dir(FileToTest) <> "")
End Function
Public Sub DeleteFile(ByVal FileToDelete As String) 'DeleteFile
If FileExists(FileToDelete) Then Kill FileToDelete
End Sub
参考链接:
https://learn.microsoft.com/en-us/windows/win32/api/wininet/nf-wininet-internetopena https://learn.microsoft.com/en-us/windows/win32/api/wininet/nf-wininet-internetconnecta https://learn.microsoft.com/en-us/windows/win32/wininet/ftp-sessions https://learn.microsoft.com/en-us/windows/win32/api/Wininet/nf-wininet-ftpgetfilea https://learn.microsoft.com/en-us/windows/win32/wininet/api-flags