Execute FTP Commands with VBA

[mchac]

I have FileZilla installed and would like to pass commands to it from VBA.
This site: How to Execute FTP Commands With VBA | eHow.com
has some instructions on how to accomplish this but the last instruction says to add the following to VBA code which executes a batch file which then executes an FTP Command File:

Shell ("doFtp.bat"), vbHide '', vbMinimizedNoFocus

It goes on to say that the line contains two single quotes, without any character between them, immediately before the second comma. However this will only create a comment after the first single quote so the rest of the line is ignored.

Does anyone have an alternative command or methodology to execute FTP via VBA?

 

[rjwebgraphix]

It goes on to say that the line contains two single quotes, without any character between them, immediately before the second comma. However this will only create a comment after the first single quote so the rest of the line is ignored.

I believe you are misinterpreting what it is saying.

"Your VBA program will invoke the batch file that, in turn, will invoke the FTP client"

In other words, the VBA program runs the batch file. The batch file runs the FTP client. Previously on that page it gives an example of having VBA write your batch file for you, but if you know what you want the FTP to do, my opinion is you are better off writing the batch file and the vba code separately, although this requires that you're intimately familiar with FTP and how to use it. The vba code is given to you. You just need to write your batch file to run the ftp commands you need to run.

For further confirmation that this is the way to do it. The built-in FTP is a command-line tool for ftp access. vba could run the ftp for you directly, but then it would be up to the user to login and do whatever they're going to do. once you close a shell session, you've lost anything that is still there, so VBA can't run a series of ftp commands. That's what the batch file is for. It maintains the shell session and allows for multiple commands to run.

Hope this helps clarify things.

 

[mchac]

Thanks very much for your post.

I followed the Jonah's logic (I think) to create a line in VBA that launches the .bat which in turn calls the FTP client which then executes the commands in the FTP Command File. Just executing the Shell("doFtp.bat") works but I thought the rest of the line including the vbMinimizedNoFocus needed to also execute.

To your point about being intimately familiar with ftp, I know what I want to accomplish but I am definitely not familiar with ftp other than a general understanding.

