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
0
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
0
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