Excel to html table vba

Create an HTML File with a Table of Contents based on Cell Data

This code example shows how to take data from a worksheet and create a table of contents in an HTML file. The worksheet should have data in columns A, B, and C that correspond to the first, second, and third levels of the table of contents hierarchy. The HTML file is stored in the same working folder as the active workbook.

Sample code provided by: Holy Macro! Books, Holy Macro! It’s 2,500 Excel VBA Examples

» Print #1, «
• » & Cells(iRow, 1).Value & «» iPage = iPage + 1 If iStage < 1 Then iStage = iStage + 1 End If End If 'Translate the second column of the table into the second level of the hierarchy. If Not IsEmpty(Cells(iRow, 2)) Then For iCounter = 2 To iStage Print #1, "

» Print #1, «
• » & Cells(iRow, 2).Value & «» iPage = iPage + 1 If iStage < 2 Then iStage = iStage + 1 End If End If 'Translate the third column of the table into the third level of the hierarchy. If Not IsEmpty(Cells(iRow, 3)) Then If iStage < 3 Then Print #1, "
  » End If Print #1, «
  • » & Cells(iRow, 3).Value & «» iPage = iPage + 1 If iStage < 3 Then iStage = iStage + 1 End If End If iRow = iRow + 1 Loop 'Add ending HTML tags For iCounter = 2 To iStage Print #1, "

  About the Contributor

  Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.

  Support and feedback

  Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

  Источник

  Convert Excel to HTML Table Tags – VBA Macro Code

  Most of the time, It is easy to represent data in table format. Excel spreadsheet is the one that can create table format data in seconds. But, to make it as HTML page, we need too many tags to be added for each cell. It is not tough, not anymore.

  Here is the easy solution.

  1. Download the Excel at end of this page.
  2. Copy the content to this Excel.
  3. Click the Convert ‘Excel to HTML Table’ button.
  4. Get the HTML code written to file.
  5. Use it in your webpage.

  If you are already a VBA gig, then just use the free code available in this page, that just does the same.

  VBA Macro Code – Convert Excel Table to HTML

  This VBA code just loops thru all the rows & columns in the Excel table in the first sheet.

  First Row Header: Instead of checking for font style, code in the Excel file attached below, converts the first row as header.

  Table Width: Each column width in Excel is set to width of each column in HTML Table.

  Sub Convert_Excel_Table_To_HTML() Dim htmlTable As String, iSh As Worksheet Dim iRow As Double, iCol As Double, iVal As String Dim iRowCount As Long, iColCount As Integer, iWidth(100) As Double iRow = 1 iCol = 1 'Get the number of rows and columns Set iSh = ThisWorkbook.Sheets(1) While iSh.Cells(iRow, 1).Value <> "" iRow = iRow + 1 Wend While iSh.Cells(1, iCol).Value <> "" iWidth(iCol) = iSh.Columns(iCol).Width iCol = iCol + 1 Wend iRowCount = iRow - 1 iColCount = iCol - 1 htmlTable = "" For iRow = 1 To iRowCount 'TR htmlTable = htmlTable & "" For iCol = 1 To iColCount 'TD or TH Tags iVal = iSh.Cells(iRow, iCol).Value 'bold text becomes table heading If iRow = 1 Then htmlTable = htmlTable & "" Else htmlTable = htmlTable & "" End If Next iCol htmlTable = htmlTable & "" Next iRow htmlTable = htmlTable & "
  " & iVal & "
  " & iVal & "
  " htmlTable = InputBox("Converted HTML Code", , htmlTable) End Sub

  If the data also has any data to be bold then consider changing the code accordingly.

  Download Excel to HTML Table Converter

  This converter app has all the functionalities explained above as same. Except for this one.

  Bold Data: If the row is Bold, then it is considered as Table header. If it is not bold, then it is considered as table cell data.

  Just download this Excel app, Enable security options for macro & content when asked for, then copy paste your data to the first sheet & Click on convert button.

  This app only converts spreadsheet into a plain HTML table. The formatting within the table are ignored.

  Источник

  Convert Excel Range into HTML Table through VBA

  As a VBA developer you may frequently come cross with a requirement of sending an email through Outlook along with an HTML table inside email body. Adding an HTML table in email body is very simple task if you do manually, just copy the range in Excel and paste it on email. It is little difficult in VBA to copy Excel range into email body. To convert Excel range into HTML table, you need to play with HTML tags. Below is the code which can make your life easy

  

  Excel VBA Code to Convert Excel Range to HTML using VBA:

  'Following function converts Excel range to HTML table
  Public Function ConvertRangeToHTMLTable(rInput As Range) As String
  'Declare variables 
  Dim rRow As Range
  Dim rCell As Range
  Dim strReturn As String
  'Define table format and font 
  strReturn 
  'Loop through each row in the range
  For Each rRow In rInput.Rows 
  'Start new html row
  strReturn = strReturn & " "
  For Each rCell In rRow.Cells
  'If it is row 1 then it is header row that need to be bold 
  If rCell.Row = 1 Then
  strReturn = strReturn & "" & rCell.Text & "
  " 
  Else 
  strReturn = strReturn & "" & rCell.Text & "" 
  End If 
  Next rCell 
  'End a row 
  strReturn = strReturn & "" 
  Next rRow 
  'Close the font tag 
  strReturn = strReturn & "" 
  'Return html format 
  ConvertRangeToHTMLTable = strReturn 
  End Function

  Convert Excel Range into HTML Table through VBA

  Below Excel Macro creates an email in outlook and call the above function to add Excel Range as HTML Table in Email Body

  'This function creates an email in Outlook and call the ConvertRangeToHTMLTable function 
  to add Excel range as HTML table in Email body
  Sub CreateOutlookEmail() 
  'Declare variable 
  Dim objMail As Outlook.MailItem 
  'Create new Outlook email object 
  Set objMail = Outlook.CreateItem(olMailItem) 
  'Assign To
  objMail.To /cdn-cgi/l/email-protection" data-cfemail="f88c9d8b8cb89f95999194d69b9795">[email protected]"
  'Assign Cc
  objMail.CC /cdn-cgi/l/email-protection" data-cfemail="691d0c1a1d5b290e04080005470a0604">[email protected]" 
  'Assign Subject
  objMail.Subject = "Test Email"
  'Define HTML email body
  'Tip: Here i have converted range A1:F20 of Sheet1 in HTML table, you can modify the
  same as per your requirement
  objMail.HTMLBody = "
  This is a test email
  " & ConvertRangeToHTMLTable(Sheet1.Range("A1:F20"))
  objMail.HTMLBody = "
  This is a test email
  " & ConvertRangeToHTMLTable(Sheet1.Range("A1:F20")) 
  'Show the email to User
  objMail.Display
  'Send the email 
  'objMail.Send
  'Close the object
  Set objMail = Nothing
  End Sub

  It is worth to mention that you must have MS Outlook installed in your system to use this code and you also need to add Outlook reference (Microsoft Outlook XX.X Object Library) in Excel VBA from Menu Bar (Tools>References…). You can read this post to see how to add Outlook Object Library in Excel Reference.

  

  To use this code in your Excel file, follow below steps:

  1. Open an Excel file
  2. Press Alt+F11
  3. Insert a Module (Insert>Module) from menu bar
  4. Paste the code in the module
  5. Now add a shape in Excel sheet
  6. Give a name to the shape like ‘Create Email’
  7. Right click on the shape and select ‘Assign Macro…’

  

  8. Select ‘CreateOutlookEmail’ from the list and click on ‘Ok’ button

  

  

  Hope you liked this article !!

  Subscribe our blog for new amazing excel tricks.

  Click to below for some more interesting tricks and learning:

  Download Practice File

  You can also practice this through our practice files. Click on the below link to download the practice file.

  Recommended Articles

  Please leave your valuable comments in Comments section:

  Excel VBA Course : Beginners to Advanced

  We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

  This course is going to help you to excel your skills in Excel VBA with our real time case studies.

  Lets get connected and start learning now. Click here to Enroll.

  Similar Posts

  

  VBA to Open Workbook in Excel

  How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…

  

  VBA Code to Count Color Cells with Conditional Formatting

  VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…

  

  How to Insert Symbol in Excel?

  How to Insert Symbol in Excel? Have you ever faced the challenge of using special character symbols in number formatting or customized number formatting? It is easy to insert any symbol in numbers i.e Delta…

  

  VBA Code to Change Sheet Color

  What is the Usage of sheet color in Excel? When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give…

  

  Find Duplicate Files In Excel Using VBA

  How to Find Duplicate Files In excel using VBA? Yesterday I was working on my computer and cleaning the drives to make some more space. I was surprised to see so many files saved at…

  

  How to Hide Sheet in Excel with or without VBA?

  Video: How to Hide Worksheet in Excel? Hide Sheet in Excel When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any…

  11 Comments

  Thanks for your response Sandeep. As we checked rInput is already declared as input parameter of ConvertRangeToHTMLTable function; hence need not to be declared separately.

  Hello – I’ve been working with this but I can’t figure out how to change the header row to bold. I’m using the function for 3 seperate tables on the same sheet and using If rCell.Row = 1 this is the first row in the sheet, not in the range? How do I select the first row in the range?

  Hi Jason, Can you try to replace If rCell.Row = 1 Then with If rCell.Row — rInput.Row = 0 Then . Regards
  ExcelSirJi Team

  hello, i copy and pasted your code. in the email, i got a super small table with no code. the problem is rCell.Text is not displaying the text. When i replace rCell.Text with something else, like “hello”, it works perfectly.

  Hi Gordon, We are not able to reproduce the error of small text. The error seems to be more related to data that you are trying to convert in HTML Table. Can you try the same code with any other Excel file? Regards
  ExcelSirJi

  Hello – this macro works perfectly thank you! However there are empty rows in my range that I am converting to a table, but when I do the macro the table does not have blank rows. Is there a way I can leave the blank cells as blank rows in the output table?

  Public Function ConvertRangeToHTMLTable(rInput As Range) As String 'Declare variables Dim rRow As Range Dim rCell As Range Dim strReturn As String 'Define table format and font strReturn = " " 'Loop through each row in the range For Each rRow In rInput.Rows 'Start new html row strReturn = strReturn & " " For Each rCell In rRow.Cells 'If it is row 1 then it is header row that need to be bold If rCell.Row = 1 Then strReturn = strReturn & "" & rCell.Text & "" Else strReturn = strReturn & "" & rCell.Text & "" End If Next rCell 'End a row strReturn = strReturn & "" Next rRow 'Close the font tag strReturn = strReturn & "" 'Return html format ConvertRangeToHTMLTable = strReturn End Function

  Hello, great, that’s exactly what I was looking for.
  I added a small function for wrap text in the cells.
  BR Karl-Heinz
  Function WrapText(strValue As String) As String
  ‘*Declare variables
  Dim strtxt As String, i As Long, MyValues
  ‘*Split value from Cell with chr(10) to get more lines
  MyValues = Split(strValue, Chr(10))
  ‘*Create temp string with breaks
  For i = LBound(MyValues) To UBound(MyValues)
  strtxt = strtxt & IIf(Len(strtxt) > 0, “”, “”) & MyValues(i)
  Next i
  WrapText = strtxt
  End Function Public Function ConvertRangeToHTMLTable(rInput As Range) As String
  ‘*Declare variables
  Dim rRow As Range
  Dim rCell As Range
  Dim strReturn As String
  Dim strValue As String ‘*Define table format and font
  strReturn = ” ”
  ‘*Loop through each row in the range
  For Each rRow In rInput.Rows
  ‘*Start new html row
  strReturn = strReturn & ” ”
  For Each rCell In rRow.Cells
  ‘*If it is row 1 then it is header row that need to be bold
  If rCell.Row = 1 Then
  ‘*Check if wrap-text is available
  If InStr(1, rCell.Value, Chr(10), vbBinaryCompare) > 0 Then strValue = WrapText(rCell.Value) Else strValue = rCell.Value
  strReturn = strReturn & “” & strValue & “”
  Else
  If InStr(1, rCell.Value, Chr(10), vbBinaryCompare) > 0 Then strValue = WrapText(rCell.Value) Else strValue = rCell.Value
  strReturn = strReturn & “” & strValue & “”
  End If
  Next rCell
  ‘*End a row
  strReturn = strReturn & “”
  Next rRow
  ‘*Close the font tag
  strReturn = strReturn & “”
  ‘*Return html format
  ConvertRangeToHTMLTable = strReturn
  End Function

  Источник

  Читайте также:  Второе вхождение подстроки python