Are you aware of resources I could read that give more detail on how via a batch file to call filezilla, connect to an ftp site (in this case it's ftp.sec.gov and they have some info on their website on connecting - user name: anonymous, password: email address), and download a file (save as a unique name then repeat)?

I know exactly the file paths and names and can open them by typing the path into an IE address bar but I need to automate the process as there are about 16k files.

My alternative is to dowload the entire data folder on the sec site but it unzips to about 800 gb and I don't want to chew up that much space locally so I'd like to just get the files I need.

Any thoughts are greatly appreciated.

 

[rjwebgraphix]

There might be a way you could do that with a browser and excel by logging in, getting filenames and using VBA to do it, but that one is beyond me. I don't do much with internet automation.

I forgot you were talking fileZilla, which is not the built-in ftp client. The batch file and built-in FTP is what that page refers to in creating your batch file launch, execute, running commands etc. The Built-in FTP is command line driven. FileZilla, if I'm correct, is a GUI client and for that, you'd be on your own, unless someone else knows how to get FileZilla to do it. Since the built-in ftp client is command line based, that is why you need a batch files.

This site talks a lot about the ftp client. Check it out if you're interested in using the batch file route to do the task...
Using the Built-In Windows FTP Client | Networking content from Windows IT Pro

I'm not sure what's available past WinXP. I refuse to upgrade to newer versions until I'm forced to by software I want to run. By my google searches, it does look like there is some form of command line ftp past WinXP, so what you read might be pertinant, but I'd suggest reading up on it if you want to try it. It would automate the process, but there are no pretty pictures to look at while it does the job. Like I said, it's command line, so you're staring at a black screen while it does the job. Although, if obtaining the files is the ultimate goal in this fashion, you don't really need excel to do any of that for you. Just a batch file and execute it manually.

Otherwise, I'd suggest going using Excel, VBA through IE to get the job done. Although, that one is a bit beyond me too as I don't do much with internet automation.

 

[mchac]

Thanks for your comment about the built-in FTP client. I sort of vaguely saw that but was too tired at that point for the lightbulb to go on. Thanks also for the link. That gave me a direction to in.

It turns out that I couldn't find sufficient documentation on filezilla command line and the windows FTP won't work because the server on the other end (the SEC Edgar database) requires passive mode and Windows ftp command line doesn't support that. I did find something called PassiveFTP (www.passive-ftp.com) that seems by default to work in passive mode.

After much pain and gnashing of teeth I got it to work for a single file ... though it's not saving the file where I thought it would. Once I get the save location sorted out I can move to writing some VB to automate the process. Fingers crossed I can turn the page on this part of the project today.

Thanks again for your comment about WinFTP. I may still be spinning my wheels if you hadn't pointed that out.

M

 

[rjwebgraphix]

I can't say much about passive-ftp, I'd have to read up on that myself, although, if it's anything like the built-in ftp client, then the directory/folder you load it from is the one it will copy the file to. In theory, if executing from VBA, a Chdir "path" should put you in the directory it will download to. Although, I'm not familiar with the program specifically, so it could operate differently.

Do you know the path to the file(s) you want to download on the server? Last night, for giggles, I used the built-in ftp to connect to the server you provided using the anonymous login and could browse around, but I didn't know what I was looking for.

Out of curiosity, how did you determine that it requires passive ftp? I'm wondering if it still is possible, but something was amiss.

 

[John_w]

connect to an ftp site (in this case it's ftp.sec.gov and they have some info on their website on connecting - user name: anonymous, password: email address), and download a file (save as a unique name then repeat)

I know exactly the file paths and names and can open them by typing the path into an IE address bar but I need to automate the process as there are about 16k files.

If you know the exact file path (URL) then you could try URLDownloadToFile, like this (which works for me):

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
  
Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" _
    (ByVal lpszUrlName As String) As Long
  
Private Const BINDF_GETNEWESTVERSION As Long = &H10


Public Sub Test()
    DownloadFile "ftp://anonymous@ftp.sec.gov/edgar/full-index/2000/QTR1/company.zip", "C:\2000_QTR1.zip"
End Sub

Private Function DownloadFile(URL As String, LocalFileName As String) As Boolean
    
    Dim RetVal As Long
    
    DeleteUrlCacheEntry URL
    RetVal = URLDownloadToFile(0, URL, LocalFileName, BINDF_GETNEWESTVERSION, 0)
    If RetVal = 0 Then DownloadFile = True Else DownloadFile = False

End Function

 

[mchac]

RJ, I had to scratch my head on the directory issue for a while. I even started to post a question then had a thought that solved it. The batch file and the .txt file that gives commands to the ftp software were both in the same directory and when I double clicked the batch file to launch the ftp, the .xls I wanted to download ended up in the same directory as the .bat and .txt. All good so far.

So then I tried to execute the batch file by a VBA shell function. The download worked but the .xls file was put one directory level up. I quadruple checked the paths etc etc and just couldn't figure it out. Then on a whim I searched the passive-ftp.com site I found an lcd command that lets you specify the target directory. With that specified I can use Shell and get the .xls copied to the right place.

I'm not sure what was going on since the batch double click and VBA shell(batchfile) should provide the same result in the same place. But with the lcd command I can get this to do what I need for a single file. (Though the unknown is eating at me a bit). Now, I'm working on a loop to download multiple files (I have about 16k to get so need to automate the process).

As far as passive mode, the SEC site has info for ftp users:
Information for FTP Users
Near the bottom there is a Troubleshooting section and the first of the two problems they list is operating in active mode - the FTP requires passive mode. If interested, or if you have trouble sleeping some night, there is a good description of active vs passive ftp here:
http://slacksite.com/other/ftp.html

Here's an example path to a folder on the SEC server:
www.sec.gov/Archives/edgar/data/1084869/000110465913008750

That gets you into a directory for 1 800 Flowers, as an example, and in there it's the Financial_Report.xls that I want.

You could type the the path and the file name into a browser address bar and the file downloads:
www.sec.gov/Archives/edgar/data/1084869/000110465913008750/Financial_Report.xls

 

[mchac]

John, that's really interesting and could allow me to bypass all the ftp pain, though the details of how this works is over my head. I tried to use your code but replaced target address in Test() with:

Public Sub Test()
DownloadFile "ftp://anonymous@ftp.sec.gov/Archives/edgar/data/1084869/000110465913008750/Financial_Report.xls", "C:\Users\MC\My Documents\Work\Trade Receivables\Data\financials\1800flowersfinancials"
End Sub

It seems to execute and I don't get an error but I don't seem to get a file either.

I also tried
DownloadFile "www.sec.gov/Archives/edgar/data/1084869/000110465913008750/Financial_Report.xls" .. etc
without success.

Could you try that ftp address and let me know if it works for you?

Thanks again.

 

[rjwebgraphix]

Now that is sweet! I just pulled down the file using that method and this would make things 1000 times easier.... Here's what I used. I think your pathtofile might be off.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
  
Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" _
    (ByVal lpszUrlName As String) As Long
  
Private Const BINDF_GETNEWESTVERSION As Long = &H10


Public Sub Test()
    DownloadFile "ftp://anonymous@ftp.sec.gov/edgar/data/1084869/000110465913008750/Financial_Report.xls", "C:\rj\excel\Financial_Report.xls"
End Sub

Private Function DownloadFile(URL As String, LocalFileName As String) As Boolean
    
    Dim RetVal As Long
    
    DeleteUrlCacheEntry URL
    RetVal = URLDownloadToFile(0, URL, LocalFileName, BINDF_GETNEWESTVERSION, 0)
    If RetVal = 0 Then DownloadFile = True Else DownloadFile = False

End Function