asked on

VBA Excel downloading file from IE11 - Open/Save/Cancel Dialogue - save as file name

I am trying to download some data on carbon emissions. I can preload the page with the relevant settings via the URL.
It loads fine and I can click the OK button by its ID then I get the IE11 - Open/Save/Cancel Dialogue at the bottom. I have tried all suggestions using FindWindows (#32770) and also Send Keys which is very unreliable. Can someone suggest the code to manipulate this dialogue box or else perhaps examine the HTML on the web page to see if a direct download would be possible?

Dim htm As Object
Dim IE As Object

    Dim Doc As Object
    Set IE = CreateObject("internetexplorer.application")
    IE.Visible = True

    IE.navigate "http://ec.europa.eu/environment/ets/exportEntry.do?form=transaction&destinationAccountHolder=&startDate=01%2F06%2F2009&destinationRegistry=-1&originatingAccountType=121&endDate=01%2F05%2F2010&transactionID=&suppTransactionType=-1&originatingAccountHolder=&transactionType=-1&languageCode=en&destinationAccountNumber=&destinationAccountType=121&toCompletionDate=&originatingRegistry=-1&destinationAccountIdentifier=&fromCompletionDate=&originatingAccountIdentifier=&transactionStatus=4&originatingAccountNumber=1973&currentSortSettings="

    Do While IE.readystate <> 4: DoEvents: Loop

    Set Doc = CreateObject("htmlfile")
    Set Doc = IE.document

        Doc.getelementbyID("btnOK").Click[embed=file 884739]
        
    'I need code here which clicks the save as button as save the file as C:\temp.xml
        
    
    Set IE = Nothing

Open in new window

Capture.JPG

bonjour-aut

I have no solution for getting att the IE button, but alternatively you culd do a direct request to the server.

For testing, just check this HTML, If this gives the correct download, you just can write some code doing the corresponding post request to the server and store teh received data to a file.

test html:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=windows-1250">
  <title></title>
  </head>
  <body>
  <form action="http://ec.europa.eu/environment/ets/export.do" method="POST" name="export">
  <p>
  <input  value="destinationAccountHolder=&startDate=01%2F06%2F2009&destinationRegistry=-1&originatingAccountType=121&form=transaction&endDate=01%2F05%2F2010&transactionID=&originatingAccountHolder=&suppTransactionType=-1&transactionType=-1&languageCode=en&destinationAccountNumber=&destinationAccountType=121&toCompletionDate=&originatingRegistry=-1&destinationAccountIdentifier=&fromCompletionDate=&originatingAccountIdentifier=&transactionStatus=4&originatingAccountNumber=1973&currentSortSettings=" name="exportURL"  size="600">
  </p>
  <p>
  <input  value="transaction" name="form">
  </p>
  <input id="fldExportType" class="formRadio" type="radio" checked="checked" value="1" name="exportType">
  <input id="btnOK" class="FormButton" type="submit" value="Ok" name="OK">
  <input class="FormButton" type="submit" value="Cancel" name="cancel">
  </body>
</html>

Open in new window

bonjour-aut

The corresponding test VBA code would be:

Public Function post_html()

Dim XMLHTTP
Dim result As String
Dim argumentString
argumentString = "?export_url=destinationAccountHolder=&startDate=01%2F06%2F2009&destinationRegistry=-1&originatingAccountType=121&form=transaction&endDate=01%2F05%2F2010&transactionID=&originatingAccountHolder=&suppTransactionType=-1&transactionType=-1&languageCode=en&destinationAccountNumber=&destinationAccountType=121&toCompletionDate=&originatingRegistry=-1&destinationAccountIdentifier=&fromCompletionDate=&originatingAccountIdentifier=&transactionStatus=4&originatingAccountNumber=1973&currentSortSettings=&form=transaction&exportType=1&OK=OK"
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
XMLHTTP.Open "POST", _
    "http://ec.europa.eu/environment/ets/export.do", False
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
XMLHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
XMLHTTP.send argumentString
result = XMLHTTP.responsetext 'just display
Debug.Print result
Set XMLHTTP = Nothing

End Function

Open in new window

mikecvba

ASKER

bonjour-aut - Thanks so much we are nearly there. Brilliant job. I can see that the output in the Immediate window is exactly the expected data from the website. But if I cut and paste it into excel manually or in code it responds with incorrect XML format. Is there something in the argument e.g.

XMLHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
 XMLHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
 XMLHTTP.send argumentString
result = XMLHTTP.responsetext

Open in new window

That alters the format away from acceptable XML to Excel? Maybe its missing a header?
Can you improve the Function to save the 'result' as an valid xml file e.g. C:\test.xml please.

aikimark

You are only seeing part of the XML file in the immediate window. That is why it is not well-formed XML.

You can use a filesystemobject textstream to write the contents to a file.

aikimark

How do you use this XML data?

ASKER CERTIFIED SOLUTION

bonjour-aut

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mikecvba

ASKER

Excellent Result. I added the Microsoft Scripting in References and it worked a dream. Sure enough, I have the data file saved as "d:\test.xml". Just an interesting point - why do it give permission denied when I change the c:\test.xml? Is VBA forbidden from writing to the root directory? Thanks so much.

bonjour-aut

yes, vba does not write to the C:/root
but you can write to your document folders on C:/user/......