I have tried a few times to find a solution to this, but still haven't.
I have a macro which carries out multiple web queries using a URL found on sheet "postcodes"
In versions of excel before 2010 it works no problem but in 2010 it hangs, not always on the same URL and sometimes it runs all the way through no problem.
I've read that "pinging" the URL before the web query may help.
If this is true please can someone help me with the code that I would need to include
An example URL is
Weather forecast for Postcode TR1 | Local | Cornwall, United Kingdom
My code is:
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8"
With Sheets("Download").QueryTables.Add(Connection:="URL;" & Sheets("Postcodes").Range("B4").Value, _
Destination:=Sheets("Download").Range("$A$1"))
.Name = "web_query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets("Download").QueryTables(1).Delete
Application.Wait Now + TimeValue("00:00:01") 'this pauses macro for 1 second
I have a macro which carries out multiple web queries using a URL found on sheet "postcodes"
In versions of excel before 2010 it works no problem but in 2010 it hangs, not always on the same URL and sometimes it runs all the way through no problem.
I've read that "pinging" the URL before the web query may help.
If this is true please can someone help me with the code that I would need to include
An example URL is
Weather forecast for Postcode TR1 | Local | Cornwall, United Kingdom
My code is:
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8"
With Sheets("Download").QueryTables.Add(Connection:="URL;" & Sheets("Postcodes").Range("B4").Value, _
Destination:=Sheets("Download").Range("$A$1"))
.Name = "web_query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Sheets("Download").QueryTables(1).Delete
Application.Wait Now + TimeValue("00:00:01") 'this pauses macro for 1 second