Search code examples
excelvbadownloading-website-files

Problems with downloading a file with EXCEL vba


I'm writing a android app and I need a fairly big databas for it. I'm using Excel and vba to construct this databas. I've been googling quite a bit and in order to download a webpage (to extract data to my databas) and I've come up with the below code. But it's not working. It always returns downloadResult=2148270085. Anyone with any good suggestions for a solution? I'm on a 64-bit system and using EXCEL2013 64-bit version.

Option Explicit
Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
        ByVal pcaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr) As LongPtr

Sub DownloadFileButton_Clicked()
    Dim fileURL As String
    Dim fileName As String
    Dim downloadResult As LongPtr
    fileURL = "http://www.wordreference.com/definicion/estar"
    fileName = Application.ThisWorkbook.Path + "\" + "estar.htm"
    downloadResult = URLDownloadToFile(0, fileURL, fileName, 0, 0)
    If downloadResult = 0 then
        Debug.Print "Download started"
    Else
        Debug.Print "Download not started, error code: " & downloadResult
    End If
End Sub

Solution

  • Ok, I ended up with httpRequest instead of URLDownloadFile, but couldn't make it work either. Until a few hours of testing I finally discovered it was my firewall blocking the request. After trying to add an exception to my firewall I just ended up turning the firewall off when using my code. Hope this helps someone. I'm prettty sure URLDownloadFile would have got stuck in the firewall as well.

    Sub DownloadFileButton_Clicked3(language As String, verb As String) ' Fr Es It
        Const WinHttpRequestOption_EnableRedirects = 6
        Dim httpRequest As Object
        Dim URL As String, myString As String
        Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
        URL = "http://www.wordreference.com/conj/" + language + "Verbs.aspx?v=" + verb
        httpRequest.Option(WinHttpRequestOption_EnableRedirects) = True
        httpRequest.Open "GET", URL, False
        'httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        'httpRequest.SetTimeouts  'connection with the server could not be established
        httpRequest.Send
        httpRequest.WaitForResponse
        Debug.Print Len(httpRequest.ResponseText)
        myString = httpRequest.ResponseText
        Dim fileName As String
        fileName = Application.ThisWorkbook.Path + "\" + verb + ".htm"
        Open fileName For Output As #1
        Print #1, myString
        Close #1
        Set httpRequest = Nothing
    End Sub