FTP mit VBA Excel: Eine vollständige Anleitung und Beispiele

FTP (File Transfer Protocol) ist ein Dateiübertragungsprotokoll, mit dem Sie Daten zwischen Computern in einem Netzwerk austauschen können. Es ist eine der wichtigsten Möglichkeiten, Dateien zwischen Client und Server zu übertragen. In diesem Artikel erfahren Sie, wie Sie FTP mit VBA Excel verwenden.

Microsoft Excel ist eines der beliebtesten Tools für die Arbeit mit Tabellen und bietet auch Möglichkeiten, verschiedene Aufgaben mithilfe der Programmiersprache VBA (Visual Basic for Applications) zu automatisieren. Insbesondere können Sie mit VBA Vorgänge zum Übertragen von Dateien über FTP ausführen.

In diesem Handbuch werden wir die grundlegenden Schritte zum Arbeiten mit FTP in VBA Excel untersuchen. Wir zeigen Ihnen, wie Sie eine Verbindung zu einem FTP-Server herstellen, verschiedene Dateiübertragungsvorgänge wie das Hochladen und Herunterladen von Dateien durchführen und wie Sie mit Fehlern umgehen können, die während der Arbeit auftreten können.

Die Beispiele in diesem Artikel helfen Ihnen, besser zu verstehen, wie Sie FTP mit VBA Excel verwenden und dieses Wissen in Ihren eigenen Projekten anwenden können. Sie können den Dateiübertragungsprozess automatisieren, die Arbeit mit Daten beschleunigen und die Effizienz Ihrer Aufgaben verbessern.

Vorbereiten von Excel-VBA für FTP

Bevor Sie mit FTP in VBA Excel beginnen können, müssen Sie einige vorbereitende Schritte ausführen:

Um einen Verweis auf die Microsoft Internet Controls-Bibliothek in Excel festzulegen, öffnen Sie den VBA-Editor, gehen Sie zu Extras -> Verweise und suchen Sie in der Liste der verfügbaren Bibliotheken nach "Microsoft Internet Controls". Setzen Sie das Häkchen daneben und klicken Sie auf "OK".

Verwenden Sie den folgenden Code, um eine Instanz eines InternetExplorer-Objekts zu erstellen:

Dim ie As New InternetExplorer

Verwenden Sie den folgenden Code, um das InternetExplorer-Objekt für die Verwendung mit FTP zu konfigurieren:

ie.Silent = Trueie.Navigate "ftp://ftp-сервер"

Wobei "FTP-Server" die Adresse des FTP-Servers ist, mit dem Sie eine Verbindung herstellen möchten.

Schließlich müssen Sie die Anmeldeinformationen (Benutzername und Kennwort) mit der SendKeys-Methode eingeben, um den Zugriff auf den FTP-Server herzustellen. Zum Beispiel:

Application.Wait Now + TimeValue("0:00:01")SendKeys "username", TrueSendKeys "", TrueSendKeys "password", TrueSendKeys "~", True

Wobei "username" der Benutzername und "password" das Passwort für den Zugriff ist.

Nachdem Sie diese vorbereitenden Schritte ausgeführt haben, ist VBA Excel bereit, mit FTP zu arbeiten und Vorgänge wie das Herunterladen, Herunterladen und Löschen von Dateien vom FTP-Server auszuführen.

Verbindung mit einem FTP-Server mit VBA Excel herstellen

Die Verbindung zu einem FTP-Server mit VBA in Excel ist einfach genug. Es gibt mehrere Möglichkeiten, diese Aufgabe auszuführen. Eine Option, die wir hier betrachten werden, besteht darin, die Microsoft XML-Bibliothek zu verwenden, um ein XMLHTTP-Objekt zu erstellen, das zum Senden von FTP-Anforderungen an den Server verwendet wird.

Hier ist ein Beispielcode, der zeigt, wie Sie sich mit VBA Excel mit einem FTP-Server verbinden und Abfragen senden können:

