daily pastebin goal
40%
SHARE
TWEET

Untitled

a guest May 16th, 2018 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub GetHTMLDocument()
  2.  
  3.     'dimension (set aside memory for) our variables
  4.     Dim objIE As InternetExplorer
  5.     Dim ele As Object
  6.     Dim y As Integer
  7.  
  8.     'start a new browser instance
  9.     Set objIE = New InternetExplorer
  10.     'make browser visible
  11.     objIE.Visible = True
  12.  
  13.     'navigate to page with needed data
  14.     objIE.navigate "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'&sectionType=sp&statType=hitting&page=1&ts=1526432697176"
  15.     'wait for page to load
  16.     Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
  17.  
  18.     'we will output data to excel, starting on row 1
  19.     y = 0
  20.  
  21.     'look at all the 'tr' elements in the 'table' with id 'myTable',
  22.     'and evaluate each, one at a time, using 'ele' variable
  23.     For Each ele In objIE.document.getElementById("datagrid"). _
  24.       getElementsByTagName("tr")
  25.         'show the text content of 'tr' element being looked at
  26.         Debug.Print ele.textContent
  27.         'each 'tr' (table row) element contains 4 children ('td') elements
  28.         'put text of 1st 'td' in col A
  29.         Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent
  30.         'put text of 2nd 'td' in col B
  31.         Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent
  32.         'put text of 3rd 'td' in col C
  33.         Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent
  34.         'put text of 4th 'td' in col D
  35.         Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent
  36.         'put text of 4th 'td' in col f
  37.         Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent
  38.         'increment row counter by 1
  39.         y = y + 1
  40.            Next
  41.  
  42.     'save the Excel workbook
  43.     ActiveWorkbook.Save
  44. End Sub
  45.    
  46. Sub GetHTMLDocument()
  47.  
  48. Application.ScreenUpdating = False
  49.  
  50. 'dimension (set aside memory for) our variables
  51. Dim objIE As InternetExplorer
  52. Dim ele As Object
  53. Dim y As Integer
  54. Dim EstaPagina As Byte
  55. Dim EstaURL As String
  56.  
  57.  
  58. 'Página inicial
  59.  
  60. EstaPagina = 1
  61. 'we will output data to excel, starting on row 1
  62. y = 1
  63.  
  64. EstaURL = "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'&sectionType=sp&statType=hitting&page=" '&ts=1526432697176"
  65.  
  66. 'start a new browser instance
  67. Set objIE = New InternetExplorer
  68. 'make browser visible
  69. objIE.Visible = False
  70.  
  71. Do Until EstaPagina = 255
  72.     'navigate to page with needed data
  73.     objIE.navigate EstaURL & EstaPagina
  74.     'wait for page to load
  75.     Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
  76.  
  77.     'If UCase(Left(EstaURL, 211) & "1") = UCase(Left(objIE.LocationURL, (211 + Len(EstaPagina)))) And y > 1 Then Exit Do
  78.  
  79.     'look at all the 'tr' elements in the 'table' with id 'myTable',
  80.     'and evaluate each, one at a time, using 'ele' variable
  81.     For Each ele In objIE.document.getElementById("datagrid").getElementsByTagName("tr")
  82.         'show the text content of 'tr' element being looked at
  83.         'Debug.Print ele.textContent
  84.  
  85.         'each 'tr' (table row) element contains 4 children ('td') elements
  86.         'put text of 1st 'td' in col A
  87.         Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent
  88.         'put text of 2nd 'td' in col B
  89.         Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent
  90.         'put text of 3rd 'td' in col C
  91.         Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent
  92.         'put text of 4th 'td' in col D
  93.         Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent
  94.         'put text of 4th 'td' in col f
  95.         Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent
  96.         'increment row counter by 1
  97.         y = y + 1
  98.     Next
  99.  
  100.     EstaPagina = EstaPagina + 1
  101.  
  102. Loop
  103.  
  104. objIE.Quit
  105.  
  106. Set objIE = Nothing
  107. Set ele = Nothing
  108.  
  109. Range("A1").Select
  110. Range(Selection, Selection.End(xlToRight)).Select
  111. Range(Selection, Selection.End(xlDown)).Select
  112. Selection.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _
  113.     Header:=xlNo
  114.  
  115.  
  116. Application.ScreenUpdating = True
  117.  
  118. MsgBox "Volcado terminado", vbInformation
  119. Range("A1").Select
  120.  
  121.     'save the Excel workbook
  122.     ActiveWorkbook.Save
  123. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top