Download file from Excel

[mordock]

hi, I would like seek some help on how to download html / pdf files using excel. It could be formula or a macro.

Response is highly appreciated.

 

[Haluk]

Try this;

Sub Test()
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    
    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0
    
    MyFile = "http://www.bigfoto.com/sites/main/tree-winter-xxx.JPG"
    
    WHTTP.Open "GET", MyFile, False
    WHTTP.Send
    FileData = WHTTP.ResponseBody
    Set WHTTP = Nothing
    
    If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"
    
    FileNum = FreeFile
    Open "C:\MyDownloads\tree-winter-xxx.JPG" For Binary Access Write As #FileNum
        Put #FileNum, 1, FileData
    Close #FileNum
    
    MsgBox "Open the folder [ C:\MyDownloads ] for the downloaded file..."
End Sub

 

[mordock]

Hi Haluk, it works! Thank you. But can you modify the code so it would donwload all files listed in cells, for example, from A1:10?

 

[Haluk]

Try this;

Sub Test2()
    Dim i As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    
    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0
    
    If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"
    
    For i = 1 To 10
        MyFile = Cells(i, 1).Text
        TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
        WHTTP.Open "GET", MyFile, False
        WHTTP.Send
        FileData = WHTTP.ResponseBody
        
        FileNum = FreeFile
        Open "C:\MyDownloads\" & TempFile For Binary Access Write As #FileNum
            Put #FileNum, 1, FileData
        Close #FileNum
    Next
    Set WHTTP = Nothing
    MsgBox "Open the folder [ C:\MyDownloads ] for the downloaded file..."
End Sub

 

[mordock]

The code works fine for me. Could it be possible to create a log file either in .txt or .xls formats for all downloaded files and to those unable to download?

Thanks in advance!

 

[Haluk]

So, this is the revised code.

After running the code, you'll find a log file located in C:\MyDownloads where the status for each file is shown.

Sub Test3()
    Dim i As Long
    Dim FileNum As Long
    Dim FileData() As Byte
    Dim MyFile As String
    Dim WHTTP As Object
    
    On Error Resume Next
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0
    
    If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"
    
    For i = 1 To 10
        MyFile = Cells(i, 1).Text
        If CheckURL(MyFile) Then
            FileNum = FreeFile
            Open "C:\MyDownloads\LogFile.txt" For Append As #FileNum
            Print #FileNum, MyFile & " --- Dowmloaded ----"
            Close #FileNum
            TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
            WHTTP.Open "GET", MyFile, False
            WHTTP.Send
            FileData = WHTTP.ResponseBody
            FileNum = FreeFile
            Open "C:\MyDownloads\" & TempFile For Binary Access Write As #FileNum
                Put #FileNum, 1, FileData
            Close #FileNum
        Else
            FileNum = FreeFile
            Open "C:\MyDownloads\LogFile.txt" For Append As #FileNum
            Print #FileNum, MyFile & " !!! File Not Found !!!"
            Close #FileNum
        End If
    Next
    Set WHTTP = Nothing
    MsgBox "Open the folder [ C:\MyDownloads ] for the downloaded files..."
End Sub
'
Function CheckURL(URL) As Boolean
    '
    ' Haluk ® - 11/11/08
    '
    '
    Dim W As Object
    On Error Resume Next
        Set W = CreateObject("winhttp.winhttprequest.5")
        If Err.Number <> 0 Then
            Set W = CreateObject("winhttp.winhttprequest.5.1")
        End If
    On Error GoTo 0
    
    On Error Resume Next
    W.Open "HEAD", URL, False
    W.Send
    If W.Status = 200 Then
        CheckURL = True
    Else
        CheckURL = False
    End If
End Function

 

[fmkjr]

Hi!

I am attempting to use the code in test3 but I am getting an error for the variable TempFile. I tried adding a variable but I am not able to find the correct variable type to assign to this item. Any ideas why this will not work?

thanks!
frank

 

[mordock]

hi fmkjr, it would be better to provide the error prompted in using the macro since it works fine for me.

Btw, try to change the path of the downloaded file. See if this will work.

 

[ingedwin]

Hi hulk,

I tried to use your program and try to asign th MYFile to "javascript: __doPostBack('ctl00$ContentPlaceHolder1$radgDL$ctl00$ctl42$ctl00','')" but it give ma an run-time error: "The URL does not use a recognized protocol"

Any adivice will really appriciate.

 

[bapcki1]

THANK YOU SOOOOO MUCH FOR POSTING THIS CODE!

- It work fantastically! The only thing that does not quite work for me is that it corrupts .zip files upon downloading.

Would it be possible to help me understand why this is?

I found this article but my VBA skills are not good enough yet.. Excel VBA: Download files from the Internet | LazerWire.com


THANK YOU FOR ALL YOUR HELP SO FAR!!! :D


Paddy