Solved

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

Posted on 2014-11-24
9
2,733 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

813 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

9 Experts available now in Live!

Get 1:1 Help Now