CODEWITHSUNDEEP
excelvbainternet-explorer-9
Federico Sanchez
Federico Sanchez

Reputation: 145

download excel file from web using vba

i have the following code to download an specific file from the web. the webpage has the name of the file and if you click on it you get the "Do you want to open or save ....... from ......?Open/Save/Cancel. this is what i have so far:

    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    Set ie = New InternetExplorer
    ie.Navigate "http://customerservice.ciena.com/BidRegister/Queue.aspx?REGIONID=3"
Set fe1 = html.getElementById("ctl00_P1_rdgFileList_ctl00_ctl00_btnGetFile")
fe1.click

at this point i get the pop up message i was telling you about above. How can i continue my code to either avoid this pop up and directly save the file in a given location? is there a line of code that saves the file directly?

Upvotes: 0

Views: 3688

Answers (1)

jamheadart
jamheadart

Reputation: 5293

From what I recall by using the Internet Explorer object you can't bypass its safety prompts.

I get round this at work by using post/get requests via a serverhttp object, getting the file as a responsebody and then use ADODB object to save the body directly to predetermined location.

Here is an example for a GET request which should download a file directly from the URL:

set objhttp = new MSXML2.ServerXMLHTTP60
URL = "http://www.princexml.com/samples/catalog/PrinceCatalogue.pdf"
objhttp.Open "GET", URL, False
objhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objhttp.send
If objhttp.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write objhttp.responseBody
    oStream.SaveToFile "filepath and filename.pdf", 2
    oStream.Close
End If

To make this work for a POST request you need a bit more info. To try and find the info you will need to use the F12 Developer tool on internet explorer:

  • Have InternetExplorer open on the page you're downloading from
  • press F12 to open the developer tool
  • Click on "Network"
  • Click on the "play" button ("Enable network traffic capturing")
  • now download the file and you should see the POST that requested the file pop up in the Network summaries.
  • Double click on that POST line in the F12 tool and click on "Request body", whatever is in there is the goldmine, the thing that was sent to the URL to request the file. If you can interpret that and deploy it in your code then you've cracked it.

the last thing to do would be to change two lines in the code above:

 objhttp.Open "POST", URL, False
 objhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
 objhttp.send (This is where you put the data string i.e. the REQUEST BODY)

Sorry I can't specify any more, there are tonnes of formats that a POST request body can have so without seeing an example of your system's I can't go any further.

Upvotes: 2

Related Questions