HELP - web query suddenly freezing after working fine

[detectf]

I use Excel to pull data from

http://www.sportinglife.com

(amongst others) by using a web query in VBA. Until today all was fine, but now the web query freezes.

If I load the url (

http://horses.sportinglife.com/Full_Results/0,12493,471638,00.html)

in IE then it's fine - it loads almost instantaneously - but if I open up a new worksheet and bring up a new web query it takes an age to load (~ 2mins) and then when I try to import I get "contacting the server for information" dialog. I've left this running for 10 minutes or more without any success - and the only way I can stop it is to close down Excel in Task Manager - cancel buttons/ctrl+break do nothing. During this, if I load the page straight into IE it loads without any problems.

Before anyone says it, yes, I know I could stick an IE control on a form and use the DOM to parse the HTML - and I do that for more complex minings - but I still don't understand why SL pages can't be accessed through web queries.

Originally wrote the above a few hours ago, just tested it again and it worked once... and then failed again. I'm trying a few other urls to see if it's a localized issue or not.

http://www.telegraph.co.uk

also gives the same error when I try to load it from the "new web query" dialog on a worksheet, but of course it loads just fine in IE. The telegraph page has a flash advert at to top for Lloyds bank (yoga mat or figures on a bike). Could that be the cause of the error?

Frustrating times here.

thanks

thanks

 

[SkipVought]

hi,

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[detectf]

Hi Skip, thanks for replying.

The code has been working for years and if the manual way of creating a web query through excel was working then I'd think there was an error with the code... but as the manual way isn't working, I'm not convinced the code is at fault.

Here it is though - all arguments checked and valid. Code hangs on .Refresh BackgroundQuery:=False Ctrl+break does nothing, can't right click on excel and close - have to use task manager.

Public Sub rwqHTML(u As String, dest As Excel.Worksheet, cell As String)
dest.Activate
On Error Resume Next
With dest.QueryTables.Add(Connection:= _
"URL;" & u _
, Destination:=dest.Range(cell))
.Name = "temp"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 1 '0 (zero) don't refresh
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True

.Refresh BackgroundQuery:=False

End With
End Sub

 

[SkipVought]

How many query tables have you ADDED in this workbook? Do you realize that each QT that you add is an object in your workbook? I do not know what effect this has on the performance of your workbook.

I usually ADD one time and then REFRESH on subsequent usages.

YourSheetObject.QueryTables(1).Refresh false

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[orangeseatbelt]

yes, I do realise that QT are objects.... and I already delete as nec. in a separate sub.

The workbook runs without problem - except for the two urls mentioned above.


Have you tried to load and import

http://www.telegraph.co.uk

via the New Web Query dialog in Excel at all? Perhaps that would be a useful starting point to try and replicate the problem outlined above - particularly if you note the advert that is displayed at the head of the page.

 

[SkipVought]

Yes, I experience that also, but have nothing to contribute.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[orangeseatbelt]

Ok, well at least that confirms that a flash ad may be to blame, it would also go some way to explaining why the sportlinglife url did work on random occasions.

Something new in flash perhaps that web queries just can't handle.

May I wask which version of Excel you're using? I'm on 2007 and I did thing perhaps a move to 2010 might be better able to cope.

Ah well, thanks for trying.

 

[SkipVought]

2007

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]