Solved

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

Posted on 2014-11-24
9
2,667 Views
Last Modified: 2014-11-25
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
0
Comment
Question by:mikecvba
  • 4
  • 2
  • 2
9 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40464046
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

0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40464063
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

0
 

Author Comment

by:mikecvba
ID: 40464200
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40464254
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 45

Expert Comment

by:aikimark
ID: 40464256
How do you use this XML data?
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 500 total points
ID: 40464326
here is a extended code example
Public Function post_html()

Dim XMLHTTP
Dim result As String
Dim argumentString
Dim fso As New Scripting.FileSystemObject
Dim fl As Scripting.File
Dim ts As Scripting.TextStream

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
'Debug.Print result
result = Replace(result, "encoding=" & Chr(34) & "UTF-8", "encoding=" & Chr(34) & "iso-8859-1")
fso.CreateTextFile "d:\test.xml"
Set fl = fso.GetFile("d:\test.xml")
Set ts = fl.OpenAsTextStream(ForWriting)
ts.Write result
ts.Close
Set ts = Nothing
Set fl = Nothing
Set fso = Nothing
Set XMLHTTP = Nothing
post_html = "OK"
End Function

Open in new window


you ned to add the 'Microsoft Scriting Runtime' reference
the reason, why we need to exchange the header is, that you have "Umlaute" in the xml-data
with an UTF-8 header this is no well formed XML (so excel cannot open)
0
 

Author Closing Comment

by:mikecvba
ID: 40464362
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.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40464374
yes, vba does not write to the C:/root
but you can write to your document folders on C:/user/......
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now