Link table from excel to word using word VBA
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 |
Link table from excel to word using word VBA
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 :-) |
Link table from excel to word using word VBA
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. wrote: 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 :-) |
Link table from excel to word using word VBA
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 :-) |
Link table from excel to word using word VBA
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 we 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").Sele ct 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. wrote: 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 :-) |
Link table from excel to word using word VBA
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 we 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 :-) |
Link table from excel to word using word VBA
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. wrote: 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 we 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 :-) |
Link table from excel to word using word VBA
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 :-) |
All times are GMT +1. The time now is 06:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com