Link table from excel to word using word VBA

[hornbecky83]

Hi-

I am new to VBA. I am making an automated report by copying excel
tables and pasting them in word. I want to use word VBA instead of
excel. I can get the tables to paste into word, but I can not get them
to be linked, so that when a parameter changes in excel it
automatically updates in my word document. The PasteExcelTable command
isn't working and I have the LinkedToExcel = true. I pasted my code
below. I am also having difficulty getting the excel table to paste
into the right document. I tried running the code in a module in the
document and also running the code in the document itself. But if
another word document is open, it sometimes pastes the table in the
other document. Can someone please help me!! Thank you in advance-
Sarah

Sub MissionDesignTemplate()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim strLVParameters As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Documents and
Settings\shornbec\Desktop\sarah\excel
templates\Mission.FY07Q1_Sarah.xls")

xlBook.Sheets("Report Tables").Range("LVParameters").Copy
Selection.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False,
RTF:=False

xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
End Sub

 

[Cindy M.]

Hi Sarah

Rather than using copy and paste, it's usually better to generate the
link by inserting a LINK field (what Word does for you when you paste and
link). Prepare by doing this

1. In Excel, select the table. Assign it a Range name by clicking in the
"Name Box" (left of the formula bar) and typing a name. Press Enter.

2. Now copy the table

3. Swith to Word. Edit/Paste and activate the "Link" option. If you want
the table to look like a Word table, don't change the selection in the
format list.

