VBA code to search & open file in FTP directory

londonman81

I am trying to write a VBA Excel macro which will open up a password protected FTP site, search for an Excel file and open it, before doing further operations on it.

I have got as far as writing a macro which will open the FTP site and enter in a username and password and enter into the relevant directory.

However I am now stuck on how to get it to find the relevant file and open it in Excel.

Does anyone know the VBA code for searching through files in a folder until the relevant one is found, and opening it?

I have found how to do this for a file saved on the computer, but unfortunately it doesn't seem to work for a file saved on FTP site.

Many thanks!

weegie.geek

Depending on the ftp server, you can do a raw ftp command of site search [thing to search for] - you can then parse the reply to find the full path of the file, cwd into the path and grab the file.

This depends on the server being used of course. A lot don't offer searching functionality. Some might have an extension that adds the function. Worth looking into.

It also depends whether the file was sent via ftp, or copied into the directory on the client side. If it wasn't created through the ftpd, it won't be in the dirlog so site search won't be able to find it. UNLESS a script is run to update the VFS. You could implement a command on the server to allow you to update the VFS before searching, depending on how much control you have over the server.