FTP Directory File Listing

[geoffcodd]

Hi all,

I have a module which I currently use to download files from ftp site, this works great with the only problem that I need to know the exact name of the file I wish to download.

The problem I have now is that the have started including the time stamp in file names so I can no longer use my current code.

What I need is a way to get the a file listing from the ftp site like I would do using filesearch on a local or network drive, but this doesn't seem to work on an ftp site.

If anyone could help me out with some ideas I world appreciate it.

Thanks
Geoff

 

[Jibbadiah]

Geoff,

You can create a .bat file and run the "ls" command against the server directory in question.

strParameters = "c:\temp\Download.txt"
strBAT = "c:\temp\Download.bat"

Open strBAT For Output As #1
Print #1, "ftp -s:" & strParameters & " IPaddress > c:\temp\dirlist.txt"
Close #1

Open strParameters For Output As #1
Print #1, <Username>
Print #1, <Password>
Print #1, "hash on"
Print #1, "cd <serverdirectory>"
Print #1, "ls"
Close #1

shell strBAT

This will create a file called c:\temp\dirlist.txt that you can then import into a table and display in your form... for later selection for download. Convoluted... but it works.

I have tested this on Windows NT pc against a Unix Server.

 

[geoffcodd]

Hi,

Thanks for the reply, I have

Function test()

strParameters = "c:\temp\Download.txt"
strBAT = "c:\temp\Download.bat"

Open strBAT For Output As #1
Print #1, "ftp -s:" & strParameters & " IPaddress > c:\temp\dirlist.txt"
Close #1

Open strParameters For Output As #1
Print #1, "Blah" 'I enter real login
Print #1, "Blah" ' I enter real password
Print #1, "hash on"
Print #1, "cd ftp://1234.co.uk/ibisprod/in/sap/account/processed"
Print #1, "ls"
Close #1

Shell strBAT
End Function

But when I run I get an ftp error, can you tell me if I have entered everything correctly.

Thanks
Geoff

 

[Jibbadiah]

Not quite.

I have attached 2 files for you.
Download_BAT.txt
-- update the <IPaddress> with the required IP
-- rename the file to Download.bat

Download.txt
-- update your user and password.

save them to c:\temp
double-click the .bat file and then view the dirlist.txt file to see what has happened (you may have to refresh the directory to see the new file).
Once you get it to work you can try and create it through the code mentioned above.

 

[geoffcodd]

Brilliant, got it working thanks for your help, your a life saver

Thanks
Geoff

 

[geoffcodd]

Hi James,

Just one last question, I get this to run fine only problem is that the command window stays open how can I get this to close once it has run.

Thanks
Geoff

 

[Jibbadiah]

After the ls, add another line that says "Bye" (or "exit"... but i think it is "bye"). This should close the window on completion of the ftp.

 

[geoffcodd]

Brilliant thanks very much

 

[Kinger43]

I need to import a file from an FTP into an Access table. I do know the exact file name that I need to bring in. How do I modify the code above to do this? I have no experience with VBA so any help that I can get would be great.

 

[Jibbadiah]

ftp

Kinger

create a file called parameters.txt with the following details in it
-------------------------------------------------------------
<user>
<password>
Hash on
binary
cd <FTPdirectory>
prompt off
get <filename.txt>
-------------------------------------------------------------

-- update <user> with username
-- update <password> with FTP password
-- update <FTPdirectory> with directory where file is (something like /dir/aaa)
-- update <filename.txt> with full file name including extension

create a 2nd file called download.bat with the following details in it
-------------------------------------------------------------
c:
cd c:\temp
ftp -s:c:\temp\parameters.txt <IPADDRESS>
-------------------------------------------------------------

-- update <IPADDRESS> with your ip address for the ftp site or server

save both files to c:\temp
double-click the .bat file and you should hopefully download the file to your c:\temp directory.

You can now call the .bat file in code through shell or use RunApp in a macro.
Now you can import the file through code... docmd.TransferText

Change the directories to suit what you want.

 

[Kinger43]

I've got the files made, when I run the .bat file it brings up a DOS prompt for a split second then it closes and nothing seems to happen. My FTPdirectory is ftp://www02.commandcenter.com. Is this the directory, or is only part of this the directory? Should the file name include this directory or should it just be filename.txt?

 

[Jibbadiah]

Look at this website...

http://www.platte.com/support/DOSInstructions.html

It gives instructions for downloading from an ftp site using DOS command line.
If you can get it to work this way then you should be able to translate the instructions and parameters to the technique that I have mentioned earlier.

Note, if you are trying this at work it is possible that your company have disabled the command prompt or blocked access with a firewall.

 

[Kinger43]

I got it working now. Thanks for that link and all your help.

 

[Jibbadiah]

No problem... glad I could help.