Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Fellow Forum Members,
I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? I would like each cell in my Word table to match every cell in the my Excel file one for one. What is the best function to use? Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? I would like to do this right, any help will be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Can you embed the Excel worksheet in your doc?
-- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? I would like each cell in my Word table to match every cell in the my Excel file one for one. What is the best function to use? Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? I would like to do this right, any help will be greatly appreciated. Thanks. |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Why wouldn't a link from the Excel worksheet range into the Word
document work? If that just won't do, then you may have to resort to a brute-strength method I have employed: as long as your Word table and your Excel range are the same size, you could have a macro that scanned through each cell one by one and wrote the data into Word: For 1 To cntRows For 1 To cntCols WDtblcell(cntRows, cntCols) = XLcell(cntRows, cntCols) Next Next Ed On Mar 17, 2:14*pm, "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. *This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. *My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? *I would like each cell in my Word table to match every cell in the my Excel file one for one. *What is the best function to use? *Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? *I would like to do this right, any help will be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Ed,
Thanks for the help. I have a followup question regarding this approach you are recommending. In EXCEL each cell is identified A1, A2, B1, B2, C1, etc... In Word the cells in tables don't really have such reference designators. How will a macro created in Excel accomplish the task of copying data from cells A1:A4 and transfer it to the same cells located in a Word Table where such reference designators are not used? I am not to clear on how the CntRows and CntCols will accomplish this. Any info or links will be greatly appreciated. "Ed from AZ" wrote: Why wouldn't a link from the Excel worksheet range into the Word document work? If that just won't do, then you may have to resort to a brute-strength method I have employed: as long as your Word table and your Excel range are the same size, you could have a macro that scanned through each cell one by one and wrote the data into Word: For 1 To cntRows For 1 To cntCols WDtblcell(cntRows, cntCols) = XLcell(cntRows, cntCols) Next Next Ed On Mar 17, 2:14 pm, "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? I would like each cell in my Word table to match every cell in the my Excel file one for one. What is the best function to use? Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? I would like to do this right, any help will be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
You can refer to cells in a Word table by Cell(row, column).
The "cntRows" and "cntCols" I used before is what is known as "air code" or "psuedo-code" - it's not a real VBA item, but a reference to something. The same with the other terms. In this case, that portion of the maco might look something like: Dim cntRows As Long Dim cntCols As Long Dim x As Long, y As Long Dim WDtbl As Word.Table cntRows = WDtbl.Rows.Count cntCols = WDtble.Columns.Count For x = 1 To cntRows For y = 1 To cntCols WDtbl.Cell(x, y) = xlWKS.Cells(x, y) Next y Next x The whole macro, though, will also contain setting objects to the Word document and table, the Excel worksheet, and possible other objects as required by your code. Specific properties are also going to depend on your requirements. If you search the Microsoft.Public.Word.VBA.General and Microsoft.Public.Excel.Programming newsgroups, there's lots of examples there for you. Here's some other references for you: http://word.mvps.org/FAQs/InterDev/C...XLFromWord.htm http://word.mvps.org/FAQs/InterDev/C...WordFromXL.htm http://word.mvps.org/FAQs/InterDev/E...ateBinding.htm Word Help: Range Object Table object Cell Object Excel Help: Worksheet Object Range Object Cells Property Referring to Cells by Using Index Numbers Looping Through a Range of Cells Ed On Mar 21, 7:45*am, binar wrote: Ed, Thanks for the help. I have a followup question regarding this approach you are recommending. In EXCEL each cell is identified A1, A2, B1, B2, C1, etc.... In Word the cells in tables don't really have such reference designators. How will a macro created in Excel accomplish the task of copying data from cells A1:A4 and transfer it to the same cells located in a Word Table where such reference designators are not used? *I am not to clear on how the CntRows and CntCols will accomplish this. Any info or links will be greatly appreciated. "Ed from AZ" wrote: Why wouldn't a link from the Excel worksheet range into the Word document work? If that just won't do, then you may have to resort to a brute-strength method I have employed: as long as your Word table and your Excel range are the same size, you could have a macro that scanned through each cell one by one and wrote the data into Word: For 1 To cntRows * For 1 To cntCols * * WDtblcell(cntRows, cntCols) = XLcell(cntRows, cntCols) * Next Next Ed On Mar 17, 2:14 pm, "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. *This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. *My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? *I would like each cell in my Word table to match every cell in the my Excel file one for one. *What is the best function to use? *Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? *I would like to do this right, any help will be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Is it possible to use this count idea to control area that word searches in
an excel wksheet? I am trying to stop word from searching the full 6000 rows 200 is plenty. I am fairly good in excel but a little lost in word. Thank You "Ed from AZ" wrote: You can refer to cells in a Word table by Cell(row, column). The "cntRows" and "cntCols" I used before is what is known as "air code" or "psuedo-code" - it's not a real VBA item, but a reference to something. The same with the other terms. In this case, that portion of the maco might look something like: Dim cntRows As Long Dim cntCols As Long Dim x As Long, y As Long Dim WDtbl As Word.Table cntRows = WDtbl.Rows.Count cntCols = WDtble.Columns.Count For x = 1 To cntRows For y = 1 To cntCols WDtbl.Cell(x, y) = xlWKS.Cells(x, y) Next y Next x The whole macro, though, will also contain setting objects to the Word document and table, the Excel worksheet, and possible other objects as required by your code. Specific properties are also going to depend on your requirements. If you search the Microsoft.Public.Word.VBA.General and Microsoft.Public.Excel.Programming newsgroups, there's lots of examples there for you. Here's some other references for you: http://word.mvps.org/FAQs/InterDev/C...XLFromWord.htm http://word.mvps.org/FAQs/InterDev/C...WordFromXL.htm http://word.mvps.org/FAQs/InterDev/E...ateBinding.htm Word Help: Range Object Table object Cell Object Excel Help: Worksheet Object Range Object Cells Property Referring to Cells by Using Index Numbers Looping Through a Range of Cells Ed On Mar 21, 7:45 am, binar wrote: Ed, Thanks for the help. I have a followup question regarding this approach you are recommending. In EXCEL each cell is identified A1, A2, B1, B2, C1, etc.... In Word the cells in tables don't really have such reference designators. How will a macro created in Excel accomplish the task of copying data from cells A1:A4 and transfer it to the same cells located in a Word Table where such reference designators are not used? I am not to clear on how the CntRows and CntCols will accomplish this. Any info or links will be greatly appreciated. "Ed from AZ" wrote: Why wouldn't a link from the Excel worksheet range into the Word document work? If that just won't do, then you may have to resort to a brute-strength method I have employed: as long as your Word table and your Excel range are the same size, you could have a macro that scanned through each cell one by one and wrote the data into Word: For 1 To cntRows For 1 To cntCols WDtblcell(cntRows, cntCols) = XLcell(cntRows, cntCols) Next Next Ed On Mar 17, 2:14 pm, "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? I would like each cell in my Word table to match every cell in the my Excel file one for one. What is the best function to use? Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? I would like to do this right, any help will be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
Thanks to all for posting. I have been researching this and something called
DAO seems to be the solution. The problem is that it seems to be very complex. Does anyone understand DAO and how to use it with Word and Excel? I found a link he http://articles.techrepublic.com.com...3-1045296.html Any additional info will be greatly appreciated. "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? I would like each cell in my Word table to match every cell in the my Excel file one for one. What is the best function to use? Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? I would like to do this right, any help will be greatly appreciated. Thanks. |
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Best way to combine Excel Data to Word Tables?
You really don't have to go into DAO with just Word and Excel. If you
were programming Word and Excel from outside the apps from VB or Access, then that may be your answer. But if you are only using Word and Excel, then the Visual Basic for Applications macro language provided with these programs is powerful enough to do almost anything you want. For instance, I have an Excel spreadsheet "linked" to a Word report. ("Linked" is in quotes because it's not a link the way Microsoft uses it; it's simply that these two go together.) At the moment, every time I update this report, I need to update about 150 separate numbers in various places in the document, plus pull in two whole-page tables and import and embed five other Excel files. I click one button and the process takes about 45 seconds (I have a slow computer!). It's all done with macros - no ADO required. The set-up was a bit tedious, but the time savings every two weeks when I put out this report is well worth it! Anyway, that's my experience. Ed On Mar 18, 11:02*am, binar wrote: Thanks to all for posting. I have been researching this and something called DAO seems to be the solution. The problem is that it seems to be very complex. Does anyone understand DAO and how to use it with Word and Excel? I found a link he http://articles.techrepublic.com.com...3-1045296.html Any additional info will be greatly appreciated. "Suzanne S. Barnhill" wrote: Can you embed the Excel worksheet in your doc? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "binar" wrote in message ... Fellow Forum Members, I am using Word 2003 for a technical manual that has close to 300 pages of tables containing parts data. *This parts data is changing all of the time due to engineering making changes all of the time. Updating these 300 pages of data is a major pain. *My thinking is I should manage all of this data in Excel and then perform a mail merge of some kind to update 300 pages automatically. What is the best way to go about combining external data into Word? *I would like each cell in my Word table to match every cell in the my Excel file one for one. *What is the best function to use? *Is there an automated way to setup the cells in my Word tables so that each cell matches a cell in my Excel table? *I would like to do this right, any help will be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Excel data into pre-existing Word tables | Tables | |||
How do I combine info in an excel table with a word letter | Microsoft Word Help | |||
How do I combine 2 data fields into one merge field in WORD 2003? | Mailmerge | |||
How do I combine tables? | Tables | |||
Linking Excel data to tables in Word | Tables |