vba - 用于连接到 FTPS 服务器的 Excel 宏代码，即 FTP Over SSL 而不是 FTP 服务器

Question

问题描述: 无法连接到 FTPS 服务器，这是一个私有(private)服务器 FTP Over SSL 或 FTPS。它是与 HTTPS 相同的安全连接。 .我正在使用的代码成功连接到公共(public)服务器。但似乎为了连接到安全的 FTPS 服务器，我需要使用某种 SSL 加密。我完全不知道，我是 Java 专家，但我被要求解决这个问题，学习新事物总是很有趣，这次是 VBA 代码。请VBA高手帮帮我。

有人可以告诉我应该修改或添加代码的内容和位置以连接到我的 FTPS 服务器吗？请再次注意，我可以连接到 FTP 服务器，但不能连接到 FTPS。

下面是我目前使用的代码。这将是一个很大的帮助，谢谢!

'API code

Private Type FILETIME
   dwLowDateTime As Long
   dwHighDateTime As Long
End Type

Private Const MAX_PATH = 260
Private Type WIN32_FIND_DATA
   dwFileAttributes As Long
   ftCreationTime As FILETIME
   ftLastAccessTime As FILETIME
   ftLastWriteTime As FILETIME
   nFileSizeHigh As Long
   nFileSizeLow As Long
   dwReserved0 As Long
   dwReserved1 As Long
   cFileName As String * MAX_PATH
   cAlternate As String * 14
End Type


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


        'Connect to the network
 Private Declare Function InternetConnect _
   Lib "wininet.dll" _
     Alias "InternetConnectA" _
       (ByVal hInternetSession As Long, _
        ByVal sServerName As String, _
        ByVal nServerPort As Integer, _
        ByVal sUsername As String, _
        ByVal sPassword As String, _
        ByVal lService As Long, _
        ByVal lFlags As Long, _
        ByVal lContext As Long) As Long

        'Get a file using FTP
 Private Declare Function FtpGetFile _
   Lib "wininet.dll" _
     Alias "FtpGetFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszremoteDir As String, _
        ByVal lpszNewFile As String, _
        ByVal fFailIfExists As Boolean, _
        ByVal dwFlagsAndAttributes As Long, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean


        'Close the Internet object
 Private Declare Function InternetCloseHandle _
   Lib "wininet.dll" _
     (ByVal hInet As Long) As Integer


        '
    Private Declare Function FtpFindFirstFile _
    Lib "wininet.dll" _
        Alias "FtpFindFirstFileA" _
        (ByVal hFtpSession As Long, _
        ByVal lpszSearchFile As String, _
        lpFindFileData As WIN32_FIND_DATA, _
        ByVal dwFlags As Long, _
        ByVal dwContent As Long) As Long

        Private Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
(ByVal hFind As Long, lpvFindData As WIN32_FIND_DATA) As Long

Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hConnect As Long, ByVal lpszDirectory As String) As Long



'***************
'downloadFile method downloads files from a specified server through FTP
'This method downloads files on only first level of specified directory on the server
'
'
'
'
'***************

'***************
'To do
'
'1. if localDir does not include "\", it does not work - fixed
'2. if folders exist on the remote server, it will not download
'
'
'***************

'download files from a specified server

Public Function downloadFiles(ServerName As String, UserName As String, Password As String, remoteDir As String, localDir As String, logFile As String) As Variant()

Dim INet As Long
Dim INetConn As Long
Dim RetVal As Long
Dim Success As Long
Dim hFile As Long
Dim w32FindData As WIN32_FIND_DATA
Dim StrFile As String
Dim fileList() As String
Dim cnt As Long
Dim gcnt As Long
Dim i As Integer
Dim curDir As Long
Dim result(1) As Variant


cnt = -1
gcnt = 0
RetVal = False


Rem confirm local dir has \ at the end

If Not Right(localDir, 1) = "\" Then
    localDir = localDir + "\"
End If

'Test Code need to remove as the username and password are hardcoded


INet = InternetOpen("MYFTP Control", 1&, vbNullString, vbNullString, 0&)
    If INet > 0 Then
        INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
        If INetConn > 0 Then
            file.log "==== Connected to " & ServerName & "===", logFile


        curDir = FtpSetCurrentDirectory(INetConn, remoteDir)
        If (curDir <> 0) Then
            file.log "current remote dir: " & remoteDir, logFile
        End If

            ''''''''''''''''''''
            ''Create a list of files to download
            ''''''''''''''''''''


            'get file list
            hFile = FtpFindFirstFile(INetConn, remoteDir, w32FindData, INTERNET_FLAG_RELOAD, 0&)


            'create a list of files on the remote server
        If hFile = 0 Then
            file.log "cannot get a list of files", logFile
        Else

            Do

                StrFile = Left(w32FindData.cFileName, InStr(w32FindData.cFileName, vbNullChar) - 1)
                StrFile = Mid(StrFile, InStrRev(StrFile, " ") + 1)

                'if the path is directory, skip this
                If ((w32FindData.dwFileAttributes And &H10) <> &H10) Then
                    'strFile = strFile & "/"


                cnt = cnt + 1

                ReDim Preserve fileList(cnt)
                fileList(cnt) = StrFile
                Debug.Print StrFile 'Debug

                End If 'end of skiping dir condition

            Loop Until InternetFindNextFile(hFile, w32FindData) = 0


            ''''''''''''''''''''
            ''Download files on the list
            ''''''''''''''''''''
            For i = 0 To cnt

                'set local file
                StrFile = localDir & fileList(i)

                    'download a file
                    Success = FtpGetFile(INetConn, fileList(i), StrFile, False, FILE_ATTRIBUTE_NORMAL, BINARY_TRANSFER, 0&)

                    If Success > 0 Then
                    file.log fileList(i) & " is downloaded", logFile
                    gcnt = gcnt + 1

                    Else
                    file.log fileList(i) & " is Not downloaded", logFile

                    End If

            Next


        End If

        RetVal = InternetCloseHandle(INet)


        Else


        'cannot connet to the server error message
        file.log "Client cannnoot connet to " & ServerName, logFile
        RetVal = InternetCloseHandle(INet)

        End If

    End If




result(0) = cnt + 1
result(1) = gcnt


file.log ServerName & " - " & "Downloaded files: " & CStr(result(1)) & " out of " & CStr(result(0)), logFile

        If RetVal > 0 Then
            file.log "===Connection is closed===", logFile
        Else
            file.log "===Connection is not closed correctly===", logFile
        End If


downloadFiles = result


End Function

Private Function log(warnLevel As String, info As String, fileName As String)




End Function

Private Function msg(info As String)

    MsgBox info

End Function

Answer 1

WinSCP将支持 FTPS(显式或隐式)；您可以使用 VBA 通过后者的脚本命令与 WinSCP 交互。

这些链接应该可以帮助您入门:

• > https://winscp.net/eng/docs/ftps
• > https://winscp.net/eng/docs/scripting
• > https://stackoverflow.com/a/35644297/5472502