none
Download .pdf file limit? (Microsoft.XMLHTTP) RRS feed

  • Question

  • In Excel 2013, I'm using the VBA code below in a loop to download a few hundred .pdf files a day from a website. It's been working fine for years then starting on Friday (9/21/18), the code would only download 101 files and then stop and give the "The download of the specified resource has failed" error message. I close Excel and start the process again where I left off and it does another 101 files and stops. Do you have any idea what is causing this and why It just started happening? Thanks for the help!


    Dim myURL As String
     myURL = "Put your download link here"

     Dim HttpReq As Object
     Set HttpReq = CreateObject("Microsoft.XMLHTTP")
     HttpReq.Open "GET", myURL, False, "username", "password"
     HttpReq.send

     myURL = HttpReq.responseBody
     If HttpReq.Status = 200 Then
         Set oStrm = CreateObject("ADODB.Stream")
         oStrm.Open
         oStrm.Type = 1
         oStrm.Write HttpReq.responseBody
         oStrm.SaveToFile ThisWorkbook.Path & "\" & "file.csv", 2 ' 1 = no overwrite, 2 = overwrite
         oStrm.Close
     End If

    Wednesday, September 26, 2018 3:28 PM

All replies

  • Hello, 

    Your code didn't contain visible errors.

    However, You didn't handle all response codes - you picking up only "200 Ok". Other codes can tell you what is wrong on the server.

    Most likely on the server you use to download PDF's was spotted your huge traffic and they decide to provide 101 document by PC. 

    Take another IP/username/password for your PC and see what happend.


    Sincerely, Highly skilled coding monkey.

    Wednesday, September 26, 2018 4:02 PM
  • Hi jnewbie2,

    According to your description, you are using "Microsoft.XMLHTTP" and your code has been working fine for years.

    First, i assume that it is caching that causes the problem.

    So you can try the code below that should prevent caching:

    With CreateObject("Microsoft.XMLHTTP")
        .Open "GET", myURL, False, "username", "password"
        .setRequestHeader "If-Modified-Since", "Tue, 1 Jan 1980 00:00:00 GMT"
        .setRequestHeader "Cache-Control", "no-cache"
        .setRequestHeader "Pragma", "no-cache"
        .send
    End With

    Second, as you said it gives the "The download of the specified resource has failed" error message.

    You could try to remove "Dim HttpReq As Object" to see if the solution in the link above will work for you.

    If the problem persists, you can compare your code to the code in the following link for troubleshooting:

    XmlHttpRequest – Http requests in Excel VBA

    Hopefully it helps you. Please feel free to ask any questions.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, September 27, 2018 8:55 AM
    Moderator
  • Hi jnewbie2,

    Thanks for your asking. Did you resolve your issue ? If the post helps you, you can mark it as answer and close the post.

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

    I wish you a happy life!

    Best Regards,

    Yuki

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 1, 2018 2:04 AM
    Moderator
  • Thanks for the help! Unfortunately, the "prevent caching" code didn't help. I tried altering my code to resemble the code in the "XmlHttpRequest – Http requests in Excel VBA" link you provided but I am still getting the "The download of the specified resource has failed". The issue is duplicated on a co-workers PC as well. Also, I'm working from home today (VPN) and the issue is the same but now it only let's me download 1 or 2 files then errors out. Is it possible that it's some setting or something in Excel/Office that causing this? It's just funny it started all of a sudden after years of working fine.   
    Monday, October 1, 2018 2:20 PM
  • > The issue is duplicated on a co-workers PC as well.

    So, it's a server side issue.


    Sincerely, Highly skilled coding monkey.

    Monday, October 1, 2018 2:25 PM