Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to web tables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access to web tables
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. On Aug 7, 9:32 am, Scott wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing tables in access | Excel Discussion (Misc queries) | |||
VLOOKUP from Access Tables | Excel Worksheet Functions | |||
Access queries/tables | Excel Discussion (Misc queries) | |||
Access tables in Excel | Excel Discussion (Misc queries) | |||
Writting to tables in Access | Excel Programming |