Sub FTPConnectionExample()Dim ftpServer As StringDim ftpUsername As StringDim ftpPassword As StringftpServer = "ftp.example.com"ftpUsername = "username"ftpPassword = "password"Dim xmlhttp As ObjectSet xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")xmlhttp.Open "GET", "ftp://" & ftpUsername & ":" & ftpPassword & "@" & ftpServer, Falsexmlhttp.sendMsgBox xmlhttp.ResponseTextEnd Sub

Im obigen Code definieren wir zunächst Variablen, die die FTP-Serveradresse, den Benutzernamen und das Passwort enthalten. Dann erstellen wir ein xmlhttp-Objekt mit der CreateObject-Funktion und geben "MSXML2" an.ServerXMLHTTP" als Argument.

Dann öffnen wir die Verbindung zum FTP-Server mit der Open-Methode des xmlhttp-Objekts. Wir geben die Methode "GET" sowie die Server-URL an, einschließlich Benutzername und Passwort. Das Flag False gibt an, dass die Verbindung synchron sein muss.

Als nächstes senden wir die Anfrage mit der send-Methode des xmlhttp-Objekts an den Server.

Schließlich geben wir die Antwort des Servers im Textformat mit der MsgBox-Funktion aus.

Wenn Sie diesen Code verwenden, müssen Sie die Werte der Variablen FTPServer, ftpUsername und ftpPassword in die entsprechenden Werte für Ihren FTP-Server und Ihr Konto ändern.

Jetzt wissen Sie, wie Sie sich mit VBA in Excel mit einem FTP-Server verbinden und Abfragen senden können. Dies eröffnet Möglichkeiten zum Erstellen von Makros, die automatisch Dateien zwischen dem FTP-Server und Ihrer Datentabelle in Excel übertragen.

Hochladen von Dateien auf einen FTP-Server mit VBA Excel

Um zu beginnen, müssen Sie eine Verbindung zum FTP-Server herstellen. Dazu können Sie die Open-Funktion aus einem Microsoft-Objekt verwenden.XMLHTTP . Sie müssen die Server-URL, den Benutzernamen und das Passwort angeben:

Dim xmlhttp As New MSXML2.XMLHTTPURL = "ftp://ftp.example.com/"Login = "username"Password = "password"xmlhttp.Open "PUT", URL, False, Login, Password

Anschließend können Sie den Pfad zu der Datei angeben, die Sie hochladen möchten, und sie mithilfe der Send-Methode an den Server senden :

File = "C:\path\to\file.csv"xmlhttp.Send File

Wenn der Download der Datei erfolgreich war, wird eine Meldung mit dem Code 200 auf dem Bildschirm angezeigt. Im Falle eines Fehlers wird eine entsprechende Meldung angezeigt.

Um sicherzustellen, dass die Datei auf den Server hochgeladen wurde, können Sie eine Anfrage an den Server stellen und mithilfe der Funktion Exists überprüfen, ob die Datei vorhanden ist :

Function Exists(URL As String) As BooleanDim http As New MSXML2.XMLHTTPOn Error Resume Nexthttp.Open "HEAD", URL, Falsehttp.SendExists = (http.Status = 200)On Error GoTo 0End Function

Sie können auch die Dir-Funktion verwenden, um Dateien zu identifizieren, die sich bereits auf dem Server befinden:

ServerURL = "ftp://ftp.example.com/"FileExtension = "*.csv"Files = Dir(ServerURL & FileExtension)Do While Files <> ""MsgBox FilesFiles = Dir()Loop

Dieser Code gibt alle Dateien mit der Erweiterung aus. "csv", die sich auf einem FTP-Server befinden.

Mit VBA Excel können Sie Dateien einfach auf einen FTP-Server hochladen. Jetzt können Sie diese Beispiele für Ihre Projekte verwenden und die Dateiübertragung über FTP automatisieren.

Herunterladen von Dateien von einem FTP-Server mit VBA Excel

Um Dateien von einem FTP-Server herunterzuladen, führen Sie die folgenden Schritte in VBA Excel aus:

