Looking for VBA code to create a list of files on a FTP server. Ideally it would be great to include the file attributes such as date/time file was created.
1 Answer
I create class named FileInformation and a function getFileList that will return an array of FileInformation. GetFTPFileList allows you to use getFileList with a ftp server.
Class FileInformation
Option Explicit
Public FilePath
Public FolderPath
Public FileExtension
Public Sub setValues(localRoot, fso, f)
FilePath = f.Path
FolderPath = f.ParentFolder.Path
FileExtension = fso.GetExtensionName(FilePath)
End Sub
The rest of the code goes into a normal code module.
Sub PrintFileInformation()
Const Username As String = ""
Const Password As String = ""
Const ftpSite As String = ""
Const ftpRoot As String = ""
Dim arFiles
Dim i As Long
arFiles = getFTPFileList(Username, Password, ftpSite, ftpRoot)
For i = 0 To UBound(arFiles)
Debug.Print arFiles(i).FilePath
Debug.Print arFiles(i).FolderPath
Debug.Print arFiles(i).FileExtension
Next
End Sub
Function getFTPFileList(Username As String, Password As String, ftpSite As String, ftpRoot As String)
ftpRoot = "ftp://" & Username & ":" & Password & "@" & ftpSite & "/" & ftpRoot
getFTPFileList = getFileList(ftpRoot)
End Function
Function getFileList(localRoot As String, Optional fld, Optional ftpArray)
Dim fso, f, baseFolder, subFolder, ftpFile, i
Set fso = CreateObject("Scripting.Filesystemobject")
If IsMissing(fld) Then
Set baseFolder = fso.GetFolder(localRoot)
Else
Set baseFolder = fld
End If
For Each f In baseFolder.Files
If IsMissing(ftpArray) Then
ReDim ftpArray(0)
Else
i = UBound(ftpArray) + 1
ReDim Preserve ftpArray(i)
End If
Set ftpFile = New FileInformation
ftpFile.setValues localRoot, fso, f
Set ftpArray(i) = ftpFile
Next
For Each subFolder In baseFolder.SubFolders
getFileList localRoot, subFolder, ftpArray
Next
getFileList = ftpArray
End Function
1 Comment
Shekhar Nalawade
I am getting path not found error when i reach the line Set baseFolder = fso.GetFolder(localRoot). My ftpRoot variable is having data as USERNAME:PASSWORD@ftp://fts.factset.com/datafeeds/fundamentals/…
