Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs Filename:="ftp://bla.de/excel/51.xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
Option Explicit
' 2001 by Thomas Rodemer
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 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 InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Integer
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" _
Alias "FtpSetCurrentDirectoryA" (ByVal hFtpSession As _
Long, ByVal lpszDirectory As String) As Long
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 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 Long
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 Long
Private Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Long
Private Declare Function FtpRenameFile Lib "wininet.dll" _
Alias "FtpRenameFileA" (ByVal hFtpSession As Long, _
ByVal lpszFromFileName As String, ByVal lpszToFileName _
As String) As Long
Private Declare Function FtpCreateDirectory Lib "wininet" _
Alias "FtpCreateDirectoryA" (ByVal hFtpSession As _
Long, ByVal lpszDirectory As String) As Long
Private Declare Function FtpRemoveDirectory Lib "wininet" _
Alias "FtpRemoveDirectoryA" (ByVal hFtpSession As _
Long, ByVal lpszDirectory As String) As Long
Private Declare Function InternetGetLastResponseInfo Lib _
"wininet.dll" Alias "InternetGetLastResponseInfoA" _
(lpdwError As Long, ByVal lpszBuffer As String, _
lpdwBufferLength As Long) As Long
Const ERROR_NO_MORE_FILES = 18
Const ERROR_INTERNET_EXTENDED_ERROR = 12003
Const FTP_TRANSFER_TYPE_BINARY = &H0
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const INTERNET_FLAG_RELOAD = &H80000000
Const INTERNET_FLAG_KEEP_CONNECTION = &H400000
Const INTERNET_FLAG_MULTIPART = &H200000
Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Const INTERNET_OPEN_TYPE_DIRECT = 1
Const INTERNET_OPEN_TYPE_PROXY = 3
Const INTERNET_INVALID_PORT_NUMBER = 0
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_SERVICE_GOPHER = 2
Const INTERNET_SERVICE_HTTP = 3
Private Declare Function FileTimeToSystemTime Lib "kernel32" _
(lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) _
As Long
Const MAX_PATH = 260
Const NO_ERROR = 0
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_TEMPORARY = &H100
Const FILE_ATTRIBUTE_COMPRESSED = &H800
Const FILE_ATTRIBUTE_OFFLINE = &H1000
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
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
Dim Verbundenoderwas As Boolean
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
Private Sub Command1_Click()
Dim nFlag As Long, hOpen As Long, hConnection As Long
Dim Result&
Dim strPath$, strDateiName$
Dim strUsername$, strPasswort$, strServer$, strSubDir$
strServer = "ftp.deinserver.de" 'Dein Server
strUsername$ = "UserName" 'Dein User Name
strPasswort = "Passwort" 'Dein Passwort
strSubDir$ = "UnterOrdner" 'Unterordner
'Pfad für Dateien?
strPath$ = IIf(Right$(ThisWorkbook.Path, 1) = "\", ThisWorkbook.Path, ThisWorkbook.Path & "\")
'Datei Name?
strDateiName$ = "Meine Datei.xls"
hOpen = InternetOpen("FTP", 1, vbNullString, vbNullString, 0)
If hOpen = 0 Then
MsgBox ("Es konnte keine Verbindung hergestellt werden!")
Exit Sub
End If
'Hier alle Angaben zum User deinen Bedürfnissen anpassen
hConnection = InternetConnect(hOpen, strServer, 0, strUsername, strPasswort, 1, nFlag, 0)
'Eventuelles wechseln in ein Subdir:
Result = FtpSetCurrentDirectory(hConnection, strSubDir$)
'Download einer File:
Result = FtpGetFile(hConnection, "Dateiname.xls", strPath$ & strDateiName$, False, &H0, &H0, 0)
'Upload einer File:
Result = FtpPutFile(hConnection, strPath$ & strDateiName$, strDateiName$, &H0, 0)
'Trennen der Verbindung:
If hConnection <> 0 Then InternetCloseHandle (hConnection)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
End Sub
Private Sub Command1_Click()
Dim nFlag As Long, hOpen As Long, hConnection As Long
Dim Result&
Dim strPath$, strDateiName$
Dim strUsername$, strPasswort$, strServer$, strSubDir$
strServer = "ftp.deinserver.de" 'Dein Server
strUsername$ = "UserName" 'Dein User Name
strPasswort = "Passwort" 'Dein Passwort
strSubDir$ = "UnterOrdner" 'Unterordner
'Pfad für Dateien?
strPath$ = IIf(Right$(ThisWorkbook.Path, 1) = "\", ThisWorkbook.Path, ThisWorkbook.Path & "\")
'Datei Name?
strDateiName$ = "Meine Datei.xls"
hOpen = InternetOpen("FTP", 1, vbNullString, vbNullString, 0)
If hOpen = 0 Then
MsgBox ("Es konnte keine Verbindung hergestellt werden!")
Exit Sub
End If
'Hier alle Angaben zum User deinen Bedürfnissen anpassen
hConnection = InternetConnect(hOpen, strServer, 0, strUsername, strPasswort, 1, nFlag, 0)
'Eventuelles wechseln in ein Subdir:
Result = FtpSetCurrentDirectory(hConnection, strSubDir$)
''Download einer File:
' Result = FtpGetFile(hConnection, strDateiName$, strPath$ & strDateiName$, False, &H0, &H0, 0)
'Upload einer File:
Result = FtpPutFile(hConnection, strPath$ & strDateiName$, strDateiName$, &H0, 0)
'Trennen der Verbindung:
If hConnection <> 0 Then InternetCloseHandle (hConnection)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
End Sub
strServer = "ftp.metzhair.de"
Option Explicit
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 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 InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Integer
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" _
Alias "FtpSetCurrentDirectoryA" (ByVal hFtpSession As _
Long, ByVal lpszDirectory As String) 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 Long
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 Long
Private Enum TRANSFER
FTP_TRANSFER_TYPE_BINARY = &H0
FTP_TRANSFER_TYPE_ASCII = &H1
End Enum
Private Enum INTERNET_SERVICE
INTERNET_SERVICE_FTP = 1
INTERNET_SERVICE_GOPHER = 2
INTERNET_SERVICE_HTTP = 3
End Enum
Private Const INTERNET_INVALID_PORT_NUMBER As Integer = 0
Private Sub Schaltfläche1_Klicken()
Dim nFlag As Long, hOpen As Long, hConnection As Long
Dim Result&
Dim strPath$, strDateiName$
Dim strUsername$, strPasswort$, strServer$, strSubDir$
strServer = "www.vba-excel.de" 'Dein Server
strUsername$ = "Dein-Username" 'Dein User Name
strPasswort = "Passwort" 'Dein Passwort
strSubDir$ = "wsb_Test" 'Unterordner oder leer lassen
'Pfad für Dateien?
strPath$ = IIf(Right$(ThisWorkbook.Path, 1) = "\", ThisWorkbook.Path, ThisWorkbook.Path & "\")
hOpen = InternetOpen("FTP", 1, vbNullString, vbNullString, 0)
If hOpen = 0 Then
MsgBox ("Es konnte keine Verbindung hergestellt werden!")
Exit Sub
End If
'Hier alle Angaben zum User deinen Bedürfnissen anpassen
hConnection = InternetConnect(hOpen, strServer, _
INTERNET_INVALID_PORT_NUMBER, strUsername, strPasswort, _
INTERNET_SERVICE.INTERNET_SERVICE_FTP, nFlag, 0)
If hConnection = 0 Then
MsgBox "Keine Verbindung zum Server"
GoTo Keine_Vernindung:
End If
'Eventuelles wechseln in ein Subdir:
If strSubDir <> "" Then
Result = FtpSetCurrentDirectory(hConnection, strSubDir$)
If Result = 0 Then
MsgBox "Unterordner nicht gefunden oder Zugriff verweigert"
GoTo Keine_Vernindung:
End If
End If
'_______________________________________________________________________________
'Datei Name? *******************************************************************
strDateiName$ = "030312_1867_5662_xslp_small.gif"
'Download einer File:
Result = _
FtpGetFile(hConnection, strDateiName$, strPath$ & strDateiName$, False, &H0, TRANSFER.FTP_TRANSFER_TYPE_BINARY, 0)
If Result = 0 Then MsgBox "Download fehlgeschlagen!"
'_______________________________________________________________________________
'Datei Name? *******************************************************************
strDateiName$ = "EineTestDatei.xls"
'Upload einer File:
Result = _
FtpPutFile(hConnection, strPath$ & strDateiName$, strDateiName$, TRANSFER.FTP_TRANSFER_TYPE_BINARY, 0)
If Result = 0 Then MsgBox "Upload fehlgeschlagen!"
'_______________________________________________________________________________
Keine_Vernindung:
'Trennen der Verbindung:
If hConnection <> 0 Then InternetCloseHandle (hConnection)
If hOpen <> 0 Then InternetCloseHandle (hOpen)
End Sub
Um eine Datei über FTP in Excel zu speichern, kannst du den folgenden VBA-Code verwenden. Dieser Code verbindet sich mit einem FTP-Server und lädt eine Excel-Datei hoch.
ALT + F11, um den VBA-Editor zu öffnen.Einfügen > Modul).Option Explicit
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 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 Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Long
Sub UploadToFTP()
Dim hOpen As Long, hConnection As Long
Dim strServer As String, strUsername As String, strPassword As String
Dim strLocalFile As String, strRemoteFile As String
Dim result As Long
strServer = "ftp.deinserver.de" ' Ersetze dies mit deinem Server
strUsername = "DeinBenutzername" ' Ersetze dies mit deinem Benutzernamen
strPassword = "DeinPasswort" ' Ersetze dies mit deinem Passwort
strLocalFile = "C:\Pfad\zu\deiner\Datei.xls" ' Lokale Datei
strRemoteFile = "Datei.xls" ' Name auf dem Server
hOpen = InternetOpen("FTP", 1, vbNullString, vbNullString, 0)
hConnection = InternetConnect(hOpen, strServer, 0, strUsername, strPassword, 1, 0, 0)
If hConnection <> 0 Then
result = FtpPutFile(hConnection, strLocalFile, strRemoteFile, 0, 0)
If result = 0 Then
MsgBox "Upload fehlgeschlagen!"
Else
MsgBox "Upload erfolgreich!"
End If
Else
MsgBox "Keine Verbindung zum Server!"
End If
If hConnection <> 0 Then InternetCloseHandle(hConnection)
If hOpen <> 0 Then InternetCloseHandle(hOpen)
End SubstrServer, strUsername, strPassword, strLocalFile und strRemoteFile an deine Bedürfnisse an.UploadToFTP aus, um die Datei hochzuladen.Fehler: "Keine Verbindung zum Server!"
ftp:// beginnen.Fehler: "Upload fehlgeschlagen!"
Fehler: "Download fehlgeschlagen!"
Eine andere Möglichkeit, um Dateien auf einen FTP-Server zu speichern, ist die Verwendung von FTP-Clients oder -Tools wie FileZilla. Diese bieten eine benutzerfreundliche Oberfläche und benötigen kein Programmieren mit VBA.
Hier ist ein Beispiel, wie du den Code anpassen kannst, um mehrere Dateien hochzuladen:
Sub MultiUploadToFTP()
Dim files As Variant
Dim i As Integer
files = Array("C:\Pfad\zu\Datei1.xls", "C:\Pfad\zu\Datei2.xls") ' Liste der Dateien
For i = LBound(files) To UBound(files)
' Führe hier den Upload-Befehl für jede Datei aus
' Du kannst den UploadToFTP Code hier einfügen und anpassen
Next i
End SubFTP_TRANSFER_TYPE_BINARY, wenn du Binärdateien hochladen möchtest.1. Wie kann ich sicherstellen, dass meine Verbindung zum FTP-Server sicher ist? Für eine sichere Verbindung solltest du SFTP (SSH File Transfer Protocol) in Betracht ziehen. VBA unterstützt dies jedoch nicht nativ, und du müsstest eine zusätzliche Bibliothek verwenden.
2. Muss ich den Code jedes Mal anpassen, wenn ich eine andere Datei hochladen möchte? Du kannst den Dateinamen in der Subroutine dynamisch gestalten, indem du Benutzereingaben verwendest oder den Dateinamen aus einer Zelle in Excel holst.
3. Welche Excel-Version benötige ich für diesen VBA-Code? Der Code sollte in den meisten modernen Excel-Versionen (Excel 2010 und später) funktionieren, die VBA unterstützen.
Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden
Suche nach den besten AntwortenEntdecke unsere meistgeklickten Beiträge in der Google Suche
Top 100 Threads jetzt ansehen