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 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
|
Option Explicit
Public 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
Public 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
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias _
"FtpSetCurrentDirectoryA" (ByVal hFtpSession As Long, _
ByVal lpszDirectory As String) As Boolean
Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" ( _
ByVal hConnect As Long, _
ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, _
ByVal fFailIfExists As Long, _
ByVal dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, _
ByRef dwContext As Long) As Boolean
Public 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
Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
(ByVal hConnect As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long
Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
(ByVal hFind As Long, ByRef lpvFindData As String) As Boolean
Public Declare Function FtpDeleteFile Lib "wininet.dll" Alias "FtpDeleteFileA" ( _
ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Public Const MAX_PATH = 260
Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
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
Public CheminFRE, CheminPDF, CheminPDFServeur, FichierFRE, FichierPDF, IP, MDP, Path As String
Public vAn As Integer
Public bin_asc, Mode As Variant
Public Internet_OK, FTP_OK, Sélect_rép As Integer
Public Succès As Boolean
Public Result As WIN32_FIND_DATA
' Détermine si le dossier FTP existe
Function DossierExiste(NomDossier As String) As Boolean
DossierExiste = Dir(NomDossier, vbDirectory) <> ""
End Function
' Pour Télécharger FRE depuis serveur
Sub Téléchargement_Fichier()
vAn = Format(Now, "yyyy")
If DossierExiste("C:\FTP\") = False Then
MkDir "C:\FTP\"
End If
Select Case MsgBox("Etes-vous à votre domicile pour vous connecter sur le serveur ?", vbYesNo, vbQuestion)
Case vbYes
IP = "192.168.X.X"
Case vbNo
IP = "81.XX.XXX.XXX"
End Select
' Mot de passe Accès Serveur
Do While MDP <> "XXXXXX"
MDP = InputBox("Mot de passe ?", "Accès serveur")
If MDP <> "XXXXXX" Then
MsgBox "Ce n'est pas le bon mot de passe..."
End If
Loop
FichierFRE = "FRE vierge Année " & vAn & ".xlsm"
CheminFRE = "C:\FTP\" + FichierFRE
Internet_OK = InternetOpen("", 1, "", "", 0)
If Internet_OK Then
FTP_OK = InternetConnect(Internet_OK, IP, 21, "XXXX", "XXXXXX", 1, 0, 0)
If FtpSetCurrentDirectory(FTP_OK, "\") Then
Succès = FtpGetFile(FTP_OK, CheminFRE, CheminFRE, False, 0, &H0, 0)
Succès = FtpDeleteFile(FTP_OK, CheminFRE)
End If
End If
If Succès Then
MsgBox "Le fichier " + FichierFRE + " a été télécharger depuis le serveur !"
Else
MsgBox ("Le fichier '" + FichierFRE + "' n'a pas été trouvé !")
End If
Workbooks.Open FileName:=CheminFRE
' Arrêt connection serveur
InternetCloseHandle FTP_OK
InternetCloseHandle Internet_OK
End Sub
' Pour Envoyer FRE vierge et PDF vers serveur
Sub Envoie_Fichier()
If DossierExiste("C:\FTP\") = False Then
MsgBox "Pas de fichiers à transférer..." & vbLf & vbLf & "Veuillez télécharger la FRE vierge depuis le serveur !", "Transfert Fichiers"
Exit Sub
End If
vAn = Format(Now, "yyyy")
FichierFRE = "FRE vierge Année " & vAn & " test" & ".xlsm"
CheminFRE = "C:\FTP\" + FichierFRE
Path = "C:\FTP\*.pdf"
FichierPDF = Dir(Path)
Internet_OK = InternetOpen("", 1, "", "", 0)
If Internet_OK Then
FTP_OK = InternetConnect(Internet_OK, IP, 21, "XXXX", "XXXXXX", 1, 0, 0)
If FtpSetCurrentDirectory(FTP_OK, "\") Then
Succès = FtpPutFile(FTP_OK, CheminFRE, FichierFRE, 1, 0)
'Succès = FtpPutFile(FTP_OK, CheminPDF, CheminPDFServeur, 1, 0)
While FichierPDF <> ""
'Path = "C:\FTP\*.pdf"
CheminPDF = "C:\FTP\" + FichierPDF
CheminPDFServeur = "C:\FTP\PDF\" + FichierPDF
Succès = FtpPutFile(FTP_OK, CheminPDF, CheminPDFServeur, 1, 0)
FichierPDF = Dir
Wend
End If
End If
If Succès Then
MsgBox "Les fichiers ont bien été transférés sur le serveur ", , "Transfert FTP"
Else
MsgBox "Serveur non accessible..." & vbLf & vbLf & "Veuillez appeler la maintenance au XX XX XX XX XX !", vbExclamation, "Erreur Transfert Fichiers"
End If
' Arrêt connection serveur
InternetCloseHandle FTP_OK
InternetCloseHandle Internet_OK
' Suppresion des fichiers
Kill ("C:\FTP\*.*")
End Sub
' Pour editer anciennes FRE
Sub EditionFRE()
If DossierExiste("C:\FTP\AncFRE") = False Then
MkDir "C:\FTP\AncFRE"
End If
Select Case MsgBox("Etes-vous à votre domicile pour vous connecter sur le serveur ?", vbYesNo, vbQuestion)
Case vbYes
IP = "192.168.X.X"
Case vbNo
IP = "81.XX.XXX.XXX"
End Select
Path = "C:\FTP\PDF\*.pdf"
FichierPDF = Dir(Path)
Internet_OK = InternetOpen("", 1, "", "", 0)
If Internet_OK Then
FTP_OK = InternetConnect(Internet_OK, IP, 21, "XXXX", "XXXXXXXX", 1, 0, 0)
If FtpSetCurrentDirectory(FTP_OK, "C:\FTP\PDF\") Then
???
End If
End If
' Arrêt connection serveur
InternetCloseHandle FTP_OK
InternetCloseHandle Internet_OK
End Sub |
Partager