Get list of files names on a ftp server (1 Viewer)

TimTDP · Feb 18, 2013

I have the following very neat code to get the names of the files on an ftp server

Code:

Dim myFolderItem As FolderItem
Dim myFiles As String

GetFTPDetails
myFiles = ""
For Each myFolderItem In ftpList(pubInternetDomainName & "/" & pubWebOrderDownloadOrdersFromInternetFolder & "/", pubInternetFTPUserName, pubInternetFTPPassword)  'Each item could be a folder or a file
    'Debug.Print myFolderItem.Name ', myFolderItem.IsFolder Is it a folder?
    myFiles = myFiles & myFolderItem.Name & ";"
Next

Me.lstAvialableFilesForDownload.RowSource = myFiles

When I open the database and run the code, the correct file list is returned. If, whilst the database is open, I add or delete a file on the server and rerun the code then the original file list is returned. If I close / open the database and run the code the correct list is returned.

It is almost as though the files names on the first run are kept in cache/memory and the cache/memory is returned, without being updated.

Do I need to add code to clear the cache/memory?

Many thanks

Pat Hartman · Feb 19, 2013

What does GetFTPDEtails do?
What does ftpList do?
Please post the code for those also. It may be that some object is not being closed.

TimTDP · Feb 19, 2013

GetFTPDetails populated the ftp details like domain, user name, password and folder locations

Code:

Public Sub GetFTPDetails()

pubInternetDomainName = DLookup("FTPDomain", "tblCompanyDetails")
pubInternetFTPUserName = DLookup("FTPUserName", "tblCompanyDetails")
pubInternetFTPPassword = DLookup("FTPPassword", "tblCompanyDetails")

pubWebOrderUploadDataFromLocalFolder = DLookup("WebOrderUploadDataFromLocalFolder", "tblCompanyDetails")

pubWebOrderUploadDataToInternetFolder = DLookup("WebOrderUploadDataToInternetFolder", "tblCompanyDetails")
pubWebOrderDownloadOrdersFromInternetFolder = DLookup("WebOrderDownloadOrdersFromInternetFolder", "tblCompanyDetails")

pubWebOrderDownloadOrdersToLocalFolder =  DLookup("WebOrderDownloadOrdersToLocalFolder", "tblCompanyDetails")
pubWebOrderMoveDownloadOrdersToInternetFolder = DLookup("WebOrderMoveDownloadOrdersToInternetFolder", "tblCompanyDetails")
pubWebOrderMoveLocalProcessedFilesToFolder = Lookup("WebOrderMoveLocalProcessedFilesToFolder", "tblCompanyDetails")

End Sub

ftpList

Code:

Public Function ftpList(strFTPLocation As String, Optional strUser As String, Optional strPassword As String) As FolderItems
' Returns a FolderItems collection from the FTP server

'Works. Bug in that list is not updated after 1st rub. Database needs to be restarted

Dim myShell As Shell
Dim strConnect As String

Set myShell = New Shell
If strUser <> "" Then strConnect = strUser & ":" & strPassword & "@"
Set ftpList = myShell.Namespace("FTP://" & strConnect & strFTPLocation).Items   '("ftp://user:password@ftp.site.com")

End Function

Pat Hartman · Feb 20, 2013

GetFTPDetails() runs 9 queries (each DLookup() runs a separate query) when you should run one. Use DAO and the .OpenREcordset method to read the one record and just reference the fields it brings back.

Try closing myShell after the Set statement.

ellenr · Aug 7, 2017

Struggling here. I am getting Runtime error 91 on the code line:

Code:

Set ftpList = myShell.Namespace("FTP://" & strConnect & strFTPLocation).Items

If I get past this point, I will have a few other questions, but first things first! Thanks

arnelgp · Aug 7, 2017

Public Function ftpList(strFTPLocation As String, Optional strUser As String, Optional strPassword As String) As FolderItems
' Returns a FolderItems collection from the FTP server
'Works. Bug in that list is not updated after 1st rub. Database needs to be restarted