4. In the Word document, press Alt+F9 to toggle on the field codes. You
should see something that starts with { LINK "Excel.Sheet which is how
Word manages the link to Excel.

5. Select the part that looks like this
Sheet1!R4C3:R6C4
and type in the Range name you entered in step (1). This is easier to
manage - especially if rows or columns are added within the range - and
to remember.

What you want to do in your is create such a field. Everything between
the { field brackets } is what you need to tell Word which table to
insert. No need to have Excel open, or to switch to it at all in order to
insert a table into Word.

The basic VBA syntax then would be
Selection.Fields.Add Range:=Selection.Range, _
Text:=" LINK Excel.Sheet.8 "C:\\Test\\Book1" RangeName \a \f 4 \h "

You can copy and paste what's between the brackets to the Text argument.

I am new to VBA. I am making an automated report by copying excel
tables and pasting them in word. I want to use word VBA instead of
excel. I can get the tables to paste into word, but I can not get them
to be linked, so that when a parameter changes in excel it
automatically updates in my word document. The PasteExcelTable command
isn't working and I have the LinkedToExcel = true. I pasted my code
below. I am also having difficulty getting the excel table to paste
into the right document. I tried running the code in a module in the
document and also running the code in the document itself. But if
another word document is open, it sometimes pastes the table in the
other document. Can someone please help me!! Thank you in advance-
Sarah

Sub MissionDesignTemplate()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim strLVParameters As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Documents and
Settings\shornbec\Desktop\sarah\excel
templates\Mission.FY07Q1_Sarah.xls")

xlBook.Sheets("Report Tables").Range("LVParameters").Copy
Selection.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False,
RTF:=False

xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[hornbecky83]

Thanks for your help Cindy, however I am at another similiar roadblock-

Since my posting, I have switched over to working in excel VBA. I am
copying tables in excel and pasting them linked in word with the
following code:

Set rng = Wdoc.Paragraphs.Last.Range
rng.PasteExcelTable linkedtoexcel:=True, wordformatting:=False,
RTF:=False
rng.Tables(1).Rows.Alignment = wdAlignRowCenter
rng.Tables(1).AllowAutoFit = True
rng.Tables(1).AutoFitBehavior wdAutoFitContent
rng.Tables(1).Range.InsertCaption Label:=wdCaptionTable, Title:= _
"Mission Design Timeline and Delta V Budget Table", _
Position:=wdCaptionPositionAbove

I have a new problem now, where it paste fine and links, but when word
updates the tables from excel, all the table formating I did (such as
alignment, autofitcontent, and caption) go away. I found an earlier
posting where you said to use \*MERGEFORMAT, by typing alt-F9 in the
word document and adding \*MERGEFORMAT. This works. However, since I
will making many different word versions with this same code and have
20 tables or more. I do not want to have to continuously open the word
document my code created, toggle alt-F9, and manually insert
\*MERGEFORMAT into the many LINKs. Is there a way I can code
\*MERGEFORMAT into my excel VBA or add another code so that when my
tables update the table formating i mentioned earlier won't go away.
At the very least I would like the wdautofitcontent to stay intact.

I tried writing code to add \*MERGEFORMAT with the previous VBA syntex
you wrote me, but it would not work. I kept getting a run-time error
'450': wrong number of arguments or invalid property assignment. I get
this error even when I change rng to Wdoc.Application.Selection, where
Wdoc = Wapp.ActiveDocument. Or when I add preserveformat = true. This
is the code I wrote:

rng.Fields.Add Range:=Selection.Range, Type:=xlWorksheet, _
Text:="LINK Excel.Sheet.8 C:\\Documents and
Settings\\shornbec\\Desktop\\sarah\\excel
templates\\Mission.FY07Q1_Sarah.xls MissionTimelineDeltaVBudgetTable \a
\f 4 \h \*MERGEFORMAT"

Can you or anyone else out there that knows my problem please help me!!

Thank you!

Sarah

 

[Cindy M.]

I tried writing code to add \*MERGEFORMAT with the previous VBA syntex

you wrote me, but it would not work. I kept getting a run-time error
'450': wrong number of arguments or invalid property assignment. I get
this error even when I change rng to Wdoc.Application.Selection, where
Wdoc = Wapp.ActiveDocument. Or when I add preserveformat = true. This
is the code I wrote:

rng.Fields.Add Range:=Selection.Range, Type:=xlWorksheet, _
Text:="LINK Excel.Sheet.8 C:\\Documents and
Settings\\shornbec\\Desktop\\sarah\\excel
templates\\Mission.FY07Q1_Sarah.xls MissionTimelineDeltaVBudgetTable \a
\f 4 \h \*MERGEFORMAT"

The Type argument is wrong; you don't need it since the LINK field name
is specified in the text. Remove it completely or use wdApp.wdFieldEmpty
(where wdApp is whatever variable name you're using for the
Word.Application). And put a space between \* and Mergeformat. You'll
probably also need to put the path in 'single quotes' since it contains
spaces.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[hornbecky83]

Hi Cindy-

I tried doing what you said and that didn't seem to work. It wouldn't
accept the type:= Wapp.wdFieldEmpty so I replaced it with type:=
wdLinkTypeText. I tried several other versions and it still wouldn't
work. I was getting the same error. The other things I tried were:
remove type argument, remove preserveformatting argument, remove both
type and preserveformatting argument, and remove range argument. The
last piece of code I tried was:

rng.Fields.Add Range:=Selection.Range, Type:=wdLinkTypeText, _
Text:="LINK Excel.Sheet.8 'C:\\Documents and
Settings\\shornbec\\Desktop\\sarah\\excel
templates\\Mission.FY07Q1_Sarah.xls' 'MissionTimelineDeltaVBudgetTable'
\a \f 4 \h \* MERGEFORMAT", PreserveFormatting:=True

I am really stuck with this. I pasted the rest of my incase you needed
it. Thank you for your help!

********************

Private Sub create_report()

Dim filename As String

Dim Wapp As Word.Application
Dim Wdoc As Word.Document
Dim dobj As New DataObject

Set Wapp = CreateObject("Word.Application")
Wapp.Visible = True
Wapp.Documents.Add
Set Wdoc = Wapp.ActiveDocument

Set rng = Wdoc.Paragraphs.Last.Range
rng.ListFormat.ApplyListTemplate Wdoc.Application.ListGalleries( _
wdOutlineNumberGallery).ListTemplates(5),
ContinuePreviousList:=False, _
ApplyTo:=wdListApplyToWholeList,
DefaultListBehavior:=wdWord9ListBehavior

rng.Style = "Title"
rng.InsertAfter "Contents"
rng.InsertParagraphAfter

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "Normal"

Set rng = Wdoc.Paragraphs.Last.Range
With Wdoc.Application.ActiveDocument
.TablesOfContents.Add Range:=rng, RightAlignPageNumbers:=True,
_
UseHeadingStyles:=True, UpperHeadingLevel:=2, _
LowerHeadingLevel:=4, IncludePageNumbers:=True
.TablesOfContents(1).TabLeader = wdTabLeaderDots
.TablesOfContents.Format = wdIndexIndent
End With

rng.InsertParagraphAfter

Set rng = Wdoc.Paragraphs.Last.Range
rng.InsertBreak Type:=wdSectionBreakNextPage


Dim rng As Word.Range
Dim xlMissionDesign As Excel.Workbook
Dim x As Integer

ThisWorkbook.Activate
Worksheets("more options").Activate
x = Worksheets("more options").Range("A1")
y = Worksheets("more options").Range("A2")

Call UseFileDialogOpen

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "Heading 1"
rng.InsertAfter "Mission Design"
rng.InsertParagraphAfter

Workbooks("Mission.FY07Q1_Sarah.xls").Activate

For i = 1 To x

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "Heading 3"
rng.Font.Underline = wdUnderlineSingle
rng.InsertAfter "Option" & " " & i
rng.InsertParagraphAfter

For j = 1 To y

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "Heading 4"
rng.Font.Bold = False
rng.Font.Name = "Arial"
rng.Font.Size = 12
rng.InsertAfter "Flight Element" & " " & j
rng.InsertParagraphAfter

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "List Bullet"
rng.InsertAfter "max DLA"
rng.ListFormat.ListIndent
rng.InsertParagraphAfter

Set rng = Wdoc.Paragraphs.Last.Range
rng.Style = "Normal"


Worksheets("Report Tables Delta V").Activate
Worksheets("Report Tables Delta
V").Range("MissionTimelineDeltaVBudgetTable").Select
Workbooks.Application.CutCopyMode = False
Workbooks.Application.Selection.Copy

rng.InsertParagraphAfter
Set rng = Wdoc.Paragraphs.Last.Range
'rng.PasteExcelTable linkedtoexcel:=True,
wordformatting:=False, RTF:=False
' rng.Tables(1).Rows.Alignment = wdAlignRowCenter
'rng.Tables(1).AllowAutoFit = True
'rng.Tables(1).AutoFitBehavior wdAutoFitContent
rng.Fields.Add Range:=Selection.Range, Type:=wdLinkTypeText, _
Text:="LINK Excel.Sheet.8 'C:\\Documents and
Settings\\sbec\\Desktop\\sarah\\excel templates\\Mission_Sarah.xls'
'MissionTable' \a \f 4 \h \* MERGEFORMAT", PreserveFormatting:=True

Next j
Next i

end sub

 

[Cindy M.]

I tried doing what you said and that didn't seem to work. It wouldn't

accept the type:= Wapp.wdFieldEmpty so I replaced it with type:=
wdLinkTypeText. I tried several other versions and it still wouldn't
work. I was getting the same error. The other things I tried were:
remove type argument, remove preserveformatting argument, remove both
type and preserveformatting argument, and remove range argument. The
last piece of code I tried was:

rng.Fields.Add Range:=Selection.Range, Type:=wdLinkTypeText, _
Text:="LINK Excel.Sheet.8 'C:\\Documents and
Settings\\shornbec\\Desktop\\sarah\\excel
templates\\Mission.FY07Q1_Sarah.xls' 'MissionTimelineDeltaVBudgetTable'
\a \f 4 \h \* MERGEFORMAT", PreserveFormatting:=True

If you include the Type argument, then you have to leave out the word
LINK in the Text argument. If you set PreserveFormatting:=True, then you
should leave \* MergeFormat out of the Text argument.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[hornbecky83]

Thank you Cindy that helped alot! Thanks for sticking with me. I
wanted to ask you another question if you don't mind. Do you know how
to bookmark a link field. I want to add a bookmark to a table, but
everytime I update my table links, the bookmark goes away. It was
suggested to me to bookmark the link field, but I do not know how to do
this. Do you have any suggestions or know where to point me? Is there
another method you would suggest for maintaining the bookmark with a
table link updates? Thanks again for all you help.

Sarah

I had another question that related

 

[Cindy M.]

Hi Hornbecky83,

Thanks for sticking with me. I
wanted to ask you another question if you don't mind. Do you know how
to bookmark a link field. I want to add a bookmark to a table, but
everytime I update my table links, the bookmark goes away. It was
suggested to me to bookmark the link field, but I do not know how to do
this. Do you have any suggestions or know where to point me? Is there
another method you would suggest for maintaining the bookmark with a
table link updates?

I peripherally followed your discussion about that, but since I basically
agreed with what "macropod" told you, I didn't jump in there...

You should be able to see the LINK field if you press Alt+F9. You'll need
to select a paragraph above (and also maybe below) the field in order to
create a bookmark that updating the field won't delete.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail