如何用vba将excel工作簿、工作表、单元格区域另存为html网页文件？

在excel中可以将excel工作簿另存为网页，如下图所示：

在vba中可以使用PublishObjects对象或者PublishObject对象的Publish方法将excel工作簿、工作表、单元格区域发布为网页。

在用vba excel工作簿、工作表、单元格区域发布为网页之前，要先用PublishObjects对象的Add方法添加要发布的对象。

以下vba代码可以将当前excel工作簿添加为发布对象，并发布为网页：

Sub QQ1722187970()
    Dim oWB  As Workbook
    Set oWB = Excel.ThisWorkbook
    Dim oPO As PublishObject
    Dim sPath As String
    sPath = Excel.ThisWorkbook.Path & "\"
    With oWB
        Debug.Print .PublishObjects.Count
        For Each oPO In .PublishObjects
            oPO.Delete
        Next
        '将整个工作簿添加为发布对象，发布为网页
       With .PublishObjects.Add(SourceType:=xlSourceWorkbook, Filename:=sPath & "Result.htm", HtmlType:=xlHtmlStatic, DivID:="Test1")
        '开始发布
            .Publish (True)
       End With
    End With
End Sub

如果要把其中某个工作表发布为网页，需要SourceType参数设置为xlSourceSheet，同时Source参数设置为工作表的名称。如下代码所示是将当前工作簿的第4个工作表添加为发布对象，并发布为网页：

Sub QQ1722187970()
    Dim oWB  As Workbook
    Set oWB = Excel.ThisWorkbook
    Dim oWk As Worksheet
    Set oWk = oWB.Worksheets(4)
    Dim oPO As PublishObject
    Dim sPath As String
    sPath = Excel.ThisWorkbook.Path & "\"
    With oWB
        Debug.Print .PublishObjects.Count
        For Each oPO In .PublishObjects
            oPO.Delete
        Next
        '将当前工作簿的第4个工作表添加为发布对象，发布为网页
   Set oPO = .PublishObjects.Add(xlSourceSheet, sPath & "Result.htm", oWk.Name, , xlHtmlStatic)
      With oPO
        '开始发布
            .Publish (True)
       End With
    End With
End Sub

如果要把某个工作表的某个单元格区域发布为网页，需要SourceType参数设置为xlSourceRange，同时Source参数设置为单元格区域的地址，如下代码所示：

Sub QQ1722187970()
    Dim oWB  As Workbook
    Set oWB = Excel.ThisWorkbook
    Dim oWk As Worksheet
    Set oWk = oWB.Worksheets(1)
    Dim oRng As Range
    Set oRng = oWk.Range("a1").CurrentRegion
    Dim oPO As PublishObject
    Dim sPath As String
    sPath = Excel.ThisWorkbook.Path & "\"
    With oWB
        Debug.Print .PublishObjects.Count
        For Each oPO In .PublishObjects
            oPO.Delete
        Next
        '将当前工作簿的第1个工作表的A1单元格的当前单元格区域添加为发布对象，发布为网页
        Set oPO = .PublishObjects.Add(xlSourceRange, sPath & "Result.htm", oWk.Name, oRng.Address, xlHtmlStatic)
        With oPO
            '开始发布
            .Publish (True)
        End With
    End With
End Sub