Use Excel VBA to add multiple tables to end of Word document

leniusc · Jul 11, 2008

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing {mso-style-priority:1; mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> I have an Excel spreadsheet of data that I would like to export into tables in a new word document.
The code below (without the marked section) does this, but I can’t figure out how to create a second table after the first one.
<o

> </o

>
Eventually I will use a For…Next loop to do this many times, but I’m trying to make the problem smaller by just making a second table.
<o

> </o

>
Thank you in advance for any help.
-Chris
<o

> </o

>
Sub CreateNewWordDoc()
' add a reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTable As Word.Table
Dim xText
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdApp.Visible = True
'Create table
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
xText = Worksheets("Sheet1").[A1].Value
With wrdTable
With .Cell(1, 1).Range
.InsertAfter xText
End With
xText = Worksheets("Sheet1").[A2].Value
With .Cell(1, 2).Range
.InsertAfter xText
End With
End With
'--------------problematic section begins here?--------------
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
xText = Worksheets("Sheet1").[B1].Value
With wrdTable
With .Cell(1, 1).Range
.InsertAfter xText
End With
xText = Worksheets("Sheet1").[B2].Value
With .Cell(1, 2).Range
.InsertAfter xText
End With
End With
'----------------------------
'clean up
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub
<o

> </o

>

Cindy Ellis · Jul 11, 2008

Hi leniusc,
Welcome to the Board!
Your code just needed some minor tweaking to work...basically you needed to collapse the range to the end, then (assuming that you want a paragraph between tables) insert a paragraph after the table, and again collapse the range to the end before adding the second (and subsequent) tables:

Code:

Sub CreateNewWordDoc()
' add a reference to the Word-library
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdTable As Word.Table
    Dim xText
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add ' create a new document
    Set wrdRange = wrdDoc.Range
    wrdApp.Visible = True
    'Create table
    Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
    xText = Worksheets("Sheet1").[A1].Value
    With wrdTable
        With .Cell(1, 1).Range
            .InsertAfter xText
        End With
        xText = Worksheets("Sheet1").[A2].Value
        With .Cell(1, 2).Range
            .InsertAfter xText
        End With
    End With
    
    '------new code-----------
    wrdRange.Collapse Direction:=wdCollapseEnd
    With wrdRange
        .Collapse Direction:=wdCollapseEnd
        .InsertParagraphAfter
        .Collapse Direction:=wdCollapseEnd
    End With
        
    '--------------no longer a problem--------------
    Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
    xText = Worksheets("Sheet1").[B1].Value
    With wrdTable
        With .Cell(1, 1).Range
            .InsertAfter xText
        End With
        xText = Worksheets("Sheet1").[B2].Value
        With .Cell(1, 2).Range
            .InsertAfter xText
        End With
    End With
    '----------------------------
    'clean up
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

Assuming that your tables are bigger than 2 columns by 1 row in real life, you might also consider using copy/paste an entire range from Excel to Word within your macro...it seems a bit simpler (to me) than exporting cell-by-cell.

Hope this helps,
Cindy

leniusc · Jul 12, 2008

Cindy,
Thank you SO MUCH for your help in this. I had been searching this and other message boards for over a week trying to figure out how to make my new addition to the word document appear at the end. You solved it for me.
My tables will only be 2 by 1, but those two cells are not adjacent in Excel. That's why I haven't been using the copy/paste procedure. Also, I read at http://peltiertech.com/Excel/XL_PPT.html that pasting from Excel into Word causes a complete copy of the workbook to be made. Since I'll be doing this procedure hundreds of times in the macro, I thought this would make a huge word file result. Am I misinterpreting this? Does pasting from Excel to Word actually only create a "normal sized" table?
Two followup questions on your code:
1. I wondered if one line was redundant. It seems that your code collapsed the range two times in a row. Removing one of these lines seems to have no effect on the result, but I just wanted to make sure it was OK.
2. When I used your code more than once (to create a third table), the third table ended up inside of the first cell of the second table. To fix this I added the first line in the code below and it seems to work. Did I do this correctly?
Thank you again for your help. You answered what dozens of hours of searching and experimenting couldn't.
-Chris

Code:

'Move to end
Set wrdRange = wrdDoc.Content
'redundant line?  wrdRange.Collapse Direction:=wdCollapseEnd
With wrdRange
    .Collapse Direction:=wdCollapseEnd
    .InsertParagraphAfter
    .Collapse Direction:=wdCollapseEnd
End With

Cindy Ellis · Jul 12, 2008

Chris,
Glad that you're close to a solution. I SHOULD have tested it out with multiple tables knowing that you wanted to do that, but (oops) stopped at just the 2 that were in your example.
I was experimenting, too, so yes, you're correct that there's an extraneous wdCollapseEnd.
Regarding copy/paste from Excel to Word...if you define the range that will be copied, and paste cells into Word, Excel will only copy/paste that range, BUT if your range included any hidden rows or columns, those get carried along for the ride as hidden text, which really can be a problem. Since the cells you're exporting aren't adjacent, your chosen approach is best.

Here's one approach to a loop...you will need to modify it with the correct column and row references (since they're adjacent in this example, but not in real life)...but it you have data in the columns A and B, rows 1 through 10, this will create a Word doc with 10 tables, each 1 row by 2 columns:

