icaos
Guest Posts: n/a

help with web query

--------------------------------------------------------------------------------

Hi,
I=B4m 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=B4t 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=B4re taken to the table. Can anyone help me?
the pages are these :

http://www.sidra.ibge.gov.br/bda/ta...5&i=3DP&c=3D655

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

http://www.sidra.ibge.gov.br/bda/ta...3Dt&o=3D5&i=3DP

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


thanks very much.




icaos

#2 02-11-2005, 02:06 PM
MisterPurple
Guest Posts: n/a

Re: help with web query

--------------------------------------------------------------------------------

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




MisterPurple

#3 02-11-2005, 02:06 PM
icaos
Guest Posts: n/a

Re: help with web query

--------------------------------------------------------------------------------

I=B4m 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=3Dt&o=3D1&i=3DP&c=3D655

http://www.sidra.ibge.gov.br/bda/ta...3Dt&o=3D1&i=3DP




icaos

#4 02-11-2005, 02:06 PM
MisterPurple
Guest Posts: n/a

Re: help with web query

--------------------------------------------------------------------------------

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




MisterPurple

#5 02-11-2005, 03:06 PM
icaos
Guest Posts: n/a

Re: help with web query

--------------------------------------------------------------------------------

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=B4t come up with anything usefull...]

thanks again,
Ian




icaos

#6 02-11-2005, 05:06 PM
Jake Marx
Guest Posts: n/a

Re: help with web query

--------------------------------------------------------------------------------

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 wrote:
> 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

#7 Yesterday, 08:37 AM
iancao is Online:
Registered User Join Date: Feb 2005
Posts: 4

import

--------------------------------------------------------------------------------

Thank you very much Jake,

I used the code and I´m getting the following error :

-2147483638(8000000a):

wich means something like : data necessary for the operation not avaiable

it seems that using some delay (like wait()) ond the end of the code might work...

another thing : how can I import the results into a worksheet ?
I´m not familiar with this debug.print output.

Thanks again for the great help!
Ian Caó

iancao
View Public Profile
Send a private message to iancao
Find all posts by iancao
Add iancao to Your Buddy List

#8 Yesterday, 09:30 AM
iancao is Online:
Registered User Join Date: Feb 2005
Posts: 4

detail

--------------------------------------------------------------------------------

Hi Jake,

How do you get those codes (like qtu1,qtu6..)

It would help me a lot because I´m also trying to change one of the parameters :

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

I would like to change the field:

IPCA - Percentual no mês Geral, grupo, subgrupo, item e subitem(593):

to the option " TUDO"

Can you help me?Again!? LOL

Thank you very much.