Link to home
Start Free TrialLog in
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.
Avatar of Mani Jaganathan
Mani Jaganathan
Flag of India image

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
Avatar of lapucca
lapucca

ASKER

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
Avatar of Mani Jaganathan
Mani Jaganathan
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca

ASKER

Works greay, thank you!
Welcome.. Thanks for giving points.