Avatar of lapucca
lapucca asked on

Is there a quick way to get data from 2 columns in a excel sheet to a html table rows?

Hi, in our cms I have a html table with 4 columns.  I have a Excel with 2 columns of data that I would like to be able to add these rows in Excel to the html table.  Our cms (Sitecore), provides RichText editor for this html table but I'm able to get the HTML source form it.  So I can work with this in HTML and then copy that back to our CMS system.

What would be the easiest way for me to add these Excel rows to this Html table?  Thank you.
HTML

Avatar of undefined
Last Comment
Mani Jaganathan

8/22/2022 - Mon
Mani Jaganathan

Try below.

You just copy the data from excel and pasted into the word document as a table and run the below macro which converts the word table to HTML coding and you copied the rows and merged into your CMS table.

Sub CodeTable()
'
' Macro to code a current table in Word in HTML
'

    Dim tableArray(500, 500) As String
    Dim tableFormat(500, 500) As String
    
    '*** count the tables
    totalTables = ActiveDocument.Tables.Count
    
    '*** loop through the tables
    For x = 1 To totalTables
        'MsgBox (totalTables & ", " & ActiveDocument.Tables.Count)
        rcount = ActiveDocument.Tables(1).Rows.Count
        cCount = ActiveDocument.Tables(1).Columns.Count
    
        'MsgBox (rcount & ", " & ccount)
        
        '*** load array with table contents
        For r = 1 To rcount
            For c = 1 To cCount
                tableArray(r, c) = Left(ActiveDocument.Tables(1).Cell(Row:=r, Column:=c).Range, Len(ActiveDocument.Tables(1).Cell(Row:=r, Column:=c).Range) - 2)
                tableFormat(r, c) = ActiveDocument.Tables(1).Cell(Row:=r, Column:=c).Range.ParagraphFormat.Alignment
            Next c
        Next r
        
        '*** highlight the table
        ActiveDocument.Tables(1).Select
        
        '*** delete the table
        ActiveDocument.Tables(1).Delete
        
        '*** write open table tag
        Selection.TypeText "<table border=" & Chr(34) & "1" & Chr(34) & " cellspacing=""0"" cellpadding=""0""><tr>"
        
        '*** overwrite with contents and html code
        For r = 1 To rcount
            For c = 1 To cCount
                Selection.TypeText "<td align=" & Chr(34)
                If tableFormat(r, c) = 0 Then Selection.TypeText "left"
                If tableFormat(r, c) = 1 Then Selection.TypeText "center"
                If tableFormat(r, c) = 2 Then Selection.TypeText "right"
                Selection.TypeText Chr(34) & ">&nbsp;" & tableArray(r, c) & "</td>"
            Next c
            
            '*** end the row and start another
            If r < rcount Then
                Selection.TypeText "</tr>"
                Selection.TypeText Chr(13)
                Selection.TypeText "<tr>"
            End If
        Next r
        
        '*** write end table tag
        Selection.TypeText "</tr></table>"
        Selection.TypeText Chr(13)
        
    Next x
    
    Selection.Find.Execute "^p", , , False, , , True, wdFindContinue, , " ", wdReplaceAll
       
End Sub

Open in new window


Hope this helps
ASKER
lapucca

Hi, I copied the 3 columns of data by clicking on the top of the 3 columns and then right click in the Word but I only get the Paste option.  There is no option to paste as table.  I tried creating a table first then paste into that but still not work.  Can you provide some steps on how I can do that?  Thank you.
ASKER CERTIFIED SOLUTION
Mani Jaganathan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
lapucca

Works greay, thank you!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mani Jaganathan

Welcome.. Thanks for giving points.