Ist das jetzt eine Anfrage, das komplett für Dich zu erstellen ???
Oder hast Du schon irgendetwas selbst gemacht und hast eine spezielle Frage.
Hier mal eine Reihe von API Funktionen, die Du für FTP in VBA benötigt (in Deinem Fall wohl nicht alle)
Code:
Option Explicit
Private Const MAX_PATH = 260
Private Const FTP_TRANSFER_TYPE_UNKNOWN = &H0
Private Const FTP_TRANSFER_TYPE_ASCII = &H1
Private Const FTP_TRANSFER_TYPE_BINARY = &H2
Private Const adVarChar = 200
Private Const adBigInt = 20
Private Const adDate = 7
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
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 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 FileTimeToSystemTime Lib "kernel32" ( _
lpFileTime As FILETIME, _
lpSystemTime As SYSTEMTIME) As Long
'Open the Internet object
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 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
'Send a file using FTP
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 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 FtpDeleteFile _
Lib "wininet.dll" _
Alias "FtpDeleteFileA" _
(ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
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 hFtpSession As Long, _
ByVal lpszDirectory As String) As Boolean
Private Declare Function FtpGetCurrentDirectory _
Lib "wininet.dll" _
Alias "FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, _
ByVal lpszCurrentDirectory As String, _
lpdwCurrentDirectory As Long) As Long
'Close the Internet object
Private Declare Function InternetCloseHandle _
Lib "wininet.dll" _
(ByVal hInet As Long) As Integer
Ein andere Möglichkeit wäre, die Datei einer Batch-Datei (quasi ein ftp Skript) und dem Windows-Scheduler regelmässig runterzuladen und das Aktualisieren davon getrennt in Excel durchzuführen bzw. mit Hilfe eines VBS Skripts im Anschluß zu erledigen.