Get names of files in ftp folder

[eredd]

I am trying to figure out how to get a list of the files residing in a folder on an ftp site and put them into an Access 2010 table. I could then put them in a combo box so the user can select the file title to open and view. (These files are pdfs of meeting minutes). I also need a button which will allow the secretary to add a new pdf to the folder--this would require a browse screen. I believe I may be in over my head! Any help would be appreciated. Thanks

 

[kjv1611]

eredd,

Here's a rather lengthy discussion from 4 years ago on the bigger topic of dealing with FTP files. I imagine you can get what you need from there:
thread705-1703957

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[eredd]

You are right, kjv1611, I should get what I need but guess I need some hand-holding.

I am obviously not entering the proper fields in the call for ftpList, since it isn't making the connection:
myFiles = FTPList("127.0.0.1 - FTP Server IP", "MyUserID", "MyPWD", "/my/folder/path/")

I entered the ip address of the website for the first field (obtained by pinging mysite.com - don't have a dedicated ip).
filled in userID and password for the next two, and
for the last field,("/my/folder/path/") I tried "mysite.com" (since I just type "ftp://mysite.com" to get to it. I have designated a folder in the root directory named "transfer" as the ftp, but I am actually needing a list of files in a folder within "transfer" named "Minutes". For the moment I am just trying to get a positive return from the transfer folder. I have also tried entering "/home/mysite/transfer/" and "/home/mysite/" for that field.

In the FTPlist function trying each of the above choices for the last field:
for the line: hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)
13369356 is returned for hOpen, hostname is the ip I entered, INTERNET_DEFAULT_FTP_PORT is 21, Username and Password are correct, and INTERNET_SERVICE_FTP is 1, so it seems to have established a connection.

Call FtpSetCurrentDirectory(hConnection, sDir) uses the hConnection value and sDir shows as whatever I entered as the folder path.

Call FtpGetCurrentDirectory(hConnection, sPath, Len(sPath)) -sPath is "" (don't know where it gets this) -- when it gets to the line:
hFind = FtpFindFirstFile(hConnection, "*.*", pData, 0, 0), hFind is returned empty so it quits.

How to troubleshoot? Thanks!

 

[strongm]

Try my much shorter code in that thread ... (I'm biased, obviously)

 

[strongm]

In fact, here's a slightly cut down minimalist version:

[blue]Option Explicit

[green]' Requires references to Microsoft Shell Controls and Automation[/green]
Public Sub Example()
    Dim myFolderItem As FolderItem
    
    For Each myFolderItem In ftpList("speedtest.tele2.net/upload", "anonymous", "anonymous") [green]' username: anonymous, password: anonymous[/green]
        Debug.Print myFolderItem.Name, myFolderItem.IsFolder [green]' demo[/green]
    Next
End Sub

[green]' Returns a FolderItems collection from the FTP server[/green]
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[/blue]

In your case you'd replace

"speedtest.tele2.net/upload" with "FTPServerIP/Transfer/Minutes"

(oh, and the appropriate username and password)

 

[eredd]

I have spent several days trying unsuccessfully to make this work, and have run out of ideas. There must be a solution!

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=#EF2929]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. The above function throws error 91 when I use the full username. When I drop the "@ncswga.com" from the username the error disappears, even though it fails to return the list. When I single-step and check values, myFolderItem.name shows <Object variable or With block variable not set>, and therefore MyFiles remains = "". BTW, above code works perfectly on a ftp site at another host that does not require the "@mysite.com" part of the username.

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? If I change my function as suggested by Strongm I have the same results.