0

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.

0

1 Answer 1

4

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.

enter image description here

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

Sign up to request clarification or add additional context in comments.

1 Comment

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/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.