Code:

Sub CreateNewWordDoc()
' add a reference to the Word-library
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdTable As Word.Table
    Dim xText, xText2
    Dim i As Integer
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add ' create a new document
    Set wrdRange = wrdDoc.Range
    wrdApp.Visible = True
    
    For i = 1 To 10
        xText = Worksheets("Sheet1").Cells(i, 1).Value
        xText2 = Worksheets("Sheet1").Cells(i, 2).Value
        
        Set wrdRange = wrdDoc.Range
        With wrdRange
            .Collapse Direction:=wdCollapseEnd
            .InsertParagraphAfter
            .Collapse Direction:=wdCollapseEnd
        End With
        
    'Create table
        Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
        
        With wrdTable
            With .Cell(i, 1).Range
                .InsertAfter xText
            End With
            xText = Worksheets("Sheet1").[a2].Value
            With .Cell(i, 2).Range
                .InsertAfter xText2
            End With
        End With
    Next i
    '------new code-----------

    'clean up
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

Hope this helps,
Cindy

leniusc · Sep 13, 2008

Cindy,
Thank you for your help in solving this issue. I know that this is a (VERY) belated reply, but your solutions worked very well and I'm very grateful. What a nice thing for beginners like me to have experts like you willing to help us.
Thanks again,
Chris

Cindy Ellis · Sep 13, 2008

Hello Chris,
Thanks for letting me know

...I had wondered how it turned out. You can learn a lot from this site. Pretty soon you'll see a question you know the answer to, and you'll be the "expert" helping someone else.
Best regards,
Cindy

Dossfm0q · Mar 9, 2009

hI
Dim wrdDoc As Word.Document
Dim wrdTable As Word.Table
The above reference to the Word-library are not working the VBA highlighted and show error as " Compile error: Use-defined not defined"

by Excel Macro
1- how to convert added table to Text.
2-and how to covert table already created on word doc.

Set wrdTable = wrdDoc.Tables(1).ConvertToText(Separator:=wdSeparateByTabs, NestedTables:= True)
thanks

Cindy Ellis · Mar 9, 2009

Hello, and Welcome to the Board!
The first step in making the macro work is to add a Reference to the Microsoft Word Library from within Excel. To do this, in Excel's Visual Basic editor, select the Tools menu, then click References, then scroll down until you see Microsoft Word 12.0 Object Library (or whatever version of Microsoft Word you have). Select it, (this should add check mark next to it), then click OK. Now when you run the macro, Excel will be able to access the appropriate Word-related objects.
I don't really understand the rest of your question...it seems to be a list of statements rather than questions...so I don't know what you need help with. Try adding the link to Word as described above, and see if the macro does what you need it to do.
Hope this helps,
Cindy

Dossfm0q · Mar 10, 2009

Good Day
I copy and paste thread into Excel 'Error Message tel_ me : " Compile error: Use-defined not defined"
Dim wrdApp As Word.Application
please see the end of thead i add witch I need

Code:

Sub CreateNewWordDoc()
' add a reference to the Word-library
'======================================
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTable As Word.Table
'Error Message tel_ me : " Compile error: Use-defined not defined"
' ======================================
Dim xText, xText2
Dim i As Integer
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdApp.Visible = True
 
For i = 1 To 10
xText = Worksheets("Sheet1").Cells(i, 1).Value
xText2 = Worksheets("Sheet1").Cells(i, 2).Value
 
Set wrdRange = wrdDoc.Range
With wrdRange
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
.Collapse Direction:=wdCollapseEnd
End With
 
'Create table
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=2)
 
 
 
With wrdTable
With .Cell(i, 1).Range
.InsertAfter xText
End With
xText = Worksheets("Sheet1").[a2].Value
With .Cell(i, 2).Range
.InsertAfter xText2
End With
End With
Next i
'==================================================================
' Please see thread below After copying form Exel to word I need to convert Each table add ed to wor to text like below code
Dim tableTemp As Table
Dim rngTemp As Range
Set tableTemp = ActiveDocument.Tables(1)
Set rngTemp = tableTemp.ConvertToText(Separator:=wdSeparateByParagraphs)
'==================================================================
'------new code-----------
'clean up
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

Thank you

Dossfm0q · Mar 10, 2009

this is a message I got

http://picasaweb.google.com/dossfm0q/Mycollect#5311427314588585330

thank you

Use Excel VBA to add multiple tables to end of Word document

leniusc

New Member

Excel Facts

Cindy Ellis

MrExcel MVP

leniusc

New Member

Cindy Ellis

MrExcel MVP

leniusc

New Member

Cindy Ellis

MrExcel MVP

Dossfm0q

Banned User

Cindy Ellis

MrExcel MVP

Dossfm0q

Banned User

Dossfm0q

Banned User

Similar threads

Forum statistics

Share this page

Use Excel VBA to add multiple tables to end of Word document

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Banned User

MrExcel MVP

Banned User

Banned User

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock