Access to web tables

[Guest]

I need to pull information from web and put them in Column F. The site
address are listed in A1:A5. Followings are the codes. When I run them, I get
a message "Invalid Web Query". Out of the 5 queries, 1 or 2 open up incorrect
web pages and cannot tell which ones will be correct and which ones will be
incorrect beforehand. Anyone can tell me what happened and how to fix the
problem?

Sub Webvalue()

Dim c As Range
Dim str As String
Dim i As Integer

i = 0
For Each c In ThisWorkbook.Sheets("Sheet2").Range("A1:A5")
c.Select
str = "URL;" & Selection.Hyperlinks(1).Address

With ActiveSheet.QueryTables.Add(Connection:=str,
Destination:=Range("F1").Offset(i, 0))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "2,4,5,6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Refresh
End With
i = i + 30
Next c
End Sub

 

[Randy Harmelink]

It may depend entirely upon the URLs you're running against, which you
haven't shown us. Can I assume all the web pages are from the same
web site and in the same format? If not, the:

.WebTables = "2,4,5,6"

....statement makes no sense, as that would be extracting the exact
same tables, based on relative page location, off of every URL you run
a query against. If the pages don't have the same format, that
wouldn't work very well.

Also, I've seen instances where web pages can be written to defeat the
Web Query process, so you could also have problems if your URLs are
going against such pages.