Web query - one table multiple pages of data
 

Hi, I need to export a table from web to excel. The table is large and the
web only shows 20 entries per page and there are over 100 pages. I recorded
a macro to pull over one page at a time but as more things are added to the
table the pages increase and when they increase I need to add a new block of
code.

I have a feeling it's possible to get the whole table, irrespective of
number of pages in the one go. However, I don't know how to do that. Could
you let me know if this is actually possible and how to go about it please?

A section of the code is at end of this message. For each new page I
increase all numbers by 20.

Thanks for your help.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.websitename/tbl_tablename.asp?start=1", _
Destination:=Range("$A$1"))
.Name = "tbl_tablename.asp?start=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Web query - one table multiple pages of data
 

Try recording a macro to edit instead of add. While doing use ALL sheets
instead of sheet 4
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"refresh" wrote in message
...
Hi, I need to export a table from web to excel. The table is large and the
web only shows 20 entries per page and there are over 100 pages. I
recorded
a macro to pull over one page at a time but as more things are added to
the
table the pages increase and when they increase I need to add a new block
of
code.

I have a feeling it's possible to get the whole table, irrespective of
number of pages in the one go. However, I don't know how to do that.
Could
you let me know if this is actually possible and how to go about it
please?

A section of the code is at end of this message. For each new page I
increase all numbers by 20.

Thanks for your help.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.websitename/tbl_tablename.asp?start=1", _
Destination:=Range("$A$1"))
.Name = "tbl_tablename.asp?start=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With