Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3572
  • Last Modified:

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
0
mikecvba
Asked:
mikecvba
  • 4
  • 2
  • 2
1 Solution
 
bonjour-autCommented:
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
 
bonjour-autCommented:
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
 
mikecvbaAuthor Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
aikimarkCommented:
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
 
aikimarkCommented:
How do you use this XML data?
0
 
bonjour-autCommented:
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
 
mikecvbaAuthor Commented:
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
 
bonjour-autCommented:
yes, vba does not write to the C:/root
but you can write to your document folders on C:/user/......
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now