ब्लॉग कोशिश कर रहा है بلاگز کی ک*Trying Blogs

[DocAElstein]

Simple HTML code string for a header table incl. background colour

We want a string to send as the .htmlbody in our code which will produce something of this form:

Machine EQ.ID

Manufacture

Model

Description

Serial Number

Weekly Date of Service

Weekly Next Service

Monthly Date of Service

Monthly Next Service

Quarterly Date of Service

Quarterly Next Service

Worksheet: Equipment PM
Lets say we put this in a sting variable, strHeader

The current String, held in variable strHTML already contains the data to be sent for that table, held in variable, ProTble.

     Dim strHTML As String: Let strHTML = ProTble

So we simply can add our header then finally via
__Let strHTML = strHeader + ProTble

From the last post, we have the following information for column width and background color
80
207 #D8D8D8
113
143 #D8D8D8
158
115 #92D050
97 #92D050
154 yellow
154 yellow
161 #D8D8D8
161 #D8D8D8

Correspondingly from the Excel range we see the header text and so have
80 EQ.ID
207 #D8D8D8 Manufacture
113 Model
143 #D8D8D8 Description
158 Serial Number
115 #92D050 Weekly
97 #92D050 Weekly Nxt
154 yellow Monthly
154 yellow Monthly Nxt
161 #D8D8D8 Quarterly
161 #D8D8D8 Quarterly Nxt



Here is a basic HTML code string for the required table, ( The vbCrLf :are not needed and are ignored by any interpreting of the HTML string. We have them for convenience of viewing the code in an editor such as a simple Notepad text file. Also the _ _ _ are just for VBA code splitting of code lines, for convenience of viewing in VB Editors )

' Table start column info
Let strHeader  = "<table width=1623>" & vbCrLf & _
"<col width=80>" & vbCrLf & _
"<col width=207>" & vbCrLf & _
"<col width=113>" & vbCrLf & _
"<col width=143>" & vbCrLf & _
"<col width=158>" & vbCrLf & _
"<col width=115>" & vbCrLf & _
"<col width=97>" & vbCrLf & _
"<col width=154>" & vbCrLf & _
"<col width=154>" & vbCrLf & _
"<col width=161>" & vbCrLf & _
"<col width=161>" & vbCrLf & vbCrLf
' single header row
Let strHeader  =  strHeader  & _
"<tr height=17>" & vbCrLf & _
"<td> EQ.ID </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Manufacture </td>" & vbCrLf & _
"<td> Model </td>" & vbCrLf & _
"<td style=""color:Black; background:#D8D8D8""> Description </td>" & vbCrLf & _
"<td> Serial Number </td>" & vbCrLf & _
"<td style=""background:#92D050""> Weekly </td>" & vbCrLf & _
"<td style=""background:#92D050""> Weekly Nxt </td>" & vbCrLf & _
"<td style=""background:yellow""> Monthly </td>" & vbCrLf & _
"<td style=""background:yellow""> Monthly Nxt </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Quarterly </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Quarterly Nxt </td>" & vbCrLf & _
"</tr>"

_.______

Results:
The initial results suggest that a bit of adjustments are necessary to get a convenient total width
InitialHeader gmail.JPG : https://imgur.com/D3xsfnu
InitialHeader t-online.JPG : https://imgur.com/QK68KcS


One possible simple way to do this adjustment is included in the final initial test code:

Rem 4.5) header row as HTML table
Dim strHeader As String, Adj As Double: Let Adj = 0.5
' 4.5a) Table start column info
Let strHeader = "<table width=" & 1623 * Adj & ">" & vbCrLf & _
"<col width=" & 80 * Adj & ">" & vbCrLf & _
"<col width=" & 207 * Adj & ">" & vbCrLf & _
"<col width=" & 113 * Adj & ">" & vbCrLf & _
"<col width=" & 143 * Adj & ">" & vbCrLf & _
"<col width=" & 158 * Adj & ">" & vbCrLf & _
"<col width=" & 115 * Adj & ">" & vbCrLf & _
"<col width=" & 97 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & vbCrLf
' 4.5b) Single header row
Let strHeader = strHeader & _
"<tr height=17>" & vbCrLf & _
"<td>EQ. ID</td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Manufacture </td>" & vbCrLf & _
"<td>Model</td>" & vbCrLf & _
"<td style=""color:Black;background:#D8D8D8"">Description</td>" & vbCrLf & _
"<td>Serial Number</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly Next</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly Next</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly Next</td>" & vbCrLf & _
"</tr>"

The following results are then obtained
HalfWidthHeader t-online.JPG : https://imgur.com/Gup4W9t
HalfWidthHeader gmail.JPG : https://imgur.com/PpWtDKM