INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get names of files in ftp folder

Get names of files in ftp folder

(OP)
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

RE: Get names of files in ftp folder

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: VBA to get list of files on ftp server

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

RE: Get names of files in ftp folder

(OP)
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!

RE: Get names of files in ftp folder

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

RE: Get names of files in ftp folder

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

CODE

Option Explicit

' Requires references to Microsoft Shell Controls and Automation
Public Sub Example()
    Dim myFolderItem As FolderItem
    
    For Each myFolderItem In ftpList("speedtest.tele2.net/upload", "anonymous", "anonymous") ' username: anonymous, password: anonymous
        Debug.Print myFolderItem.Name, myFolderItem.IsFolder ' demo
    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 

In your case you'd replace

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

(oh, and the appropriate username and password)

RE: Get names of files in ftp folder

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

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 & myFolderItem.Name & ";"
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close