title: VBA copy html table data to excel worksheet
body: <p>I need a VBA script that can extract local html table data to an Excel worksheet. I have some code (found it somewhere on the web) that works by using a URL link, but what I want is to be able to do it using my locally stored html file. The error is I get is <strong>'app defined or object defined error'</strong>.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub HTML_Table_To_Excel() 
  
      Dim htm As Object 
      Dim Tr As Object 
      Dim Td As Object 
      Dim Tab1 As Object 
      
      'Replace the URL of the webpage that you want to download 
      Web_URL = &quot;http://espn.go.com/nba/&quot; 
      
      'Create HTMLFile Object 
      Set HTML_Content = CreateObject(&quot;htmlfile&quot;) 
  
      'Get the WebPage Content to HTMLFile Object 
      With CreateObject(&quot;msxml2.xmlhttp&quot;) 
          .Open &quot;GET&quot;, Web_URL, False 
          .send 
          HTML_Content.body.innerHTML = .responseText 'this is the highlighted part for the error 
      End With 
      
      Column_Num_To_Start = 1 
      iRow = 2 
      iCol = Column_Num_To_Start 
      iTable = 0 
  
      'Loop Through Each Table and Download it to Excel in Proper Format 
      For Each Tab1 In HTML_Content.getElementsByTagName(&quot;table&quot;) 
          With HTML_Content.getElementsByTagName(&quot;table&quot;)(iTable) 
              For Each Tr In .Rows 
                  For Each Td In Tr.Cells 
                      Sheets(1).Cells(iRow, iCol).Select 
                      Sheets(1).Cells(iRow, iCol) = Td.innerText 
                      iCol = iCol + 1 
                  Next Td
                  iCol = Column_Num_To_Start 
                  iRow = iRow + 1 
              Next Tr 
          End With 
  
          iTable = iTable + 1 
          iCol = Column_Num_To_Start 
          iRow = iRow + 1 
      Next Tab1 
  
      MsgBox &quot;Process Completed&quot; 
  End Sub
  </code></pre>