Outlook VBA - Download from FTP VBA

[Stildawn]

Hi All

Can someone help me with this, I need to grab a file from ftp. But I dont think its like normal ftp coding.

Firstly I tried the code here: http://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html

But I dont have the Microsoft Internet Transfer Control library (in any of my software, Outlook, Word, Excel etc).

So I think that is out.

However I think our ftp at work is set up a bit weird. How I manually get the file is like this:

1. In a normal My Computer window, I navigate to a location called "pdf printer", taking the path from the My Computer window its this: "ftp://xx.xxx.112.220/" (where x's are actual numbers I've just removed them).
2. By clicking on this folder (it looks like a folder haha) we see a few other folders the one I want location is: "ftp://xx.xxx.112.220/HB/"
3. Now we see a list of PDF files, the weird thing is we can't simply click on a file and copy and then paste to our desktop. We have to open the file by double clicking.
4. This will then open the pdf into Internet Explorer, the full path in the address bar looks like it has made a temporary pdf file in: "C:\Users\tbaker\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\R8VOCGTQ\HB-11737261[1].pdf" for example.
5. We get it from here by using the built in adobe icons (either save as, or email) etc.

To access this ftp site there is no login or anything, its setup like navigating around out desktop hard drive etc.

Basically what I am trying to achieve is to simply grab a HB file from this ftp location and save/copy it to a desktop location based on VBA code like this:

job = InputBox("Enter job number")
FileCopy "[URL]ftp://xx.xxx.112.220/HB/HB[/URL]-" & job & ".pdf", "C:\Users\tbaker\Documents\test\HB-" & job & ".pdf"

This code doesn't work as it uses the normal FileCopy method, but you get the idea on what I am trying to achieve.

Is there anyway to do this via VBA without using the Microsoft Internet Transfer Control library?

Perhaps a route would be to open up Internet Explorer, put in an address (which would be something like: ftp://xx.xxx.112.220/HB/HB-11594004.pdf, which I would create as a variable), this then loads the pdf in internet explorer (i have tested manually), then you can go File -> Save As -> and this enables you to save the pdf to desktop (I have tried this manually so I'm hoping there is a way to do this all automatically via VBA).

Thanks

 

[Stildawn]

Sorry dont worry solved it with this code:

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
  
Sub DownloadFilefromWeb()
    job = InputBox("Enter Job Number")
    Dim strSavePath As String
    Dim URL As String
    Dim ret As Long
    URL = "[URL]ftp://xx.xxx.112.220/HB/HB[/URL]-" & job & ".pdf"
    strSavePath = "C:\Users\tbaker\Documents\test\HB-" & job & ".pdf"
    ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)
    If ret = 0 Then
        MsgBox "Download has been succeed!"
    Else
        MsgBox "Error"
    End If
End Sub

Thanks anyway.