View Full Version : VBA_ How to download .xls file (which is created from javascript) from website
junior_88
11-29-2014, 04:45 AM
Hi All,
I have a problem how to create macro code which will be download .xls file (created by javascript). I want to use download file and use involved data to calculate.
Here is the website. Click on the product and then appears a site where on the top is Excel icon "Export"
Now I write the VBA code where List of products is scraping to ListBox. I want to when I select product in ListBox then it will download data from Export file .xls
Here is code:
Private Sub CommandButton1_Click()
Dim pl As String
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
url_name = "ec.europa.eu/sanco_pesticides/public/?event=commodity.selection"
IE.navigate (url_name)
Do
DoEvents
Loop Until IE.readyState = 4
' <a href
Set chooseLanguage = IE.Document.getElementById("L_langue")
chooseLanguage.selectedindex = 17
chooseLanguage.FireEvent ("onchange")
Arkusz1.ListBox1.Clear
'chooseLanguage = chooseLanguage.innertext
MsgBox "Czekaj..ładuj listę produktów"
'products
Set Products = IE.Document.getElementById("listec")
Arkusz1.ListBox1.Clear
'Products = Products.innertext
'MsgBox Products
'ComboBox1 = Products.Text
For Each Product In Products
ListBox1.AddItem Product.innertext
Do
Loop Until Not (IE.Busy)
Next
MsgBox "Done"
End Sub
westconn1
11-29-2014, 01:29 PM
this appears to work
For Each ele In IE.Document.getelementsbytagname("a")
If InStr(ele.href, "'commodity.export_XLS'") Then ele.Click: Exit For
Next
Do Until IE.ReadyState = 4: DoEvents: Loop
IE.Document.SaveAs "c:\temp\pest.xls"
though, of course, a hard coded file name will always overwrite the previous, you could use the listbox item as the filename
as this code is to be in the listbox click event, you would need to dimension ie globally so that it is available to all procedures, else you would end up creating a new instance of ie each time you click a listbox item
junior_88
11-29-2014, 04:35 PM
Thank You westconn1,
It looks promising, but now I have a problem to implement the result site with define product. For that is responsible next <Form
<form action="?event=commodity.export_XLS" name="charge" method="post"><script language="javascript" type="text/javascript">
function page(n){document.charge.start.value = n; ch_action(2,'commodity.resultat');}
</script>
<input type="hidden" name="ch_sub" value="">
<input type="hidden" name="listec" value="5">
<input type="hidden" name="valeur" value="">
<input type="hidden" name="nom_ecran" value="">
<input type="hidden" name="l_langue" value="PL">
<input type="hidden" name="start" value="">
</form>
I try by code:
Private Sub CommandButton1_Click()
Dim AllHyperLink As HTMLInputElement
Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
url_name = "ec.europa.eu/sanco_pesticides/public/?event=commodity.resultat"
IE.navigate (url_name)
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
<a href
Set AllHyperLink = IE.Document.getelementsbytagname("listec")
AllHyperLink.Value = "5"
MsgBox "Done"
End Sub
but... it does not work.
Idea why ?
westconn1
11-29-2014, 07:14 PM
IE.Document.getelementsbytagname("listec")listec is an element name, not a tagname, the tagname is input
try like
set mylist = IE.Document.all("listec")
junior_88
11-30-2014, 10:35 AM
listec is an element name, not a tagname, the tagname is input
try like
set mylist = IE.Document.all("listec")
Set mylist = IE.Document.all("listec")
is ok but when I add
mylist.Value = "5"
then error windows is appears
"Runtime error 91 Object variable or with block variable not set". The same error appears when I add for e.g
msgbox mylist
...there is lack of some declaration of "mylist" , right ?
westconn1
11-30-2014, 01:27 PM
.there is lack of some declaration of "mylist" , right ?while it would be much better to dimension the variable, it would not cause this error
try like
mylist.selectedindex = 5
though the msgbox should have worked without error, showing [object]
so possibly the object is not being assigned to variabe
hold mouse pointer over variable, should show [object] or nothing
worked correctly for me on testing
junior_88
12-01-2014, 03:14 AM
First,
In "In the VBA Editor, go to the Tools menu, choose Options and then select the Editor tab, shown below. There, check the Require Variable Declaration option." and then try to Compile program with next code:
Private Sub CommandButton1_Click()
Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
url_name = "ec.europa.eu/sanco_pesticides/public/?event=commodity.resultat"
IE.navigate (url_name)
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
' <a href
Set mylist = IE.Document.all("listec")
mylist.selectedindex = 5
MsgBox mylist
MsgBox "Done"
End Sub
The result: The same error - 91
What I am doing wrong ? Nevertheless it works for you...
Finally I have solved problem by other way. Despite this I hope you help me to understand new things / tips & tricks :)
How I did it ?
I used previous web (when You are choose the product) (...)?event=commodity.selection , select product and click on "Search" button.
Then in is forwarded to "Results site" (...)?event=commodity.resultat when I used to yours part of code:
Private Sub CommandButton1_Click()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
url_name = "ec.europa.eu/sanco_pesticides/public/?event=commodity.selection"
IE.navigate (url_name)
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
' <a href
Set mylist = IE.Document.getElementById("listec")
mylist.selectedindex = 5
With IE.Document
Set but = .getelementsbytagname("input")
For Each e In but
If (e.getattribute("className") = "ButtonSmall") Then
e.Click
Exit For
End If
Next e
End With
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
For Each ele In IE.Document.getelementsbytagname("a")
If InStr(ele.href, "'commodity.export_XLS'") Then ele.Click: Exit For
Next
Do Until IE.ReadyState = 4: DoEvents: Loop
IE.Document.SaveAs "c:\temp\pest.xls"
IE = Nothing
MsgBox "Done"
End Sub
You part of code about download created .xls file is work but my IE appears the windows "Downloading file" and ask me I want to "Open" or "Save". Is it possible to learn IE that in this case file must be saved at c:\temp\pest.xls ?
Finally everything is works but at te end VBA shows 438 error (Object does not support this property or method)
westconn1
12-01-2014, 03:41 AM
(Object does not support this property or method)probably you should have
ie.quit
set ie = nothing
Is it possible to learn IE that in this case file must be saved at c:\temp\pest.xls ?this would be complex coding, requiring to create an additional instance of excel to find the download dialog and click the button
i did not get this dialog when i ran the test code
The result: The same errori do not see any real difference in that part of the code in the new version of your code, so i am not sure what actually solved the problem
junior_88
12-01-2014, 05:45 AM
probably you should have
ie.quit
set ie = nothing
It is not helps.
i do not see any real difference in that part of the code in the new version of your code, so i am not sure what actually solved the problem
My problem was to dwonload the .xls file.
I try to solve my problem by code that you propose
Private Sub CommandButton1_Click()
Dim IE As SHDocVw.InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
url_name = "ec.europa.eu/sanco_pesticides/public/?event=commodity.resultat"
IE.navigate (url_name)
Do While IE.ReadyState <> 4 Or IE.Busy = True
DoEvents
Loop
' <a href
Set mylist = IE.Document.all("listec")
mylist.selectedindex = 5
MsgBox mylist
MsgBox "Done"
End Sub
...when I (with yours help) start process by site ec.europa.eu/sanco_pesticides/public/?event=commodity.resultat
Unfortenatelly I can not understand how excatly you do that
Set mylist = IE.Document.all("listec")
mylist.selectedindex = 5
is working for you, so I have written another code where I start IE.navigate from ec.europa.eu/sanco_pesticides/public/?event=commodity.selection site.
Could you help to understand how to open ec.europa.eu/sanco_pesticides/public/?event=commodity.resultat and set up next values:
<form action="?event=commodity.export_XLS" name="charge" method="post"><script language="javascript" type="text/javascript">
function page(n){document.charge.start.value = n; ch_action(2,'commodity.resultat');}
</script>
<input type="hidden" name="ch_sub" value="">
<input type="hidden" name="listec" value="5">
<input type="hidden" name="valeur" value="">
<input type="hidden" name="nom_ecran" value="">
<input type="hidden" name="l_langue" value="PL">
<input type="hidden" name="start" value="">
</form>
Both code's are giving the same results but I wish to understand both case.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.