将VBA Excel内容转换为HTML（从Excel创建HTML文件）

Can anyone help me make this code viable?

任何人都可以帮助我使这段代码可行吗？

The idea here is to gather a set row with information on different lines and insert this information all together in an html file.

这里的想法是收集一个包含不同行信息的集合行，并将这些信息一起插入到html文件中。

Some perks on the code that would make me very grateful to you:

代码上的一些好处会让我非常感激你：

1) making it work 8P; 2) some kind of way for the user to choose the place he is going to save the file like a normal save window (if not possible, at least let him choose the name of the file in the assigned folder); and 3) making sure the code captures all non empty lines on the row.

1）使其工作8P; 2）某种方式让用户选择他要保存文件的地方就像普通的保存窗口一样（如果不可能，至少让他选择指定文件夹中文件的名称）; 3）确保代码捕获行上的所有非空行。

Many thanks to the community for the attention!

非常感谢社区的关注！

What I have come up to is bellow.

我所得到的是吼叫。

Sub CreateHTML()
'Define your variables.
   Dim iRow As Long
   Dim iStage As Integer
   Dim iCounter As Integer
   Dim iPage As Integer

   'Create an .htm file in the assigned directory.
   Dim sFile As String
   sFile = "J:\GEROC1\Avaliação RO\4) CICLOS\ArquivosExportados" & "\test.html"
   Close

   'Open up the temp HTML file and format the header.
   Open sFile For Output As #1
   Print #1, "<html>"
   Print #1, "<head>"
   Print #1, "<style type=""text/css"">"
   Print #1, "table {font-size: 16px;font-family: Optimum, Helvetica, sans-serif;Border -collapse: collapse}"
   Print #1, "tr {border-bottom: 1px solid #A9A9A9;}"
   Print #1, "td {padding: 4px; margin: 3px; padding-left: 20px; width: 75%; text-align: justify;}"
   Print #1, "th { background-color: #A9A9A9; color: #FFF; font-weight: bold; font-size: 28px; text-align: center;}"
   Print #1, "</style>"
   Print #1, "</head>"
   Print #1, "<body>"
   Print #1, "<table class=""table""><thead><tr class=""firstrow""><th colspan=""2"">Ficha de Risco</th></tr></thead><tbody>"

   'Start on the 2nd row to avoid the header.
   iRow = 2

   'Translate the first column of the table into the first level of the hierarchy.
   Do While WorksheetFunction.CountA(Rows(iRow)) > 0
      If Not IsEmpty(Cells(iRow, 23)) Then
         For iCounter = 1 To iStage
            'Print #1, "</ul>"
            iStage = iStage - 1
         Next iCounter
         Print #1, Cells(iRow, 1).Value
         iPage = iPage + 1
         If iStage < 1 Then
            iStage = iStage + 1
         End If
      End If
   Loop

   'Add ending HTML tags
   Print #1, "</body>"
   Print #1, "</html>"
   Close
End Sub

1 个解决方案

#1

The Code below will create an html file from a table in your excel file. It will write the entire table and search for the number of lines and columns to write it all that is showing.

下面的代码将从excel文件中的表创建一个html文件。它将编写整个表并搜索行和列的数量，以便将其全部显示出来。

Please make sure to edit the html section of the table itself. On my example I wanted to transpose lines and columns and print them one by one.

请确保编辑表格的html部分。在我的例子中，我想要转置行和列并逐个打印它们。

Also, make sure to adjust if you want a table main header with a title. Remember to adjust the colspan.

此外，如果您想要一个带有标题的表主标题，请务必进行调整。记得调整colspan。

This example can be tweaked to write any table into an html file.

可以调整此示例以将任何表写入html文件。

Lastly, I put in a little extra where it will ask you where you want to save the file.

最后，我添加了一些额外的内容，它会询问您要保存文件的位置。

Sub CreateHTML()
  'Define your variables.
  Dim iRow As Long
  Dim tRow As Long
  Dim iStage As Integer
  Dim iCounter As Integer
  Dim iPage As Integer
  Dim lastCol As Integer
  Dim lastRow As Integer

  'Find the last Column Number
   With ActiveSheet
       lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   End With

  'Find the last Column Row
   With ActiveSheet
       lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With

  'Create an .html file in the assigned directory.
  Dim sFile As Variant

   sFile = Application.GetSaveAsFilename(fileFilter:="HTML Files (*.html), *.htm")

   If fileName <> False Then
   SaveWorkbook = fileName
   End If

  'Open up the temp HTML file and format the header.
  Open sFile For Output As #1
  Print #1, "<html>"
  Print #1, "<head>"
  Print #1, "<style type=""text/css"">"
  Print #1, "table {font-size: 16px;font-family: Optimum, Helvetica, sans-serif; border-collapse: collapse}"
  Print #1, "tr {border-bottom: thin solid #A9A9A9;}"
  Print #1, "td {padding: 4px; margin: 3px; padding-left: 20px; width: 75%; text-align: justify;}"
  Print #1, "th { background-color: #A9A9A9; color: #FFF; font-weight: bold; font-size: 28px; text-align: center;}"
  Print #1, "td:first-child { font-weight: bold; width: 25%;}"
  Print #1, "</style>"
  Print #1, "</head>"
  Print #1, "<body>"
  Print #1, "<table class=""table""><thead><tr class=""firstrow""><th colspan=""2"">INSERT TABLE MAIN HEADER HERE - WATCH OUT FOR TABLE COLSPAN</th></tr></thead><tbody>"

  'Translate the first column of the table into the first level of the hierarchy.
  tRow = 1

     'Start on the 2nd row to avoid the header. - iRow=2 / tRow is the table header
     For iRow = 2 To lastRow


     For iCounter = 1 To lastCol

  'EDIT HERE TO CHANGE IT TO YOUR LINKING
  Print #1, "<tr><td>"
  Print #1, Cells(tRow, iCounter).Value
  Print #1, "</td><td>"
  Print #1, Cells(iRow, iCounter).Value
  Print #1, "</td></tr>"

     Next iCounter
  Next iRow

  'Add ending HTML tags
  Print #1, "</body>"
  Print #1, "</html>"
  Close
  End Sub

#1