A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Microsoft Word Help
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Best way to combine Excel Data to Word Tables?



 
 
Thread Tools Display Modes
  #1  
Old March 17th 08, 08:28 PM posted to microsoft.public.word.docmanagement
binar
external usenet poster
 
Posts: 90
Default 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.
Ads
  #2  
Old March 17th 08, 09:14 PM posted to microsoft.public.word.docmanagement
Suzanne S. Barnhill
external usenet poster
 
Posts: 33,626
Default 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  
Old March 17th 08, 09:32 PM posted to microsoft.public.word.docmanagement
Ed from AZ
external usenet poster
 
Posts: 91
Default 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  
Old March 18th 08, 06:02 PM posted to microsoft.public.word.docmanagement
binar
external usenet poster
 
Posts: 90
Default 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.




  #5  
Old March 18th 08, 10:20 PM posted to microsoft.public.word.docmanagement
Ed from AZ
external usenet poster
 
Posts: 91
Default 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 -


  #6  
Old March 21st 08, 02:45 PM posted to microsoft.public.word.docmanagement
binar
external usenet poster
 
Posts: 90
Default 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 -



  #7  
Old March 24th 08, 02:49 PM posted to microsoft.public.word.docmanagement
Ed from AZ
external usenet poster
 
Posts: 91
Default 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 -


  #8  
Old April 11th 08, 03:21 PM posted to microsoft.public.word.docmanagement
Curt
external usenet poster
 
Posts: 32
Default 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 -



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Excel data into pre-existing Word tables Neil Tables 0 October 4th 07 07:01 PM
How do I combine info in an excel table with a word letter Alexander[_3_] Microsoft Word Help 1 September 20th 07 05:00 AM
How do I combine 2 data fields into one merge field in WORD 2003? Jay Silver Mailmerge 2 July 19th 06 07:39 PM
How do I combine tables? Bob S Tables 2 February 10th 05 06:31 PM
Linking Excel data to tables in Word KSit Tables 3 November 26th 04 09:36 PM


All times are GMT +1. The time now is 07:22 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.