Cannot import data from Web into Excel 1

[cctanaka]

Hello,

I cannot import data from teh following site:

http://www.anp.gov.br/preco

then link "SEMANAL - RESUMO I"
Select option button ESTADOS, combo box GASOLINA
Submmit - Processar

The resultant table, starting with word "ESTADO" in light blue, has spanned cells (in the header), I think Web queries of Excel cannot import tables like that, is that right? If not how can I import.

Is there any other way to import the table data? I am looking for a solution in VBA.
I manage to go to the final site with the following code, but I do not know how to import.

Thanks in advance,
Carlos

CODE (first of all I checked References - Microsoft HTML Object Library and Microsoft Internet Controls).
Please put a breakpoint at line 47 to see the web page loaded:

Option Explicit

Sub ImportWithIE()
Dim IEApp As InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim URL As String
Dim Formulario As HTMLFormElement
Dim RadRegiao As Object
Dim RadINP As HTMLInputElement
Dim SelCombustivel As HTMLSelectElement
Dim BtnSubmit As HTMLButtonElement

URL = "

http://www.anp.gov.br/preco/"

Set IEApp = New InternetExplorer
IEApp.Visible = True
IEApp.Navigate URL
Do
DoEvents
Loop Until IEApp.ReadyState = 4

Set HTMLDoc = IEApp.Document

IEApp.Navigate HTMLDoc.Links(2)

Do
DoEvents
Loop Until IEApp.ReadyState = 4 And Not IEApp.Busy

Set Formulario = HTMLDoc.forms("frmAberto")
Set RadRegiao = Formulario.elements("RdResumo")
Set RadINP = RadRegiao.Item(2)
RadINP.Click

Set SelCombustivel = Formulario.elements("selcombustivel")
SelCombustivel.Value = "487*Gasolina"
'(Submmit)
Set BtnSubmit = Formulario.elements("image1")
BtnSubmit.Click

Do
DoEvents
Loop Until IEApp.ReadyState = 4 And Not IEApp.Busy


'NEED to import here
Sheets("Data").Select

Set HTMLDoc = IEApp.Document
Set Formulario = HTMLDoc.forms("frmAberto")
Set HTMLDoc = Formulario.Document

' ????????????

IEApp.Quit
Set IEApp = Nothing

End Sub


Carlos César tanaka
Curso de Excel / Access
MPR Informática

 

[Gruuuu]

I think you have to loop through the table elements and set the values in your Excel spreadsheet to the data inside the table.

You can check the types of the elements in the table by checking the .tagName property of each element

This might help you:

Dim element as IHTMLElement
Dim strOutput as String
...

...
For each element in HTMLDoc.all
   strOutput = strOutput & vbCrLf & element.tagName & ": " & element.innerText
Next element

Sheets("sheetname").Range("A1").Value = strOutput

This will show you the element breakdown and the innerText values of each element you're looking at.
Hopefully you can figure out how to pull out only the information you want throught he code from there.

 

[cctanaka]

Thanks Gruuuu,

I am trying to extract data from that table using HTML elements. I would like to know if it is possible to make a web query (using Excel / Data) to navigate through links of web page and options of forms, and then extract a table, like that I described in the previous post.
I am thinking of web queries with parameters. Does it work? How?

Carlos César tanaka
Curso de Excel / Access
MPR Informática

 

[Gruuuu]

No, a web query works only on a URL. That means it would only work if you can save a favorite to a website and get back to it without clicking on any links or interacting with the site in any way.

This one looks like a form is submitted by POST, instead of GET. Which means no URL for you.

Keep picking away at that table though! All the information is there, and if you set up your algorithm right, you can get everything you need.

 

[cctanaka]

Hi Gruuuu,

I have researched this subject and discovered that is possible to make a Excel Web Query to a site that has a form using POST method. It has to use a IQY file, separating URL from parameters. My problem is I am not being able to discover what are the parameters (names).

Could you help me to discover those parameters. I think I am near final line.
The sequence to get the page with data is:

-Site:

http://www.anp.gov.br/preco

-link "SEMANAL - RESUMO I
-Select option button ESTADOS
-Combo box GASOLINA
-Processar (it is the submmit button)

Thanks

Carlos César tanaka
Curso de Excel / Access
MPR Informática

 

[Gruuuu]

Well that's a handy bit of information about the post method! Have a star.

so the post method takes the 'name' elements of the field as the variable name, and the 'value' element as the variable's value

<form id=frmAberto name=frmAberto method=post action="javascript:Direciona();">
	<input type=hidden name=selSemana value='619*de 24/04/2011 a 30/04/2011'>
	<input type=hidden name=desc_Semana value='de 24/04/2011 a 30/04/2011'>
	<input type=hidden name=cod_Semana value='619'>
    <input type=hidden name=tipo value='2'>

Those are the elements of the form you're interested in.

And remember the page you're interested in landing on is 'Resumo_Semanal_Estado.asp'

I have obviously not ever set up POST method variables in a web query, so I can't give you much direction there.

Best of luck!

 

[cctanaka]

Thanks again,

One more doubt, I have seen the code place you have posted above. Shouldn´t we also send two more parameters which appear some lines below in the code of Resumo_semanal_index.asp?
These parameters are:
rdResumo
selCombustivel



Carlos César tanaka
Curso de Excel / Access
MPR Informática

 

[cctanaka]

Hi Gruuuu,

I have understood you did not used POST options in VBA before, but I think you really can help me.

Here is a code that uses this method, that I adapted from site

http://www.business-spreadsheets.com/forum.asp?t=103

You have to create an account and change in the constants below.

Sub Login_WebQuery()
Dim MyPost As String
Dim query As QueryTable
Const MyUrl As String = "

http://www.business-spreadsheets.com/forum.asp?t=103"

Const PostUser As String = "login=LLLLLLL" 'Change user name here
Const PostPassword As String = "&pass=PPPPPP" 'Change password here

Cells.Clear

For Each query In ActiveSheet.QueryTables
query.Delete
Next

Rem MyPost = PostUser & PostPassword & "&log=1"
MyPost = PostUser & "&log=1" & PostPassword

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
.PostText = MyPost
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

End Sub

But with the site I have told you before, I cannot see the variables necessary to fill .PostText property. Do you have any hint?

Carlos César tanaka
Curso de Excel / Access
MPR Informática