dantheman9
Board Regular
- Joined
- Feb 5, 2011
- Messages
- 175
HI wondering if any could help with the following.
I'm looking to use this great bit of code below;
I issue I have it that, when trying to write time values "mm:ss.0" into the html file, the format is modifed to genral.
I have set the My Formats (marked in red) values to "","","","" as each table as a text heading followed by time values.
I have even tried setting the MyFormats array to: "","mm:ss.0","","" but without any luck (it just prints each row with the above as a text value.
Weridly, when i impored a text file with time values in the same format (but setting the column import to general). the html maker sub seemed to cope fine with it?
I guess the two questions are, can i force excel to change time values into a genral value without changing the formating on how it looks?
If not, can anyone think of a way to set the myFormats array in the html maker to print values after the first row in tables in the mm:ss.0 format?
thanks in advance...this board has been a constant foundtain of great knowledge and helpful hints!!
I'm looking to use this great bit of code below;
Code:
Option Base 1 [COLOR=#008000]' sets first array element to 1, not 0[/COLOR]
[COLOR=#0000ff]Sub[/COLOR] MakeHTM_Basic()
[COLOR=#008000]' Defining a list of variables used in this program[/COLOR]
Dim PageName As String, FirstRow As Integer, LastRow As Integer
Dim FirstCol As Integer, LastCol As Integer, MyBold As Byte
Dim TempStr As String, MyRow As Integer, MyCol As Integer
Dim MyFormats As Variant, Vtype As Integer, MyPageTitle As String
[COLOR=#008000]' MyFormats is an array which can contain formats for numbers
' and dates. Add one element for each table column.
[/COLOR][COLOR=red]MyFormats = Array("", "dd mmm yy", "£#,##0", "0%")
[/COLOR]PageName = "d:\tempm.htm" [COLOR=#008000]'location and name of saved file[/COLOR]
MyPageTitle = Range("A1").Value
FirstRow = 3 [COLOR=#008000]' the range of the worksheet to be[/COLOR]
LastRow = 6 [COLOR=#008000]' converted into an HTML table[/COLOR]
FirstCol = 1
LastCol = 4
If UBound(MyFormats) < (LastCol - FirstCol + 1) Then
MsgBox "The 'MyFormats' array has insufficient elements", vbOKOnly + vbCritical, "MakeHTM macro"
Exit Sub
End If
Open PageName For Output As #1
Print #1, "<html>"
Print #1, "<head>"
Print #1, "<title>Excel to HTML simple table [MeadInKent]</title>"
Print #1, "<style type='text/css'>"
Print #1, "body {font-family: Arial, Helvetica; font-size: 11pt; margin-left: 10; margin-right: 10}"
Print #1, "td {padding: 1pt 3pt 2pt 3pt; border-style: solid; border-width: 1; border-color: #0F5BB9; font-size: 11pt}"
Print #1, "table {border-collapse: collapse; border-width: 1 ; border-style: solid; border-color: #0F5BB9 }"
Print #1, "</style>"
[COLOR=#008000]' The next line refers to a cascading style sheet as an alternative to the <style> instructions
' Print #1, "<link rel='stylesheet' type='text/css' href='mikbasic.css'>"[/COLOR]
Print #1, "</head>"
Print #1, "<body>"
Print #1, "<h1>" & MyPageTitle & "</h1>"
Print #1, "<table>"
For MyRow = FirstRow To LastRow
Print #1, "<tr>"
For MyCol = FirstCol To LastCol
If Cells(MyRow, MyCol).Font.Bold = True Then
MyBold = 1
Else
MyBold = 0
End If
Vtype = 0 [COLOR=#008000]' check whether the cell is numeric
[/COLOR]If IsNumeric(Cells(MyRow, MyCol).Value) Then Vtype = 1
If IsDate(Cells(MyRow, MyCol).Value) Then Vtype = 2
[COLOR=#008000]' if numeric and a format code has been created, apply it
[/COLOR]If Vtype > 0 And MyFormats(MyCol - FirstCol + 1) <> "" Then
TempStr = Format(Cells(MyRow, MyCol).Value, MyFormats(MyCol - FirstCol + 1))
Else
TempStr = Cells(MyRow, MyCol).Value
End If
If MyBold = 1 Then
TempStr = "<b>" & TempStr & "</b>"
End If
If Vtype = 1 Then [COLOR=#008000]' align numbers (not dates) to the right
[/COLOR][COLOR=#008000] [/COLOR]TempStr = "<td align='right'>" & TempStr & "</td>"
Else
TempStr = "<td>" & TempStr & "</td>"
End If
[COLOR=#008000]' if a table cell is blank, add a space
[/COLOR]If TempStr = " <td></td>" Or TempStr = "<td align='right'></td>" Then
TempStr = " <td> </td>"
End If
Print #1, TempStr
Next MyCol
Print #1, "</tr>"
Next MyRow
Print #1, "</table>"
Print #1, "<p>You can search for a name or any detail using [ctrl]+'f'. Press [Home] to<br>"
Print #1, "move to the top of the page. It can be copied and pasted into Excel</p>"
Print #1, "<hr>"
Print #1, "<p><small>Source file: " & ThisWorkbook.Name & " | Page name: " & PageName & " | Created: " & Format(Date, "dd mmm yy") & " | www.MeadInKent.co.uk</small></p>"
Print #1, "</body>"
Print #1, "</html>"
Close #1
MsgBox "The file has been saved as " & PageName, vbOKOnly + vbInformation, "MakeHTML macro"
[COLOR=#0000ff]End Sub[/COLOR]I issue I have it that, when trying to write time values "mm:ss.0" into the html file, the format is modifed to genral.
I have set the My Formats (marked in red) values to "","","","" as each table as a text heading followed by time values.
I have even tried setting the MyFormats array to: "","mm:ss.0","","" but without any luck (it just prints each row with the above as a text value.
Weridly, when i impored a text file with time values in the same format (but setting the column import to general). the html maker sub seemed to cope fine with it?
I guess the two questions are, can i force excel to change time values into a genral value without changing the formating on how it looks?
If not, can anyone think of a way to set the myFormats array in the html maker to print values after the first row in tables in the mm:ss.0 format?
thanks in advance...this board has been a constant foundtain of great knowledge and helpful hints!!