1. Verweisen Sie auf die Microsoft Internet Controls-Bibliothek (Menü Tools → References → Microsoft Internet Controls).
2. Instanziieren eines InternetExplorer-Objekts und eines Shell-Objekts.
3. Öffnen Sie den FTP-Server mit dem InternetExplorer-Objekt.
4. Geben Sie die Anmeldeinformationen für den Zugriff auf den FTP-Server ein (falls erforderlich).
5. Zum gewünschten Verzeichnis auf dem FTP-Server wechseln.
6. Laden Sie die Datei mit dem Shell-Objekt herunter.
7. Schließen Sie die InternetExplorer- und Shell-Objekte.

Hier ist ein Beispiel für VBA-Code zum Herunterladen einer Datei von einem FTP-Server:

Sub DownloadFileFromFTPServer()Dim ie As ObjectDim objShell As ObjectDim ftpURL As StringDim ftpUsername As StringDim ftpPassword As StringDim ftpPath As StringDim localPath As String' Инициализация переменныхftpURL = "ftp://example.com"ftpUsername = "username"ftpPassword = "password"ftpPath = "/path/to/file.txt"localPath = "C:\Downloads\file.txt"' Создание экземпляров объектов InternetExplorer и ShellSet ie = CreateObject("InternetExplorer.Application")Set objShell = CreateObject("Shell.Application")' Открытие FTP сервераWith ie.Visible = False.navigate ftpURL' Ожидание полной загрузки страницыDo While .Busy Or Not .readyState = 4DoEventsLoopEnd With' Ввод учетных данныхSendKeys ftpUsername & "" & ftpPassword & ""Application.Wait (Now + TimeValue("0:00:2"))' Переход в нужную директориюSendKeys ftpPath & ""Application.Wait (Now + TimeValue("0:00:2"))' Скачивание файлаobjShell.Namespace(0).CopyHere objShell.Namespace(ftpURL & ftpPath).Items.Item("file.txt")Application.Wait (Now + TimeValue("0:00:5"))' Закрытие объектов InternetExplorer и Shellie.QuitSet ie = NothingSet objShell = Nothing' Открытие скачанного файлаWorkbooks.Open localPathEnd Sub

Mit diesem Codebeispiel können Sie die Datei "file" herunterladen.txt" vom FTP-Server "ftp://example.com/path/to/file.txt " in ein lokales Verzeichnis "C:\Downloads \" mit den Anmeldeinformationen "username" und "password". Nachdem Sie die Datei heruntergeladen haben, wird sie in Excel geöffnet.

Mit dem obigen Codebeispiel können Sie es an Ihre Bedürfnisse anpassen, indem Sie den gewünschten FTP-Server, den Pfad zur Datei auf dem FTP-Server, die Anmeldeinformationen und das lokale Verzeichnis zum Speichern der Datei angeben.

Löschen von Dateien von einem FTP-Server mit VBA Excel

1. Verweisen Sie in Ihrem Excel-VBA-Projekt auf die Microsoft Internet Controls-Bibliothek (MSCOMCTLLib). Dies ermöglicht die Verwendung von internetbezogenen Objekten, einschließlich FTP.
2. Erstellen Sie eine Instanz des InternetExplorer-Objekts, und definieren Sie dessen Variable:

Dim ie As New InternetExplorerie.Visible = False

ie.Navigate "ftp://example.com"

Do While ie.BusyDoEventsLoop

Dim htmlDoc As HTMLDocumentSet htmlDoc = ie.documentDim links As ObjectSet links = htmlDoc.getElementsByTagName("a")

Dim link As ObjectFor Each link In linksIf link.innerText = "file.txt" ThenDim fileToDelete As StringfileToDelete = link.getAttribute("href")Exit ForEnd IfNext link

ie.DeleteUrlCacheEntry "ftp://example.com/" & fileToDelete

ie.Quit

Denken Sie daran, dass Sie über die entsprechenden Berechtigungen verfügen müssen, um Dateien vom FTP-Server zu löschen. Stellen Sie außerdem sicher, dass der richtige Pfad für die zu löschende Datei angegeben ist.

