title: VBA to extract HTML Table data
body: <p>I am receiving a run-time error '91': object variable or with block variable not set whenever I try to run my code below. This code works perfect when extracting the data off of the commented out website I have listed in the code. However, when I try to use it on the google finance site I get that error. Can anyone see what I am doing wrong? I am using the following VBA: </p>
  
  <pre><code>Sub test()
  
  Dim oDom As Object: Set oDom = CreateObject("htmlFile")
  Dim x As Long, y As Long
  Dim oRow As Object, oCell As Object
  Dim data
  
  y = 1: x = 1
  
  With CreateObject("msxml2.xmlhttp")
  'http://www.bundesbank.de/Navigation/EN/Statistics/Time_series_databases/Macro_economic_time_series/its_details_value_node.html?tsId=BBNZ1.Q.DE.Y.G.0000.A&amp;listId=www_s311_b4_vgr_verw_nominal
      .Open "GET", "http://finance.yahoo.com/q/hp?s=GOOG+Historical+Prices", False
      .Send
      oDom.body.innerHtml = .responseText
  End With
  
  With oDom.getElementsByTagName("table")(0)
      ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
      For Each oRow In .Rows
          For Each oCell In oRow.Cells
              data(x, y) = oCell.innerText
              y = y + 1
          Next oCell
          y = 1
          x = x + 1
      Next oRow
  End With
  
  Sheets(1).Cells(1, 1).Resize(UBound(data), UBound(data, 2)).Value = data
  End Sub
  </code></pre>