Discussion :

[lartiste_973]

Bonjour à tous,
Je m'en remets à vous car je crois avoir atteint mes limites en VBA. J'ai repris un bout de code qui fonctionne très bien sur FTP mais visiblement pas sur SFTP. Ce code est censé récuperer le dernier fichier csv produit, il tourne très bien autrement il ne bug pas sur une ligne erronée mais il ne récupère aucun fichier.


Auriez-vous une piste me permettant de régler le problème ?

Merci de votre aide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
Const MAX_PATH = 260
 
Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As Currency 'low & high 32 bits as 64-bit data type
    ftLastAccessTime As Currency
    ftLastWriteTime As Currency
    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
 
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
 
Private Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, _
ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, _
ByVal fFailIfExists As Boolean, _
ByVal dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean
 
Private Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, _
ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean
 
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, _
ByVal lpszDirectory As String) As Boolean
 
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, _
lpFindFileData As WIN32_FIND_DATA) As Long
 
Private Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Integer
 
Sub Download_ftp_file()
 
    Dim hOpen As Long, hConn As Long, hFind As Long
    Dim ret As Long
    Dim hostName As String, port As Long, username As String, password As String
    Dim localFolder As String
    Dim remoteDirectory As String, remoteMatchFiles As String
    Dim ftpMode As Long
    Dim fileFind As WIN32_FIND_DATA
    Dim newestFileTime As Currency
    Dim newestFileName As String
 
     '========== User-defined settings ==========
 
    localFolder = "P:\XX\XX\"
    hostName = "xx"
    port = 21
    username = "xx"
    password = "xx"
    remoteDirectory = "/xx/xx/"
    remoteMatchFiles = "*.csv"
 
     '===========================================
 
    ftpMode = 0 'active mode FTP
     'ftpMode = INTERNET_FLAG_PASSIVE    'passive mode FTP
 
    ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
    hOpen = ret
 
    If ret > 0 Then
        ret = InternetConnect(hOpen, hostName, port, username, password, 1, ftpMode, 0)
        hConn = ret
    End If
 
    If ret > 0 Then
        ret = FtpSetCurrentDirectory(hConn, remoteDirectory)
    End If
 
    If ret > 0 Then
 
         'Find first matching file
 
        fileFind.cFileName = String(MAX_PATH, vbNullChar)
        ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, 0, 0)
        hFind = ret
 
        While ret > 0
            Debug.Print TrimNulls(fileFind.cFileName)
 
             'Is this file newer than the newest file seen so far?
 
            If fileFind.ftLastWriteTime > newestFileTime Then
                newestFileTime = fileFind.ftLastWriteTime
                newestFileName = TrimNulls(fileFind.cFileName)
            End If
 
             'Find next matching file
 
            fileFind.cFileName = String(MAX_PATH, vbNullChar)
            ret = InternetFindNextFile(hFind, fileFind)
 
        Wend
 
        Debug.Print "Newest "; newestFileName
 
         'Download the newest file to local folder
 
        ret = FtpGetFile(hConn, newestFileName, localFolder & newestFileName, False, 0, 0, 0)
 
        If ret = 0 Then
            Debug.Print "FtpGetFile error "; Err.LastDllError
        End If
 
    End If
 
     'Release handles
 
    InternetCloseHandle hFind
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
 
End Sub
 
Private Function TrimNulls(buffer As String) As String
    TrimNulls = Left(buffer, InStr(buffer, vbNullChar) - 1)
End Function

[marot_r]

Bonjour.

J'ai fouillé un peu sur Google et voici les réponses qui m'ont paru intéressantes :

Upload with VBA via sFTP & FTP, log outputs to detect errors
http://stackoverflow.com/questions/3...-detect-errors

En bas de la discussion.

Download a file from SFTP site using VBA
https://social.msdn.microsoft.com/Fo...orum=accessdev

A+