Solved Copy all files of all subfolders via FTP

[perlfan]

Hi there, I'm working on pulling order data from our web shop into MS Access which works pretty well. Now I'd like to copy files uploaded by customers within the order process onto our drive via FTP. Unfortunately, I only know part of the path - my URL/order_ID/unique_ID/files .... As I cannot retrieve the unique ID, I'm looking for a way to determine all subfolders contained in my URL/order_ID/ and then copy all files in these subfolders via FTP. Maybe there is someone who can help me. Thank you, Frank

 

[Gasman]

My first point of call.

ftp find folders using vba example - Google Search

Also search here, as this site comes up in that Google search?

 

[perlfan]

I already did a lot of googling However there was nothing really useful.

 

[Gasman]

Well that link I posted, walked through all the folders for all the files. That is what I thought you were after?

 

[The_Doc_Man]

Using the Scripting.FileSystemObject, you can start from a given folder and get the collection of all files in that folder. Be warned that this is a recursive problem if it is possible to have orders within orders.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

From this link as your starting point, you can read up on the FileSystemObject by using the tree diagram on the left to navigate. The entry above the FileSystemObject is the Files collection. The entry below is the Folders collection. You can do all kinds of things using FSO including browse folders to find files. Up to you as to how you want to use it, but this is the 10,000 foot answer to your question.

 

[Gasman]

O/P is talking about FTP Doc?

 

[The_Doc_Man]

Yes, but also about finding the files. FSO can work across networks if there is a viable login that would support the FTP operation. The issue may also be dependent on whose FTP stack is in place, since there IS an FTP standard but there is also the fact that there are implementation-dependent issues with FTP.

The FTP command MGET can be used to try to get multiple files and can use wildcard selectivity. That may be helpful but would require some user experimentation to see if it will actually do what is desired.

 

[perlfan]

@Gasman
Thank you - it's true that I kind of ignored that one post where they were addressing my issue. With the help of that post I was able to put together a routine which now connects via ftp, looks through a certain folder for subfolders, and downloads all files from the subfolders to a local folder. Thanks to you all for your comments. Frank

Private Sub get_files()
'copy files from website
' Requires references to Microsoft Shell Controls and Automation
    Dim myFolderItem As Shell32.FolderItem
    Dim myFolderItem2 As Shell32.FolderItem
    Dim localFolder As Shell32.Folder
    Dim myShell As New Shell

    For Each myFolderItem In ftpList("yourftpserver/your_folder/" & Form!wooID, "username", "password") 'Each item could be a folder or a file
        Debug.Print myFolderItem.name, myFolderItem.IsFolder ' just for fun to illustrate stuff we can do
        If myFolderItem.IsFolder = True Then
                For Each myFolderItem2 In ftpList("yourftpserver/your_folder/" & myFolderItem.name, "username", "password") 'Each item could be a folder or a file
                    If myFolderItem2.IsFolder = False Then
                    Set localFolder = myShell.NameSpace("C:\your_local_folder\") ' or wherever your local folder is
                    localFolder.CopyHere myFolderItem2 ' copy the required item
                    End If
                Next
        End If
    Next
end sub

' Returns a FolderItems collection from the FTP server
Private Function ftpList(strFTPlocation As String, Optional strUser As String, Optional strPassword As String) As FolderItems
    Dim myShell As New Shell
    Dim strConnect As String
   
    If strUser <> "" Then strConnect = strUser & ":" & strPassword & "@"
    Set ftpList = myShell.NameSpace("FTP://" & strConnect & strFTPlocation).Items '("ftp://user:password@ftp.site.com")
End Function

 

[cheekybuddha]

' ...
For Each myFolderItem2 In ftpList("yourftpserver/your_folder/" & "/" & myFolderItem.name, "username", "password") 'Each item could be a folder or a file
'                                                              ^^^^^^^
'                                                                 |
'                                                           Do you want this too?
' ...

Do you want that extra forward slash in there? You already have one left from the preceding string literal.

 

[perlfan]

' ...
For Each myFolderItem2 In ftpList("yourftpserver/your_folder/" & "/" & myFolderItem.name, "username", "password") 'Each item could be a folder or a file
'                                                              ^^^^^^^
'                                                                 |
'                                                           Do you want this too?
' ...

Do you want that extra forward slash in there? You already have one left from the preceding string literal.

You're right, there should be only one slash, I just deleted a few things before putting the code here, that's when it happened ...