Web query. Rejected by server after 7 updates... Way around?

[SpeeD]

Hi!

Im querying a web server trough the excel web query. All is wright expect
for the fact tha the web server rejects my query after 7 updates. After that
i have to to shutdown excel and restarts again to have be able to update
again.
(The information that im accessing is public and im careful not to
overwhelm the web server).

Is there a way around this problem? does excel keeps the connection alive,
and there for creates this problem? is there a way to REALLY kill the
connection to the server? im totally new at this web query stuff....

Thanks a lot
Speed

 

[Don Guillett]

More info such as the url and the method used.
If desired, send your wb to my address below along with a snippet of this
msg.

 

[SpeeD]

Hi don

Sorry for the delayed reply.
My code is the following. If you run it 7 times the server rejects the
query, i have to restart excel do continue to query the server. I have about
15 querys like this i would like to run every day.

What happens when i close excel? does excel keeps any kind of connection
open??

WEB
1
http://publicacoes.mj.pt/pt/pesquis...sCAPTCHA=&pesquisar=Pesquisar&dfConcelhoDesc=

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

 

[Don Guillett]

I tried yours and had no problem. As I said, send your wb and I will test.

 

[Don Guillett]

Using this, I got 12 before having to exit excel and start over.

Sub getdata()
counter = 0
Do Until counter = 20
With Sheets("sheet1").QueryTables(1)
.Connection = _
"URL;http://publicacoes.mj.pt/pt/pesquisa.asp?iNIPC=" & _
"&sFirma=&dfDistrito=11&dfConcelho=06&dInicial=2009-04-09" & _
"&dFinal=2009-04-10&iTipo=1&sCAPTCHA=" & _
"&pesquisar=Pesquisar&dfConcelhoDesc"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
counter = counter + 1
If Sheets("sheet1").Range("a10") = "Erro" Then
MsgBox "Failed at " & counter
Exit Sub
End If
Loop

End Sub