时间戳

首页 > 解决方案 > 将html数据导入Excel时如何显示阿拉伯字母？

html - 将html数据导入Excel时如何显示阿拉伯字母？

问题描述

我在这里阅读了一个关于将 html 转换为 Excel 的问题，它可以工作，但有未解决的问题。如果 html 包含阿拉伯字母，因为它在导入后无法在 Excel 中正确显示。

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

标签： htmlexcelvbacharacter-encoding

解决方案

charSet是一个只读属性。如果尝试通过此属性进行设置，这可能是您的错误的根源。

我想你可能想要：

HTML_Content.defaultCharset = "UTF-8"

我更愿意MSHTML.HTMLDocument通过 VBE > Tools >References > Microsoft HTML Object Library reference 声明 early bound，然后像html.defaultCharset = "UTF-8"where htmlis your instance of the object。

我真的不喜欢htmlFile它，因为它是如此有限，并且仅在您绝对无法添加项目引用时才有用。

另外，请声明所有变量并Option Explicit在模块顶部使用。

推荐阅读