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 · Mar 10, 2009

Did you follow the steps I listed to link to the Word Object Model from within Excel VBA?
If you have done that and it still won't compile, then I really don't know how to help you.

Once you do get it to compile, there may still be errors, since it was created in Office2000. If you are running Office2007, the word document object model has changed, and you will likely run into problems further into the macro. If that is the case, I will try to help, but my time is VERY limited at the moment.

Regards,
Cindy

Dossfm0q · Mar 11, 2009

Its working very will
Thank you alot

Guntherm · Apr 10, 2009

Cindy Ellis said:
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

I like the work you did, but I keep getting variable not defined myself.

1. I have the referance to word in vba
2. It is highlighting Set wrdRange = wrdDoc.Range (wrdRange)

I see it is not dimmed anywhere

If I add Dim wrdrange as Range then I get typemismatch on the Set wrdRange line.

What am I missing?

krinsn · Jan 11, 2012

Hi Cindy,

I saw your thread on adding data from excel to table in word. Need your help on similar issue.I am trying to move/paste an image in excel to a table in word.

The images in excel have a unique name(Picture 1, 2,3)(Successful)
There needs to be a table created with 1 row and 3 columns in word(successful)
Each image should go to each column in word .(Image 1->column 1,image 2->column2 and image 3->column3)(Failing)

I tried the following code with help from your previous thread :

Sub table()
Dim appWD As Word.Application
Dim wrddoc As Word.Document
Dim wrdTable As Word.table
Set appWD = CreateObject("Word.Application")
Set wrddoc = appWD.Documents.Add
Set wrdRange = wrddoc.Range
appWD.Visible = True
'appWD.Documents.Add'
Set wrdTable = wrddoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=3)
wrdTable.Range.Text = Sheets("RxLevel").Shapes("Picture 1")->TROUBLE HERE(Pointing a image to text seems wrong but i dont get any other option)
-Normal insertion to word doc'-This works
Sheets("RxLevel").Shapes("Picture 2").Select
Selection.Copy
appWD.Selection.Paste
Application.CutCopyMode = False
'Set appWD = Nothing'
' Save the new document with a sequential file name
appWD.ActiveDocument.SaveAs Filename:="File"
'appWD.ActiveDocument.Close'
'appWD.Quit'
End Sub

Thanks in Advance

Regards
Krishnan

Cindy Ellis · Jan 11, 2012

Hi,
I think you need to use copy/paste, but you don't need to select or activate the sheet first. See if this does what you need:

Code:

Sub MakeTable()
Dim appWD As Word.Application
Dim wrddoc As Word.Document
Dim wrdTable As Word.table
Dim wrdRange As Word.Range
Dim rngTable As Word.Range
Dim i As Integer

Set appWD = CreateObject("Word.Application")
Set wrddoc = appWD.Documents.Add
Set wrdRange = wrddoc.Range

appWD.Visible = True

Set wrdTable = wrddoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=3)
For i = 1 To 3
Set rngTable = wrdTable.Cell(1, i).Range
Sheets("RxLevel").Shapes("Picture " & i).Copy
rngTable.Paste
Application.CutCopyMode = False
Next i
'Save the new document
appWD.ActiveDocument.Save 'This will prompt for a new filename
appWD.ActiveDocument.Close
appWD.Quit
Set appWD = Nothing '
End Sub

Note that the previous "ActiveDocument.SaveAs filename:="File" probably doesn't work as intended. You mentioned a "sequential file" which is a specific type of text file that Excel can open, read from, and write to, but a sequential file is text...I don't know of any way to put a picture into it. My guess is that your intention was to increment a number at the end of the filename, which you can do by setting the value of a variable (for instance savefilename) before you get to the ActiveDocument.Save part.

Hope that helps,

krinsn · Jan 12, 2012

Hi Cindy ,

Your code is working well,I however have a small issue when i try the following:

I have to insert an image in the word document followed by this table and then insert 3 images into it.

1.Word document getting created(Success)
2.First image is getting pasted in word(Success)
3.Table getting created,(but the image in step 2 is deleted)-->PROBLEM
4.Images get inserted into the table(Success)

Code:

