VBA将HTML表格数据复制到Excel工作表 [英] VBA copy html table data to excel worksheet

问题描述

我需要一个VBA脚本，该脚本可以将本地html表数据提取到Excel工作表中.我有一些代码(在网络上的某个地方找到了)，可以通过使用URL链接来工作，但是我想要的是能够使用本地存储的html文件来实现.我得到的错误是应用程序定义或对象定义的错误" .

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 'app defined or object defined error'.

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 = "http://espn.go.com/nba/" 
    
    'Create HTMLFile Object 
    Set HTML_Content = CreateObject("htmlfile") 

    'Get the WebPage Content to HTMLFile Object 
    With CreateObject("msxml2.xmlhttp") 
        .Open "GET", 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("table") 
        With HTML_Content.getElementsByTagName("table")(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 "Process Completed" 
End Sub

推荐答案

我遇到了相同的问题，为了解决该问题，我使用了问题的原始代码，但没有下载html，而是将html作为文本文件打开了并将结果传递给对象HTML_Content.body.innerHtml，其余代码相同.

I had the same problem and to solve it I used the original code of the question, but instead of downloading the html, I opened the html as a text file and the result was passed to the object HTML_Content.body.innerHtml the rest of the code is same.

Sub HTML_Table_To_Excel() 

Dim htm As Object 
Dim Tr As Object 
Dim Td As Object 
Dim Tab1 As Object
Dim file as String

'Replace the file path with your own 
file = "c:\your_File.html"

'Determine the next file number available for use by the FileOpen function
TextFile = FreeFile

'Open the text file
Open file For Input As TextFile

'Create HTMLFile Object 
Set HTML_Content = CreateObject("htmlfile") 
HTML_Content.body.innerHtml = Input(LOF(TextFile), TextFile)

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("table") 
    With HTML_Content.getElementsByTagName("table")(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 "Process Completed" 
End Sub

这篇关于VBA将HTML表格数据复制到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！