Ledger Nano X - The secure hardware wallet
SHARE
TWEET

scrape example

a guest Oct 31st, 2012 828 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Explicit
  2.  
  3. Sub WebScraping()
  4.  
  5. Dim oIE As Object        ' for Internet Explorer Object ...
  6. Dim sURL As String        ' URL String ..
  7. Dim sStocklist As String        ' Stocklist String ...
  8. Dim vTxtInput As Variant        ' input texbox ...
  9. Dim ieDoc As Object        ' the document retrieved ...
  10. Dim IeTable As Object        ' the table where our data resides ...
  11. Dim ieCell As Object        ' the cells (in the table above) holding our data ...
  12.  
  13. Dim ElementCol As MSHTML.IHTMLElementCollection        ' to loop thru element collection ...
  14. Dim btnInput As MSHTML.HTMLInputElement        '           |
  15. Dim x As Integer, i As Integer, p As Integer        ' misc counters
  16.  
  17. ' load our stocklist
  18. sStocklist = "MSFT,GE,O,DIS,C,JPM,BBT,VFINX,T,SAN,VZ"
  19.  
  20. ' instantiate the oIE object ...
  21. Set oIE = CreateObject("InternetExplorer.Application")
  22.  
  23. ' open Yahoo finance ...
  24. sURL = "http://www.Finance.Yahoo.com"
  25. With oIE
  26.     .Navigate sURL
  27.     .Visible = True
  28.  
  29.     ' loop until the page finishes loading ...
  30.    Do While .Busy
  31.     Loop
  32.  
  33.     ' enter our stocklist in the stockqoutes input box
  34.    Set vTxtInput = .document.getElementsByName("s")
  35.     vTxtInput(0).Value = sStocklist
  36.  
  37.     ' click 'Submit' button
  38.    Set ElementCol = .document.getElementsByTagName("button")
  39.     For Each btnInput In ElementCol
  40.         If btnInput.Title = "Get Quotes" Then
  41.             btnInput.Click
  42.             Exit For
  43.         End If
  44.     Next btnInput
  45.  
  46.     ' loop until the page finishes loading
  47.    Do While .Busy
  48.     Loop
  49.  
  50.     Sheets("Web Scraping Using Automation").Activate
  51.     Range("Start1").Select
  52.  
  53. End With
  54.  
  55. Set ieDoc = oIE.document
  56.  
  57. 'Loop through all the elements in the document via the 'all' property
  58. For i = 0 To ieDoc.all.Length - 1
  59.     ' check that we have the right table ...
  60.    If TypeName(ieDoc.all(i)) = "HTMLTable" And _
  61.        InStr(ieDoc.all(i).innerText, "Symbol") > 0 Then
  62.         Set IeTable = ieDoc.all(i)
  63.         ' loop thru each row in our table
  64.        ' note we skip the first (header) row below ...
  65.        For x = 1 To IeTable.Rows.Length - 1
  66.             ' loop thru the cells in the row ...
  67.            For p = 0 To IeTable.Rows(x).Cells.Length - 1
  68.                 Set ieCell = IeTable.Rows(x).Cells(p)
  69.                 ActiveCell.Offset(x, p).Value = ieCell.innerText
  70.             Next
  71.         Next
  72.         Exit For
  73.     End If
  74. Next i
  75.  
  76. ' clean up ...
  77. oIE.Quit
  78. Set oIE = Nothing
  79.  
  80. 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