Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    grab table from site and insert and find (excel)

    i am building a little excel worksheet that goes to a specified website and pulls in infromation on from a table
    I was wondering if someone knows the best way i can import a table of information. The problem is that there is about 10 pages each having a table that I need to
    make the excel workbook fuction properly.

    My question is how can I bring in only the table on the internet page need and loop into the next page and bring in the next table.

    The reason for this is that page 1 lists the top 30 players. more players are listed on page 2 and so on. would like to be able to bring in all the players on each page available.
    any bright ideas on how to do this? i have recorded a macro which does basically what I want but brings the whole web page and all that is needed is the stats.
    Thanks

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    http://www.nhl.com/nhlstats/stats?service=...t=points&page=1

    this is the website i am dowloading from
    at the end it says points&page=1
    I was wonder how i could increment this number up by one so that I can move to the next page and get its table until the last table :22?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (excel)

    Instead of

    .WebSelectionType = xlEntirePage

    use

    .WebSelectionType = xlSpecifiedTables
    .WebTables = "1"

    to import the first table on the page. If you wanted to import the second and third table, you'd use

    .WebSelectionType = xlSpecifiedTables
    .WebTables = "2,3"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (exce

    You could use something like

    Dim i As Integer
    For i = 1 To 22
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://...&sort=points&page=" & i _
    , Destination:=Range("A1"))
    ...
    End With
    Next i

    (You should use the full web address instead of ...)

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    sorry never seen the last post.

    I have implemented it into my code put came up with numerous problems

    1. the data i pasted across the columns
    what can i add so it will import down the rows.

    2."stats?service=page&context=Stats&fetchKey=20062 ALLAAS&viewName=points&sort=points&page=1"
    should this line also be i instead of 1

    i have update the last post with the code i am presently using.

    thanks so much for your help on this

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    thanks hans

    is their peices of code not need for this macro
    i used the record method.

    also, how do i build a loop which increments page=1 to page=2 and so on till 22? stats?service=page&context=Stats&fetchKey=20062ALL AAS&viewName=points&sort=points&page=1

    Sub pullintable()
    '
    ' pullintable Macro
    ' Macro recorded 10/23/2005 by Patrick
    '

    '
    Dim i As Integer
    For i = 1 To 22
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.nhl.com/nhlstats/stats?service=page&context=Stats&fetchKey=20062ALL AAS&viewName=points&sort=points&page=i" _
    , Destination:=Range("A1"))
    .Name = _
    "stats?service=page&context=Stats&fetchKey=20062AL LAAS&viewName=points&sort=points&page=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
    .WebTables = "3"
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=True
    End With
    Next i
    ActiveWindow.SmallScroll Down:=-30
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (exce

    1. The table will be imported in the same order as in the web page. If you want to transpose the rows and columns, you must do that yourself, for example by using Copy, then Paste Special > Transpose. This can be done in code too.

    2. Yes, but I would use simpler names, e.g.

    .Name = "MyTable" & i

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    thank you,

    i worded my question wrong in question number 1.

    I was trying to ask about the destination of the next table.
    table 1 starts its import in A1,

    could table 2 be imported in A52

    I tried setting up a routine for this but is has a few bugs how do i set the destination to increase by 52 rows each time aa table is imported. As in my code I set the destination at the end to increase by 52 by using varible x. then the code start over on i and runs through destiation being set as A1 Again?:

    '

    '
    Dim i As Integer
    Dim x As Integer
    x = 51
    For i = 1 To 5
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.nhl.com/nhlstats/stats?service=page&context=Stats&fetchKey=20062ALL AAS&viewName=points&sort=points&page=" & i _
    , Destination:=Range("A1"))
    .Name = "MyTable" & i
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "3"
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=True
    End With
    Set Destination = Range("A" & x)
    Next i
    ActiveWindow.SmallScroll Down:=-30
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (exce

    Remove the line

    Set Destination = Range("A" & x)

    and change

    , Destination:=Range("A1"))

    to

    , Destination:=Range("A65536").End(xlUp).Offset(1,0) )

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    Thanks Hans,
    I made the change and still come up with the import being pasted in across the workbooks columns.
    The code below seems to still bring in the table and move on to Table 2 and import it in to the columns to the right of is. Therefore it runs out of space.

    I am not sure if the import is only limited to pasting into columns?


    Option Explicit ()
    Dim i As Integer


    For i = 1 To 5
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.nhl.com/nhlstats/stats?service=page&context=Stats&fetchKey=20062ALL AAS&viewName=points&sort=points&page=" & i _
    , Destination:=Range("A65536").End(xlUp).Offset(1,0) )
    .Name = "MyTable" & i
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "3"
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=True
    End With
    Next i
    ActiveWindow.SmallScroll Down:=-30
    End Sub

    Thanks

  11. #11
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: grab table from site and insert and find (exce

    Hi Jazzy,

    I havent tested this buit give it a try


    Option Explicit ()
    Dim i As Integer
    Dim strInsertCellAddress As String

    For i = 1 To 5
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.nhl.com/nhlstats/stats?service=page&context=Stats&fetchKey=20062ALL AAS&viewName=points&sort=points&page=" & i _

    'Determine the destination cell for the query table.
    strInsertCellAddress = ActiveCell.Address(RowAbsolute:=False, columnabsolute
    ', Destination:=Range("A65536").End(xlUp).Offset(1,0) )
    .Name = "MyTable" & i
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "3"
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=True
    End With
    Next i
    ActiveWindow.SmallScroll Down:=-30
    End Sub

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    thanks matix
    I added and fixed this line
    strInsertCellAddress = ActiveCell.Address(RowAbsolute:=False, columnAbsolute:=True)
    still doesnt wrk oput i think that the import might just have to br loaded across columns

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (exce

    I think it's due to the way querytables work. You might move the blocks below each other after importing, but it mighr be easier to live with the horizontal format.

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab table from site and insert and find (exce

    Hans,
    Would it be possible to have the destination increase by 33 each time through a loop?
    so table 1 starts its destination at a1
    table 2 starts its destination at a33
    table 3 at a66
    and so on?
    Destination:=Range("A1"))

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: grab table from site and insert and find (exce

    Nope. doesn't work.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •