Need Vba Code for listing the Filenames in a FTP server 2

[sarathi57]

Hi,

I need to login to a ftp site and list all the filename and the size of the file in all the folders and sub fodlers recursively.

Is there an option to do that with access vba?

I am able to do that for a single folder. But i need to do for the entire folders and sub folders in that FTP site.

Please help with your comments and thoughts.

regards
Sarathi.

 

[RonRepp]

Here is an example of some code I wrote years ago, however, it was done in VB6. I hope it helps.

''(iNet1 is an Internet Control)

Public Sub UploadFiles()
    
    Dim FSO, txtFile, F
    Dim RetString
    Dim i As Integer
    Dim j As Integer
    Dim Pos As Integer
    Dim strFile As String
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
        
    frmFTP.Label1.Caption = "Starting RepProductions Upload"
   
   
Set FSO = CreateObject("Scripting.FileSystemObject")
Set F = FSO.OpenTextFile(strFilePathName, 1, False)
i = 0

Do While F.AtEndOfStream <> True
    ReDim Preserve FileArray(i)
    RetString = F.ReadLine
     Replace RetString, vbCrLf, ""
    FileArray(i) = RetString
    i = i + 1
Loop
F.Close
   
    frmFTP.Label1.Caption = "There are " & i & " Files to Upload"

    ' Get file count
    j = i
    
    
    With frmFTP.Inet1
        .URL = FTPSite
        .UserName = UserName
        .Password = Password
        .RemotePort = iPort
        .RequestTimeout = 360
        DoEvents
        
    End With
    
    frmFTP.Inet1.Execute , "DIR"
    DoEvents
    
    For i = 0 To UBound(FileArray) ' - 1
        
        frmFTP.pb1.Max = j
        frmFTP.pb1.Value = frmFTP.pb1.Value + 1
        'If i = j Then Exit For ' bStopLoop = True
        
        Dim P2 As Integer
        P2 = InStrRev(FileArray(i), " ")
    If P2 > 0 Then
        'write bad file here.
        WriteLog "Space in Filename " & FileArray(i)
        GoTo NEXTI
        
    Else

        Pos = InStrRev(FileArray(i), "\")
        strFile = Mid(FileArray(i), Pos + 1, Len(FileArray(i)))

        Do While frmFTP.Inet1.StillExecuting = True
            DoEvents
        Loop
        
        frmFTP.Inet1.Execute , "Send " & FileArray(i) & " " & strFile
        DoEvents
        
        WriteLog "Successfully Sent " & strFile

        frmFTP.Label1.Caption = "sending " & strFile
        
    End If
NEXTI:
    
    Next
    
End Sub

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[sarathi57]

Hi,
Thanks for the Reply.
However the code that you have shared doesnt meet my requirment.
You have shown how to upload the files.
But I dont need to download or upload any file from FTP. I just need to get the file names and File size of each file that are present in the Folders and Sub folders of the FTP server.

Anyway thanks for taking time and repying me

Regards
Sarathi

 

[RonRepp]

Sarathi:

I did a quick Google of your request and came up w/this. It's through VB.net, but you should be able to get the gist of it.

http://www.codeproject.com/Questions/205367/VB-NET-read-all-file-inside-a-folder-from-a-FTP-Se.aspx

IHTH,

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[strongm]

Might I suggest that a quick search of this forum might have found thread707-1647110, where the code example I provide might prove useful (although the example provides path info it can easily be modified to request the .size property instead)

 

[sarathi57]

Hi Friends,
Thanks for the link that you have posted. I will have a look and let you know if that helped out my problem.

Thanks again for your time.

Regards
Sarathi

 

[sarathi57]

Hi Stongm,

Thanks for posting the link. It worked out a bit.
However this is not giving me the file names that are present in the sub folders.

assume i have a folder strucutre like

Root/Folder1/subfolder1/a.txt
Root/Folder1/Subfolder2/b.txt
Root/Folder1/file1.txt

The code gives me the file names of the Folder 1 alone. its not getting the file name from the sub folders.

can you please help me out in getting the file names from the Sub folders as well?

Thanks in advance.

 

[strongm]

Sure. Try this minor modification:

[blue]Public Sub example()
     [green]' Requires a reference to Microsoft Shell Controls and Automation[/green]
    Dim ftpfolder As Shell32.Folder
    Dim ftpshell As Shell32.Shell
    Dim sGetFile As String
    
    sGetFile = "ftp://"
    [green]' if we require a username and password:
    ' sgetFile = sGetFile & "myusername:mypassword@"[/green]
    sGetFile = sGetFile & "ftp.nai.com/commonupdater/"
    sGetFile = sGetFile & "current/"

    Set ftpshell = New Shell32.Shell
  
    Set ftpfolder = ftpshell.Namespace(sGetFile)
    WalkFolder ftpfolder
End Sub

Public Sub WalkFolder(ftpfolder As Shell32.Folder)
    Dim item As Shell32.ShellFolderItem
    
    For Each item In ftpfolder.Items
        If item.IsFolder Then
            WalkFolder item.GetFolder
        Else
             Debug.Print item.Path, item.ExtendedProperty("Size")
        End If
    Next
End Sub[/blue]

 

[sarathi57]

Dear Strongm,

Thanks a lot for your help. It worked out the way exactly i wanted. You helped me out in quick time.

I am sorry if i am bothering too much, But i need one last help from you on the above code.

The code you sent gives me all the file names and the File size.

I am stuck up here with files that are in .zip format.
Is there a way, where i can list the file names and file size that are present in the .zip files?

example: the code returns the file name as
\root\folder1\subfolder1\sample1.zip
\root\folder1\subfolder1\sample2.zip

assume the zip file sample1.zip has 2 files as shown below
1.a.txt (20KB)
2.b.txt (30KB)

I need to show the above file names and file size as well.
I dont know if this is possible or not as i am new to the VBA coding. I am also exploring the google to get info, but i could not reach where i want.

could you please help with this final hurdle?
Thanks once again for all your effort.

Regards
Sarathi

 

[strongm]

FTP predates zip files by some margin (1971 versus 1989) and, as such, includes no capability to examine zip files.

Any solution would require actually downloading the zip file, and then examining the contents locally

However, once you have downloaded the file, examining the contents is easy. Here's an example which I've tried to make as similar as possible in approach as the WalkFolder routine:

[blue]Public Sub WalkZipFile(strZipFile As String) [green]' provide complete path to zip file[/green]
    Dim item As Shell32.ShellFolderItem
    
    With New Shell
        For Each item In .Namespace(strZipFile).Items
            Debug.Print item.Name, item.ExtendedProperty("Size") [green]' we don't use Path as this would give path on local machine[/green]
        Next
    End With
End Sub[/blue]

 

[sarathi57]

Hi Strongm,

Thanks a lot for all your effort.

I dont know how to appreciate you for the help you are providing to others in this forum.

Regards
Sarathi