none
VBA HTML Format In a For Loop RRS feed

  • Question

  • Hi

    I'm new to excel vba, I have a spreadsheet that sends email notification  to a user where it reads an xml file and sends the data in a table format, currently the table its static, I'm having issues making it dynamic, I know the number of columns but the number of rows are unknown.  and write the data in an html email body. Please assist.


         

    strbody = "<style>table, th, td { border: 1px solid black; padding: 5px;}table {border-spacing: 15px;}</style><table border: 1px solid black><tr><td>" _
              & Cells(1, 1) & "</td><td>" & Cells(1, 2) & "</td><td>" & Cells(1, 3) & "</td><td>" & Cells(1, 4) & "</td><td>" & Cells(1, 5) & "</td><td>" & Cells(1, 6) & "</td><td>" & Cells(1, 7) & "</td></tr><tr><td>" _
              & Cells(2, 1) & "</td><td>" & Cells(2, 2) & "</td><td>" & Cells(2, 3) & "</td><td>" & Cells(2, 4) & "</td><td>" & Cells(2, 5) & "</td><td>" & Cells(2, 6) & "</td><td>" & Cells(2, 7) & "</td></tr><tr><td>" _
              & Cells(3, 1) & "</td><td>" & Cells(3, 2) & "</td><td>" & Cells(3, 3) & "</td><td>" & Cells(3, 4) & "</td><td>" & Cells(3, 5) & "</td><td>" & Cells(3, 6) & "</td><td>" & Cells(3, 7) & "</td></tr><tr><td>" _
              & Cells(4, 1) & "</td><td>" & Cells(4, 2) & "</td><td>" & Cells(4, 3) & "</td><td>" & Cells(4, 4) & "</td><td>" & Cells(4, 5) & "</td><td>" & Cells(4, 6) & "</td><td>" & Cells(4, 7) & "</td></tr><tr><td>" _
              & Cells(5, 1) & "</td><td>" & Cells(5, 2) & "</td><td>" & Cells(5, 3) & "</td><td>" & Cells(5, 4) & "</td><td>" & Cells(5, 5) & "</td><td>" & Cells(5, 6) & "</td><td>" & Cells(5, 7) & "</td></tr><tr><td>" _
              & Cells(6, 1) & "</td><td>" & Cells(6, 2) & "</td><td>" & Cells(6, 3) & "</td><td>" & Cells(6, 4) & "</td><td>" & Cells(6, 5) & "</td><td>" & Cells(6, 6) & "</td><td>" & Cells(6, 7) & "</td></tr><tr><td>" _
              & Cells(7, 1) & "</td><td>" & Cells(7, 2) & "</td><td>" & Cells(7, 3) & "</td><td>" & Cells(7, 4) & "</td><td>" & Cells(7, 5) & "</td><td>" & Cells(7, 6) & "</td><td>" & Cells(7, 7) & "</td></tr></table>"


       

    Tuesday, June 21, 2016 8:59 AM

All replies

  • This should do it.  Assumes cells are empty at end of data block.

    Sub test()
    
      Dim s As String
      Dim ws As Worksheet
      Dim r As Integer, c As Integer
      
      
      s = "<style>table, th, td { border: 1px solid black; padding: 5px;}table {border-spacing: 15px;}</style><table border: 1px solid black>" & vbCrLf
      r = 1
      c = 1
      Set ws = Worksheets("Sheet1")
      While ws.Cells(r, c) <> ""
        s = s & "<tr>"
        While ws.Cells(r, c) <> ""
          s = s & "<td>" & ws.Cells(r, c) & "</td>"
          c = c + 1
        Wend
        s = s & "</tr>" & vbCrLf
        r = r + 1
        c = 1
      Wend
      s = s & "</table>"
      Debug.Print s
    End Sub

    Tuesday, June 21, 2016 12:57 PM