[ExcelVBA] VB to combine multiple webpage tables of the same format/name

 

Listers

I thought it was worthwhile sharing this macro, just feed it the table
name from the pages you want to combine, tblTransactionSearchResult
was the table name in my case (from a simple webquery)

I engineered the syntax of the search to increment the pagenumber
(this is NOT the URL I used!)

http://www.yadayadayada.com?PageNumber=1
http://www.yadayadayada.com?PageNumber=2 ...... etc

If your incremental URL format is different you will have to sort that out

It will import the WHOLE table so you will have to filter out or
delete the headers or footers

Just used it to grab 4500 tables!

here it is!

Dr B

============

Sub getwebqueries()

Dim mysheet As Worksheet
Dim mywebquery As QueryTable

Set mysheet = ActiveSheet

For i = 1 To 1000 ' how many pages you have to download
Set mywebquery = mysheet.QueryTables _
.Add(Connection:="URL;http://www.yadayadayada.com?PageNumber=" & i, _
Destination:=mysheet.Cells(i * 15, 1)) 'the spacing of the imports
With mywebquery
.WebFormatting = None
' .WebSelectionType = tblTransactionSearchResult
.WebTables = "tblTransactionSearchResult"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.BackgroundQuery = False
.Refresh

End With

Set mywebquery = Nothing

Next i

--

===========================
http://uk.linkedin.com/in/drjohnbullas

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___