Dim myShell As Object
Dim strConnect As String

Set myShell = CreateObject("Shell.Application")
If strUser <> "" Then strConnect = strUser & ":" & strPassword & "@"
Set ftpList = myShell.Namespace("FTP://" & strConnect & strFTPLocation).Items '("ftp://user

assword@ftp.site.com")
Set myShell = Nothing
End Function

The_Doc_Man · Aug 7, 2017

Do I need to add code to clear the cache/memory?

FTP protocol does not support browsing like this. What is happening is that FTP is being treated as an application object. You command it to get a list of files from a particular folder. But the rest of the code has interpret the files one at a time so there is an implied buffering effect.

You need to re-run the FTPLIST routine, essentially start over again from the top by reloading the buffer, to see what is there after your putative delete operation.

ellenr · Aug 7, 2017

arnelgp, I am still getting the run-time error 91. I have added Microsoft Shell Controls and Automation to my references, which made no difference.

arnelgp · Aug 8, 2017

9¹ means the shell object is not created.

ellenr · Aug 12, 2017

I have spent several days trying unsuccessfully to make this work, and have run out of ideas. When I single-step and check values, myFolderItem.name shows <Object variable or With block variable not set>, and therefore MyFiles remains = "".

Code:

Private Sub Command62_Click()
Dim myFolderItem As FolderItem
Dim localFolder As Folder
Dim myShell As New Shell
Dim MyFiles As String
MyFiles = ""
For Each myFolderItem In ftpList("ncswga.com" & "/", "transfer", "password")   'Each item could be a folder or a file
    MyFiles = MyFiles & [COLOR="Red"]myFolderItem.Name[/COLOR] & ";"
Next
Debug.Print MyFiles

End Sub

Function ftpList(strFTPLocation As String, Optional strUser As String, Optional strPassword As String) As FolderItems
' Returns a FolderItems collection from the FTP server

Dim myShell As Object
Dim strConnect As String

Set myShell = CreateObject("Shell.Application")
If strUser <> "" Then strConnect = strUser & ":" & strPassword & "@"
Set ftpList = myShell.NameSpace("FTP://" & strConnect & strFTPLocation).Items   
Set myShell = Nothing
End Function

When I subscribed for the website and designated a folder named "transfer" as the ftp folder, I was told to use "transfer@ncswga.com" as the username when accessing the ftp site. When I was having error 91 in the above function, I dropped the "@ncswga.com" from the username and the error disappeared, even though it failed to return the list. If I put the entire line "FTP://" & strConnect & strFTPLocation into the url line of Chrome, it will open the ftp site if I use the "transfer@ncswga.com" as the username, but not if I leave off the @ncswga.com. So, I suspect the reason the function isn't returning anything is because the username needs the @ncswga.com. How can I put it into the ftplist strConnect without throwing the 91 error?

ellenr · Aug 13, 2017

To follow up:

Code:

Set myShell = CreateObject("Shell.Application")
If strUser <> "" Then strConnect = strUser & ":" & strPassword & "@"
Set ftpList = myShell.NameSpace("FTP://" & strConnect & strFTPLocation).Items

I have only listed the problem lines. This vba code works correctly, downloading a list of files on another of my ftp sites. The download on the one I need, however, does not work. The only difference is that the strUser is "transfer@mysite.com" (a requirement of the webhost company) rather than just "transfer". It apparently gets confused with the second @ in the "FTP://transfer@mysite.com:mypassword@mysite.com". Is there a solution? Thanks.

ashleedawg · Aug 13, 2017

I think you're close. The "full URL" method is:

Code:

Your examples like
            ftp://user:password@[COLOR=Red]ftp[/COLOR].site.com
must not have [COLOR=red]@[/COLOR] or [COLOR=red]ftp [/COLOR]more than once in any case.

Correct:
           [COLOR=seagreen]ftp://user:pass@subdomain.domain.ext[/COLOR]  
