Columns into rows from excel to word

sivapc

New Member
Joined
Jul 17, 2019
Messages
1
Hello helpers,


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


in Excel


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name1[/TD]
[TD]DOB1[/TD]
[TD]Address1[/TD]
[TD]Title1[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]DOB2[/TD]
[TD]Address2[/TD]
[TD]Title2[/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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>

--------------
Code:
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.
    references.jpg
  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

find-related-object-library.jpg



-----------

Result like this:

3e66be4b23ead7dd5352fc6ec6dd3dc9.jpg
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top