Columns into rows from excel to word

[sivapc]

Hello helpers,


I'm trying to convert a column of information into row to word via excel. Example as below


in Excel

Name1	DOB1	Address1	Title1
Name2	DOB2	Address2	Title2

<tbody>
</tbody>

in Word in should look like


Name1,
DOB1,
Address1,
Title1


Name2,
DOB2,
Address2,
Title2




I tried transpose function in excel itself, but it turned out hideous. Any help would be appreciated

 

[DanteAmor]

I guess your data in excel is like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DOB</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ADDRESS</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TITLE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Name1</td><td >DOB1</td><td >Address1</td><td >Title1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Name2</td><td >DOB2</td><td >Address2</td><td >Title2</td></tr></table>

--------------

Sub from_excel_to_word()
    Dim objWord As Word.Application
    Dim cad As String, sh As Worksheet
    
    Set sh = Sheets("test")
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        cad = cad & sh.Cells(i, "A").Value & "," & Chr(10)
        cad = cad & sh.Cells(i, "B").Value & "," & Chr(10)
        cad = cad & sh.Cells(i, "C").Value & "," & Chr(10)
        cad = cad & sh.Cells(i, "D").Value & "," & Chr(10)
        cad = cad & Chr(10) & Chr(10)
    Next
        
    Set objWord = CreateObject("Word.Application")
    objWord.Documents.Add
    objWord.Visible = True
    objWord.ActiveDocument.Content.FormattedText.Text = cad
    Set objWord = Nothing
End Sub

-----------------
How to Add an Object Library Reference in VBA

1. In VBA editor window, click the “Tools” button in the menu bar.
2. Then, from the drop down list, select the “References” option.
   
3. Subsequently, the “References – Project 1” dialog box will display.
4. In this dialog box, you can pull the scrolling bar down until you locate what you want, such as “Microsoft Excel nn.0 Object Library”.

Note: nn is your version

-----------

Result like this: