Download file from Excel

mordock

New Member
Joined
Nov 13, 2008
Messages
11
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this;

Code:
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
 
Upvote 0
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?
 
Upvote 0
Try this;

Code:
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
 
Upvote 0
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!
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top