CODEWITHSUNDEEP
excelvba
james daley
james daley

Reputation: 55

vba download excel workbook from url and save to c drive?

I am trying to download an excel workbook and save this to the users c drive on there local machine. For some reason I get an error operation is not allowed in this context on the following line:

oStream.Write WinHttpReq.ResponseBody

Can someone please show me where I am going wrong?

thanks

Dim myURL As String
myURL = "http://eu.storagemadeeasy.com/files/357eb15ab28bade4da58c149506d00d8.xls"

    Dim WinHttpReq As Object
    Set WinHttpReq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    WinHttpReq.Open "GET", myURL, False
    WinHttpReq.Send

    myURL = WinHttpReq.ResponseBody
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 2
        oStream.Write WinHttpReq.ResponseBody
        oStream.SaveToFile ("C:\logs.xls")
        oStream.Close
    End If

Upvotes: 1

Views: 1556

Answers (2)

Michael
Michael

Reputation: 1

I use Excel 2010 and Windows 7.

This kind of code works for me sometimes. But not every time. I get HttpReq.Status = 404 moore often than HttpReq.Status = 200.

Dim HttpReq As Object

Set HttpReq = CreateObject("Microsoft.XMLHTTP")

HttpReq.Open "GET", myURL, False

Application.Wait Now + TimeValue("0:00:10")

**HttpReq.send**

Application.Wait Now + TimeValue("0:00:05")

myURL = HttpReq.responseBody

**If HttpReq.Status = 200 Then**
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile "C:\Users\MiLarsso\Downloads" & "\" & "Narvarotid1.xls", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close
End If

I actually believe the file is there, because it shows up in the webbrowser. Might it be that it times out fast if it do not find it in like 100 ms. I can imagine that the connection can be a bit slow.

Upvotes: 0

Alex K.
Alex K.

Reputation: 175936

  • Use a binary stream

  • Modern versions of windows disallow writes to the root of C:, use a different path.

    oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile Environ$("TEMP") & "\logs.xls" '// users temp dir

Upvotes: 1

Related Questions