VBA_ How to download .xls file (which is created from javascript) from website

[junior_88]

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]

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]

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]

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]

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]

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

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]

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

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.