首页 文章

如何将带有html标签的文本列转换为excel中vba中的格式化文本

提问于
浏览
1

我想知道如何使用VBA脚本将带有html标签的整列单元格转换为格式化文本(基于这些标签) .

screenshot of spreadsheet

我能够根据以前的列表转换一个单元格:HTML Text with tags to formatted text in an Excel cell

使用以下内容:

Sub Sample()
    Dim Ie As Object
    Set Ie = CreateObject("InternetExplorer.Application")
    With Ie
        .Visible = False
        .Navigate "about:blank"
        .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
             'update to the cell that contains HTML you want converted
        .ExecWB 17, 0
             'Select all contents in browser
        .ExecWB 12, 2
             'Copy them
        ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B1")
             'update to cell you want converted HTML pasted in
        .Quit
    End With
End Sub

但这只会转换列中的第一个单元格 . (在上面的示例中,我手动键入A2和B2以执行第二个单元格) . 如果这是一个天真的问题,我很抱歉,但我是VBA的新手 . 我试过使用循环并玩范围,但没有成功 .

2 回答

  • -1

    您的代码仅适用于第一行,因为您只获取并设置第一行:

    'get the A1 cell value
    .document.body.InnerHTML = Sheets("Sheet1").Range("A1").Value
     'set the B1 cell value
     ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B1")
    

    要为所有行应用代码,必须在循环内执行它 .

    所以你的代码变成:

    Sub Sample()
    
    Dim Ie As Object
    
    'get the last row filled
    lastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
    'loop to apply the code for all the lines filled
    For Row = 1 To lastRow
        Set Ie = CreateObject("InternetExplorer.Application")
        With Ie
            .Visible = False
            .Navigate "about:blank"
            .document.body.InnerHTML = Sheets("Sheet1").Range("A" & Row).Value
                 'update to the cell that contains HTML you want converted
            .ExecWB 17, 0
                 'Select all contents in browser
            .ExecWB 12, 2
                 'Copy them
            ActiveSheet.Paste Destination:=Sheets("Sheet1").Range("B" & Row)
                 'update to cell you want converted HTML pasted in
            .Quit
        End With
        Set Ie = Nothing
    Next
    
    End Sub
    
  • 0

    请检查:

    Option Explicit
    
    
        Sub Sample()
    
            Dim Ie As Object
    
            Dim i As Long, lastrow As Long
    
            lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    
            On Error Resume Next
    
           For i = 1 To lastrow
             Set Ie = CreateObject("InternetExplorer.Application")
    
            With Ie
    
                .document.body.InnerHTML.Reset
                .Visible = False
                .Navigate "about:blank"
                .document.body.InnerHTML = Sheets("Sheet1").Cells(i, "A").Value
                     'update to the cell that contains HTML you want converted
    
    
                .ExecWB 17, 0
                     'Select all contents in browser
                .ExecWB 12, 2
                     'Copy them
    
    
                Sheets("Sheet1").Paste Destination:=Sheets("Sheet1").Cells(i, "B")
    
    
                     'update to cell you want converted HTML pasted in
    
    
                .Quit
    
            End With
    
              Next
    
        End Sub
    

相关问题