like
          [COLOR=SeaGreen] ftp://myusername:mypassword@mysubdomain.ucoz.net[/COLOR]

You can test this method by typing it in the Address Bar in any[SIZE=1](?)[/SIZE] browser.Or, also in the browser:
           ftp://mysubdomain.ucoz.net
           ...and get prompted for the userid & pw

Or, in an FTP Client like [URL="https://filezilla-project.org/download.php?platform=win32"]Filezilla[/URL]:
           [B]host[/B]:   mysubdomain.ucoz.net  (No "[COLOR=red]ftp[/COLOR]" since it's assumed)
           [B]userid[/B], [B]password [/B]in separate boxes. (No [COLOR=Red]@[/COLOR]'s)
           (Also specify "Logon Type" as "Normal".

Whatever instructions you were given probably have all the right pieces but it sounds like it's not put together properly.

I know you're end-goal is to load the files from VBA but it's probably best to first troubleshoot your logon with more reliable methods.

Then, moving along, here is a link to a slick FTP VBA-based FTP File-Transfer app using Internet Connections as opposed to Scripting.

ellenr · Aug 16, 2017

ashleedawg, I looked at the slick File Transfer app but can't figure out how to adapt it to getting a list of files on the ftp site in question. I understand how to ftp into the site and how to upload files--I am the one who has put the files there in the first place-it only works if I use the full "transfer@mysite.com" as my username. What I am attempting to do is write vba code behind a button in an Access program to download updated list of files and put them in a table (for a combo box) so a user can choose one to open. These are organization's meeting minutes (pdfs) spanning 50 years and continuing into the future (after I am no longer around). I would love it if you could show me how to do it without using scripting, since it doesn't look like there is a solution to doing it my way.

BTW, your code is great--am sure I will be using at least some of it in the future. Thanks for sharing!

The_Doc_Man · Aug 16, 2017

There MIGHT be a roundabout way of doing this.

Look into the specifics of your FTP utility, where it should be possible to use command-line option ">" (specify output) to send the file-list output to a file. Issue the command via the SHELL command. If your specific FTP utility has a command line option to give you raw file info and nothing else (like, no headers, no prefix, etc.) then you might be able to use that directly. If so, you might be able to take that file into a temporary table using a stored import specification for ImportText.

If you have to get the headers and such, then ImportText might be trickier. In that case, just open the file for input in text mode (i.e. ordinary file open). Loop through the file via InputLine. For each line, use InStr to look for things that are known to be in the (unwanted) header lines and don't process them. On the lines you want to keep, use the Mid function to extract the data you wanted (knowing the fixed columnar pattern of the output of FTP's LS or DIR command as appropriate) and use a recordset to update your temporary table that you use for final insertion. For each field of the recordset, you are going to have a string but you could use things like CInt, CLng, CDat, etc. to convert to the proper final format.

I know it sounds hard but if you wanted a VBA method, this will do it. Further, this is the sort of "tool" that if you write it once, you can probably re-use it a LOT with minimal tweaking.

TylerJ · Feb 21, 2019

Just found this thread looking for the solution to the original problem.

Answer to the final problem is that URLs have a limited character set. Problem is the "@" in the user name (and possibly characters in the password as well). In a URL "@" needs to be replaced by "%40". 40 being the hex code for "@". Search "url syntax" for a more complete explanation. As I'm a new user, the system wouldn't let me enter a link.

Get list of files names on a ftp server (1 Viewer)

TimTDP

Registered User.

Pat Hartman

Super Moderator

TimTDP

Registered User.

Pat Hartman

Super Moderator

ellenr

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

The_Doc_Man

Immoderate Moderator

ellenr

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ellenr

Registered User.

ellenr

Registered User.

ashleedawg

"Here for a good time"

ellenr

Registered User.

The_Doc_Man

Immoderate Moderator

TylerJ

New member

Similar threads

Users who are viewing this thread