Sub MakeTable()
Dim appWD As Word.Application
Dim wrddoc As Word.Document
Dim wrdTable As Word.Table
Dim wrdRange As Word.Range
Dim rngTable As Word.Range
Dim i As Integer

Set appWD = CreateObject("Word.Application")
Set wrddoc = appWD.Documents.Add
Set wrdRange = wrddoc.Range

appWD.Visible = True
Sheets("Sheet1").Shapes("Picture 1").Copy
appWD.Selection.Paste
Set wrdTable = wrddoc.Tables.Add(Range:=wrdRange, NumRows:=1, NumColumns:=3)
For i = 1 To 3
Set rngTable = wrdTable.Cell(1, i).Range
Sheets("Sheet1").Shapes("Picture " & i).Copy
rngTable.Paste
Application.CutCopyMode = False
Next i
'Save the new document
appWD.ActiveDocument.Save 'This will prompt for a new filename
appWD.ActiveDocument.Close
appWD.Quit
Set appWD = Nothing '
End Sub

Awaiting your reply

Regards

Krishnan

Cindy Ellis · Jan 12, 2012

Hello Krishnan,
The following code has a few added statement to move the insertion point to a new line in Word, and to add the table at the insertion point rather than taking the "whole range" of the document, which was over-writing the picture.

Code:

Sub MakeTable()
Dim appWD As Word.Application
Dim wrddoc As Word.Document
Dim wrdTable As Word.table
Dim wrdRange As Word.Range
Dim rngTable As Word.Range
Dim i As Integer

Set appWD = CreateObject("Word.Application")
Set wrddoc = appWD.Documents.Add
Set wrdRange = wrddoc.Range

appWD.Visible = True
Sheets("Sheet1").Shapes("Picture 1").Copy
With appWD.Selection
    .Paste
    .MoveRight Unit:=wdCharacter, Count:=1
    .TypeParagraph
    Set wrdTable = wrddoc.Tables.Add(Range:=.Range, NumRows:=1, NumColumns:=3)
End With
For i = 1 To 3
    Set rngTable = wrdTable.Cell(1, i).Range
    Sheets("Sheet1").Shapes("Picture " & i).Copy
    rngTable.Paste
    Application.CutCopyMode = False
Next i
'Save the new document
appWD.ActiveDocument.Save 'This will prompt for a new filename
appWD.ActiveDocument.Close
appWD.Quit
Set appWD = Nothing '
End Sub

Hope that helps,

krinsn · Jan 13, 2012

Hi Cindy

Thanks a lot for your help its working now

It would be of great help if you can help me understand how you varied the ranges for table insertion here.

Regards

Krishnan

Cindy Ellis · Jan 13, 2012

Thanks for the feedback, I'm glad it worked.
I'll do my best to explain...but parts of this code were borrowed from help files and other sources several years ago, so my explanation may not be exactly correct, and there may be more efficient ways to write the code.
The object wrddoc is created as a new word document, then wrdRange is created as a range object that includes the whole document.
When the location for the table was set to wrdRange, the "insertion point" was no longer after the the original picture, but instead, the new table replaced the current content of wrdRange, and replaced the picture. By changing the range argument in wrddoc.Tables.Add, the table could be inserted at the cursor location, which I "moved" by typing a paragraph. This could have also been done by "collapsing" wrdRange as in the code by Dossfm0q.

When you're using Excel to manipulate Word, it can be useful to record the actions in Word, then convert those to the Excel macro. That's how I started learning the syntax.

Hope that helps,

krinsn · Jan 15, 2012

Hi Cindy,

Thank you for the reply.,
So the code "Range:=.Range" takes the table to the current cursor location is it?

Also what does the following line do,

Set rngTable = wrdTable.Cell(1, i).Range

I am a novice to VB coding ,pl bear with me

Regards
krishnan

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

leniusc

New Member

Cindy Ellis

MrExcel MVP

Excel Facts

Dossfm0q

Banned User

Guntherm

New Member

krinsn

New Member

Cindy Ellis

MrExcel MVP

krinsn

New Member

Cindy Ellis

MrExcel MVP

krinsn

New Member

Cindy Ellis

MrExcel MVP

krinsn

New Member

Similar threads

Forum statistics

Share this page

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

New Member

MrExcel MVP

Excel Facts

Banned User

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

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