Solved

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

Posted on 2014-11-24
9
2,578 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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