help with web query

[icaos]

Hi,
I´m trying to run a query to import some data from the web.
The problem is that the url from the page doesnt work with the query.
The data is displayed in the web as a table , generated by hitting an
"OK" button. The problem is that the url from the generated table(page)
seems to be some generic address. You can´t load the table just by
writing the url on the IE. You have to go to a page, hit the "OK"
button and then you´re taken to the table. Can anyone help me?
the pages are these :

http://www.sidra.ibge.gov.br/bda/ta...t&o=5&i=P&c=655

and after pressing the ok you get to that one, wich is the one
containing the data I´m looking for :

http://www.sidra.ibge.gov.br/bda/ta...asp?z=t&o=5&i=P

if you use this last address directly as an url an error message shows
up.


thanks very much.

 

[MisterPurple]

The URL's in your post do NOT work. Please provide working URL's and
I'll try to help you. I'm expert user of web queries so I should be
able to help you but I need to see the data first.

-Ken

 

[icaos]

I´m sorry!!
thanks for trying t help me.
this is the correct url for the first url. you have to click the "OK"
button in the bottom of the page to be redirected to the next url.


http://www.sidra.ibge.gov.br/bda/tabela/listabl.asp?
z=t&o=1&i=P&c=655

http://www.sidra.ibge.gov.br/bda/tabela/protabl.asp?z=t&o=1&i=P

 

[MisterPurple]

Unfortunately, the output table is not showing up in Excel. I've tried
several things but couldn't get it to show up. The first URL is passed
to server which creates the HTML for the table, but it uses the input
parameter string plus your selections on the first page to generate the
second page.

Thus,

First URL Parameters + First URL Selection Inputs = Second Page

It's not enough to just use the 2nd URL to generate the table. You
need the inputs from the first page which are not being passed into the
2nd URL. Rather, your selection inputs are passed to the server and
the HTML rendering is automatically generated.

Sorry.

-Ken

 

[icaos]

thanks anyway!

I tried to find something on the first url's HTML code that would help
me building a code to acces directly the data on the second url, but I
couldn´t come up with anything usefull...]

thanks again,
Ian

 

[Jake Marx]

Hi Ian,

The following routine will get the data you need, but you will have to parse
it out programmatically. In order to get it to work, you must set a
reference to Microsoft XML 5.0 via Tools | References in the VBE. If you
don't have 5.0, use the highest you have and change the first line of code
to the correct version number.

Sub test()
Dim xml As XMLHTTP50
Dim sPost As String
Dim abytPostData() As Byte
Dim sResponse As String

sPost = "i=P&"
sPost = sPost & "gv=n&"
sPost = sPost & "tab=655&"
sPost = sPost & "nivt=0&"
sPost = sPost & "unit=0&"
sPost = sPost & "pov=1&"
sPost = sPost & "orv=2&"
sPost = sPost & "opv=1&"
sPost = sPost & "sev=63&"
sPost = sPost & "opc315=1&"
sPost = sPost & "poc315=1&"
sPost = sPost & "orc315=3&"
sPost = sPost & "sec315=7169&"
sPost = sPost & "ascendente=&"
sPost = sPost & "opp=1&"
sPost = sPost & "pop=1&"
sPost = sPost & "orp=4&"
sPost = sPost & "sep=28953&"
sPost = sPost & "nome=&"
sPost = sPost & "pon=1&"
sPost = sPost & "orn=1&"
sPost = sPost & "qtu1=1&"
sPost = sPost & "opn1=2&"
sPost = sPost & "qtu6=2&"
sPost = sPost & "opn6=0&"
sPost = sPost & "opn7=0&"
sPost = sPost & "qtu7=9&"
sPost = sPost & "proc=1&"
sPost = sPost & "notarodape=&"
sPost = sPost & "arquivo=&"
sPost = sPost & "formato=1&"
sPost = sPost & "modalidade=1&"
sPost = sPost & "email=&"
sPost = sPost & "decm=99&"
sPost = sPost & "cabec=&"
sPost = sPost & "gera= OK "

abytPostData = StrConv(sPost, vbFromUnicode)
Set xml = New XMLHTTP50
With xml
.Open "POST", _
"http://www.sidra.ibge.gov.br/bda/tabela/protabl.asp?z=t&o=1&i=P"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With

Set xml = Nothing
Debug.Print sResponse
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[icaos]

jake,
you are incredible!!
Thanks a lot!!