Export list of FTP folders to Excel

[kgartin]

I want to export a list of all folders on our FTP server to Excel. Can I do this and, if so, how?

 

[kgartin]

...buried

 

[John_w]

Try this macro, changing the FTP login strings as required.

Option Explicit

Private Const cFTPport = 21

Public Sub Ftp_Directories_List()

    Dim FTPhostname As String, FTPusername As String, FTPpassword As String
    Dim FTPcommandsFile As String, FTPoutputFile As String, fileNum As Integer
    Dim FTPcommand As String
    Dim ws As Object 'WshShell
    Dim wsExec As Object 'WshExec
    Dim line As String
    Dim r As Long
    
    FTPhostname = "xxxx.yyyy.com"
    FTPusername = "username"
    FTPpassword = "password"
   
    'Create file containing ftp commands
    
    FTPcommandsFile = Environ("temp") & "\FTP_commands.txt"
    FTPoutputFile = Environ("temp") & "\FTP_output.txt"
    fileNum = FreeFile
    Open FTPcommandsFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , "open " & FTPhostname & " " & cFTPport
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , "user " & FTPusername & " " & FTPpassword
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , "dir -R"       'dir -R lists all files in current directory and subdirectories
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , "bye"
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
       
    'Construct ftp command line, specifying the file containing ftp commands and the ftp output file
    
    FTPcommand = Environ("COMSPEC") & " /c ftp -n -s:" & Q(FTPcommandsFile) & " > " & Q(FTPoutputFile)
    Debug.Print FTPcommand
    
    'Run ftp command synchronously, waiting for the command to return
    
    Set ws = CreateObject("WScript.Shell")
    ws.Run Command:=FTPcommand, WindowStyle:=0, WaitOnReturn:=True
    
    'Read output file line by line and write to Excel cells
    
    With ActiveSheet
        .Cells.ClearContents
        r = 0
        fileNum = FreeFile
        Open FTPoutputFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
        While Not EOF(fileNum)
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , line
            Debug.Print line
            If InStr(line, "./") = 1 Then
                .Range("A1").Offset(r, 0).Value = line
                r = r + 1
            End If
        Wend
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] 
    End With
    
    'Delete both output files because they contain the FTP username and password
    
    Kill FTPcommandsFile
    Kill FTPoutputFile
    
End Sub


Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function