Das ist alles! Jetzt wissen Sie, wie Sie Dateien mit VBA Excel vom FTP-Server löschen können.

Umbenennen von Dateien auf einem FTP-Server mit VBA Excel

Das Umbenennen von Dateien auf einem FTP-Server mit VBA in Excel kann eine nützliche Funktion sein, wenn Sie den Dateinamen oder die Dateierweiterung auf einem Remote-Server ändern müssen. In diesem Abschnitt werden wir uns ansehen, wie dies zu tun ist.

Um mit dem FTP-Server zu beginnen, müssen Sie einen Link zur Microsoft Internet Controls-Bibliothek erstellen. Gehen Sie folgendermaßen vor, um dies zu tun:

1. Öffnen Sie den VBA-Modus in Excel, indem Sie Alt + F11 drücken.
2. Wählen Sie im Menü Extras und dann Links aus.
3. Suchen Sie in der Liste nach "Microsoft Internet Controls" und setzen Sie das Häkchen daneben.
4. Klicken Sie auf "OK", um die Einstellungen zu speichern.

Nachdem wir den Bibliothekslink installiert haben, können wir mit der Programmierung beginnen. Hier ist ein Beispielcode, mit dem Sie Dateien auf einem FTP-Server umbenennen können:

Sub RenameFileOnFTPServer()Dim FTP As New InternetExplorerDim FTPAddress As StringDim FTPUsername As StringDim FTPPassword As StringDim NewFileName As StringDim FileURL As String' Установка параметров FTP сервераFTPAddress = "ftp://ftp.example.com"FTPUsername = "username"FTPPassword = "password"' Установка нового имени файлаNewFileName = "newfile.txt"' Формирование URL файла для переименованияFileURL = FTPAddress & "/path/to/file.txt"' Подключение к FTP серверуFTP.Navigate FTPAddress' Ожидание завершения загрузки страницыDo Until FTP.Busy = False And FTP.ReadyState = 4DoEventsLoop' Ввод данных для авторизации на FTP сервереFTP.Document.All.Item("username").Value = FTPUsernameFTP.Document.All.Item("password").Value = FTPPasswordFTP.Document.forms(0).submit' Ожидание завершения авторизацииDo Until FTP.Busy = False And FTP.ReadyState = 4DoEventsLoop' Переименование файлаFTP.Document.parentWindow.execScript "ftp.rename('" & FileURL & "', '" & NewFileName & "')"' Ожидание завершения переименованияDo Until FTP.Busy = False And FTP.ReadyState = 4DoEventsLoop' Отключение от FTP сервераFTP.QuitMsgBox "Файл переименован успешно!", vbInformationEnd Sub

Im obigen Beispiel erstellen wir eine neue Instanz des InternetExplorer-Objekts, das wir verwenden, um mit dem FTP-Server zu arbeiten. Dann setzen wir die Parameter des FTP-Servers, den neuen Dateinamen und die URL der Datei, die wir umbenennen möchten. Danach stellen wir eine Verbindung zum FTP-Server her, geben die Autorisierungsdaten auf dem Server ein und benennen die Datei um.

Bitte beachten Sie, dass wir vor dem Umbenennen der Datei darauf warten müssen, dass die Seite geladen und auf dem FTP-Server autorisiert wurde. Daher verwenden wir Do-Until-Schleifen, um darauf zu warten, dass der FTP-Server die gewünschten Aktionen ausführt.

Nachdem die Datei auf dem FTP-Server umbenannt wurde, trennen wir uns vom Server und zeigen eine Meldung an, dass die Datei erfolgreich umbenannt wurde.

Jetzt wissen Sie, wie Sie Dateien auf einem FTP-Server mit VBA in Excel umbenennen. Diese Funktionalität kann in vielen Szenarien nützlich sein, die mit der Automatisierung von Dateiverarbeitungsvorgängen auf einem Remote-Server verbunden sind.