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.

 

[varsakov]

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

Hi, Haluk, thank you for this post! It seems the code works, but I am wondering if there is a way to get it to authenticate. I am trying to download a pdf from the following hyperlink: http://apps.trepp.com/abpw/w/do_download.cgi/latest/remit/abac6ns2/abac6ns2.pdf, but need to provide username and password to authenticate. Any thoughts on how to achieve this? Thank you for your response in advance.

-Vasilis

 

[goldeempate1]

I am trying to do the same thing as the original poster, but I wrote in an extra bit of code to make it so that I can use it for a variable number of rows within the spreadsheet. The code below ran without any error messages, and the intermediate window seems to indicate it got the file from the Internet, but there is no file in the specified folder. Any suggestions as to why that is would be greatly appreciated

Sub imagestore()

Dim i As Long
Dim lastRow As Long
Dim beginningRow As Long
Dim FileNum As Long
Dim columnNum As Long
Dim FileData() As Byte
Dim filesLocation As String
Dim MyFile As String
Dim message As String
Dim WHTTP As Object

beginningRow = 55
lastRow = WorksheetFunction.CountA(Columns(17)) + beginningRow - 2

filesLocation = "C:\Users\<username>\Desktop\<foldername>" 'Specifies where to put file
columnNum = 17 'Specifies column number to pull from

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(filesLocation, vbDirectory) = Empty Then MkDir filesLocation

For i = beginningRow To lastRow 'Sets range from row variables
MyFile = Cells(i, columnNum).Text
TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody

FileNum = FreeFile
Open filesLocation & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
Next
Set WHTTP = Nothing


message = "Files downloaded to " & filesLocation

MsgBox message 'Alerts user to location of download

End Sub

 

[goldeempate1]

I am trying to do the same thing as the original poster, but I wrote in an extra bit of code to make it so that I can use it for a variable number of rows within the spreadsheet. The code below ran without any error messages, and the intermediate window seems to indicate it got the file from the Internet, but there is no file in the specified folder. Any suggestions as to why that is would be greatly appreciated

Sub imagestore()

Dim i As Long
Dim lastRow As Long
Dim beginningRow As Long
Dim FileNum As Long
Dim columnNum As Long
Dim FileData() As Byte
Dim filesLocation As String
Dim MyFile As String
Dim message As String
Dim WHTTP As Object

beginningRow = 55
lastRow = WorksheetFunction.CountA(Columns(17)) + beginningRow - 2

filesLocation = "C:\Users\<username>\</username>Desktop\<foldername></foldername>" 'Specifies where to put file
columnNum = 17 'Specifies column number to pull from

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(filesLocation, vbDirectory) = Empty Then MkDir filesLocation

For i = beginningRow To lastRow 'Sets range from row variables
MyFile = Cells(i, columnNum).Text
TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody

FileNum = FreeFile
Open filesLocation & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
Next
Set WHTTP = Nothing


message = "Files downloaded to " & filesLocation

MsgBox message 'Alerts user to location of download

End Sub

My apologies for the error in my post above. I did put a proper path to the folder on the desktop I wanted it to go to so that shouldn't be the problem?

 

[pbcnick]

thanks for this code Haluk, I needed it for work to download a bunch of files. I had worked out to download files from a ftp site but I had a few http links I needed to get files for also. this did the trick straight out the gate. thanks!

 

[paulobitt]

Amazing code, it's working!!! But I need some help guys... I want to save the file with a different name, as an adjacent cell name. Can you help me please with that? :D

I am trying to do the same thing as the original poster, but I wrote in an extra bit of code to make it so that I can use it for a variable number of rows within the spreadsheet. The code below ran without any error messages, and the intermediate window seems to indicate it got the file from the Internet, but there is no file in the specified folder. Any suggestions as to why that is would be greatly appreciated

Sub imagestore()

Dim i As Long
Dim lastRow As Long
Dim beginningRow As Long
Dim FileNum As Long
Dim columnNum As Long
Dim FileData() As Byte
Dim filesLocation As String
Dim MyFile As String
Dim message As String
Dim WHTTP As Object

beginningRow = 55
lastRow = WorksheetFunction.CountA(Columns(17)) + beginningRow - 2

filesLocation = "C:\Users\<username>\</username>Desktop\<foldername></foldername>" 'Specifies where to put file
columnNum = 17 'Specifies column number to pull from

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(filesLocation, vbDirectory) = Empty Then MkDir filesLocation

For i = beginningRow To lastRow 'Sets range from row variables
MyFile = Cells(i, columnNum).Text
TempFile = Right(MyFile, InStr(1, StrReverse(MyFile), "/") - 1)
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody

FileNum = FreeFile
Open filesLocation & TempFile For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
Next
Set WHTTP = Nothing


message = "Files downloaded to " & filesLocation

MsgBox message 'Alerts user to location of download

End Sub

 

[arthurburg]

hi is it also possible to tweak this macro so that it works for links pointing to files stored on a server?
i have a shared path on a server where documents are stored which i like to download. This macro works fine for documents with http links but how can i tweak it so that it works for links like this: \\files\32\testdocument.docx

thanks for your help

 

[Nossi]

hi is it also possible to tweak this macro so that it works for links pointing to files stored on a server?
i have a shared path on a server where documents are stored which i like to download. This macro works fine for documents with http links but how can i tweak it so that it works for links like this: \\files\32\testdocument.docx

thanks for your help

I have a similar problem.

I've got a link like that: https://site.com/link.html and there I get a CSV file.

Is it possible to save the file under a new name (like file.csv)?

 

[Nossi]

I have a similar problem.

I've got a link like that: https://site.com/link.html and there I get a CSV file.

Is it possible to save the file under a new name (like file.csv)?

I've got a solution

Public Function funcFileDownload(strURL As String, strPath As String, strFile As String, Optional strUserName As String = "", Optional strPassWord As String = "") 
'---------------------------------------------------------
'** Variablen deklarieren
Dim objWinHttpReq As Object
Dim objStream As Object
Dim wksBlatt As Object
Dim i As Integer
 
'---------------------------------------------------------
'** Quellcode Funktion
'** HTTP Verbindung aufbauen,
    Set objWinHttpReq = CreateObject("Microsoft.XMLHTTP")
    objWinHttpReq.Open "GET", strURL, False, strUserName, strPassWord
    objWinHttpReq.send


    If objWinHttpReq.Status = 200 Then
        Set objStream = CreateObject("ADODB.Stream")
        With objStream
            .Open
            .Type = 1
            .Write objWinHttpReq.responseBody
            .SaveToFile strPath & strFile, 2 ' 1 = no overwrite, 2 = overwrite
            .Close
        End With
    End If

It does work without any problems (at least until now)