title: Website data table scraper
body: <p>Before I ask my question, I'm an amateur coder with basically no meaningful experience beyond VBA in ms office apps (I know - noob!)</p>
  
  <p>I'm trying to create a web scraper using VBA to import data into excel and as per my comments in the below extract of code, the best I've been able to find on this is was in the winning answer to <a href="https://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba">this question</a>.</p>
  
  <p>Below, I'm using investing.com as an example but in reality my project will be across multiple sites and will feed into a matrices which will be updating daily and self cannibalizing as events expire - For this reason I'd rather front-up the workload on the code side to make the inputs on an ongoing basis as minimal as possible (for me).</p>
  
  <p>With that in mind, can I ask if there's a way to do any of the following (brace yourself, this will be cringe-worthy basic knowledge for some):</p>
  
  <ol>
  <li><p>Is there a way in which I can and navigate to a url and run a <code>for each</code> loop on every table on that page (without have a known id for any)? this is to speed up my code as much as it's to minimise my inputs as there'll be quite a bit of data to be updated and I was planning on putting a 2 minute looping trigger on the refresh.</p></li>
  <li><p>Instead of doing what I've been doing below, is it possible to reference a table, rather than a row, and do something along the lines of Cells(2,5).value to return the value within row 1, column 4? (assuming that both the array indexing starts at 0 in both dimensions?) Further to that, my first column (my primary key in some ways) may not be in the same order on all sources so is there a way in which I could do the equivalent to <code>Columns("A:A").Find(What:=[Primary key], After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Row</code> to find what row within the table relates to the even I'm looking for?</p></li>
  </ol>
  
  <p>Code :</p>
  
  <pre><code>Sub Scraper()
  Dim appIE, allRowOfData As Object
  
  ' As per https://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba
  
  Set appIE = CreateObject("internetexplorer.application")
  
  With appIE
     .Navigate "http://uk.investing.com/rates-bonds/financial-futures" 'Sample page
     .Visible = False
  End With
  
  Do While appIE.Busy
      Application.Wait (Now + TimeValue("0:00:01")) 'If page not open, wait a second befor trying again
  Loop
  
  Set allRowOfData = appIE.document.getElementById("pair_8907") 
  'tr id="[ID of row within table]"
  Dim myValue As String: myValue = allRowOfData.Cells(8).innerHTML 
  'The 8 is the column number of the table 
  '(note: column numbers start at 0 so the 9th column should have "8" entered here
  
  Set appIE = Nothing
  
  Range("A1").Value = myValue
  
  End Sub
  </code></pre>