Listing files in Server Directory

[Freshman]

Hi all,

Looking for some simple code to list all file in a spesific ftp server directory.
All I can find is some "DOS" code method.

Is there not a "Windows command" to do the same thing?

Thanks
Pierre

 

[mdlueck]

Looking for some simple code to list all file in a spesific ftp server directory.

FTP does not involve specifically DOS nor Windows. FTP stands for File Transfer Protocol, and is an Internet standard.

What methods/functions exist depends on which FTP implementation you are talking about.

Checking the A2007 online help, I did not encounter FTP capabilities native to VBA. Do you have some sort of FTP OCX installed on your system, providing VBA access to FTP capabilities? Perhaps provide an example so we can understand the context.

 

[mdlueck]

Do you have some sort of FTP OCX installed on your system, providing VBA access to FTP capabilities?

Oh, I guess MS ships a separate OCX control to gain access to FTP servers:
"How to Use FTP From VBA"
http://www.ehow.com/how_8772610_use-ftp-vba.html

Is this what you are talking about?

 

[Freshman]

I have a FTP Server containing files for diffrent Clients. I want to populate a listbox with all file names of the files on my server.

 

[mdlueck]

I have a FTP Server containing files for diffrent Clients. I want to populate a listbox with all file names of the files on my server.

And in order to accomplish that you need some sort of FTP client software running on the Access computer in order to connect to the FTP server.

I suggest you start with researching the suggestion I came up with in my second reply. That appeared to be the most reliable method of connecting to an FTP server with the VBA programming language.

 

[boblarson]

See if this helps:

http://www.databasejournal.com/features/msaccess/article.php/3513061

 

[mdlueck]

See if this helps:

http://www.databasejournal.com/features/msaccess/article.php/3513061

Note: That one uses command injection to the ftp.exe program, which I would consider less reliable than using a VBA compatible OCX object.

 

[boblarson]

Note: That one uses command injection to the ftp.exe program, which I would consider less reliable than using a VBA compatible OCX object.

Just an FYI - VBA OCX objects are notorious for problems. That is why I suggest not using ActiveX controls unless you have absolutely no alternative. The reason is that the ActiveX controls need to be installed on every machine that uses them, and they aren't always there, so you need to have an installation routine. Also, Microsoft many times will do something like either remove them from Access or do some changes which require you to go scrambling for an alternative.

So, in my opinion - avoid ActiveX controls unless there is no alternative.

 

[Freshman]

Hi guys,

This is the code I'm currently using:

FTPServerDir = "/wwwroot/ClientBE/"
FileNo = FreeFile
ChDir "C:\LabourSoft\Data\FTP\"
Open "ftp-cmd.txt" For Output As #FileNo
Print #FileNo, "open [URL="ftp://ftp.laboursoft.co.za"]ftp.laboursoft.co.za[/URL]"
Print #FileNo, "laboursoft.co.za"
Print #FileNo, "###Pass###"
Print #FileNo, "cd " & FTPServerDir
Print #FileNo, "ls - C:\LabourSoft\Data\FTP\ServerDir.txt"
Print #FileNo, "close"
Print #FileNo, "quit"
Close #FileNo
On Error Resume Next
Shell "[URL="ftp://ftp.exe"]ftp.exe[/URL] -s:ftp-cmd.txt", vbHide

The problem is that it keeps locking up the txt file resulting in an error so I don't get the output I need.

Either I need to fix the error in my current code or go over to something like ftp.exe but I battle to find the syntax for the ftp script to extract the file names to a listbox.

Any help would be appreciated.

Thanks
Pierre

PS: Thanks Bob - I actually played around wth that sample base in your link. That too is using a "DOS" method like my code above. But looking at it I could not find my script error.

 

[boblarson]

Unfortunately, the last time I did something like this was about 4 years ago so I'm kind of rusty.

 

[Freshman]

Hi guys,

OK - I found my error. I was calling the above routine from 2 different events (onOpen and onCommandButtonClick) in the code which caused it to lock up the txt file.

All working now.

Thanks again for your input
Pierre

 

[mdlueck]

Just an FYI - VBA OCX objects are notorious for problems. That is why I suggest not using ActiveX controls unless you have absolutely no alternative.

Driving ftp.exe via a canned response file input is macro programming without error handling. Rx for disaster.

 

[Freshman]

Hi Michael, kindly explain please. I do have error handeling in my code (not shown in the extract) or were you refering to something else. Like to learn.

Cheers
Pierre

 

[mdlueck]

Hi Michael, kindly explain please

Each operation's return code could be checked if you were using an Active-X FTP control verses needing to queue up a series of commands to be force-fed to ftp.exe and "hope nothing derails along the way".

Using the FTP object, you could check the return code of the server login, check the return code of changing directory, check the return code of each get/put operation, etc... and if something was wrong you could immediately react in your VBA code.

Scripting ftp.exe, at best you could connect / do one operation / disconnect over and over, and thus somewhat isolate where the trouble was.