Excel data to a Word doc

[Guest]

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies. Thanks! Carolyn

 

[Cindy M -WordMVP-]

Hi Carolyn

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies.

I'm afraid I don't understand the question very well. Do you mean you want to
LINK a column of data from Excel into Word? Try this:

- In Excel, select the cells and copy
- in Word: Edit/Paste Special. Activate the "link" option. OK

This should insert the cells in the form of a Word table. The link back to Excel
exists (but depending on the version of Word may not update automatically).
Behind the table a LINK field is maintaining the connection. Press Alt+F9 and
you can see the code, including the path and file name of the Excel workbook,
and the cell range.

Does this give you at least the basics of what you need?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[John McGhie [MVP - Word and Word Macintosh]]

This is easy if the name of the Excel spreadsheet does NOT vary: you simply
link to named ranges in the spreadsheet, as described in the Help.

If the name of the spreadsheet DOES vary, you need to construct logic to
compute the name of the spreadsheet and perform the import.

We can be more help if you provide more detail about what you're trying to
accomplish.

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies. Thanks! Carolyn

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

 

[Ed]

What about an AutoNew macro that would invoke and return the file name form
a built-in dialog, and then pull the same range of cells from the same
sheet?

Ed

 

[Guest]

Thank you John, Cindy and Ed - To give you the info that is needed - The name
of the spreadsheet will vary. This comlicates things to be sure. But the
spreadsheet is generated from a template, so it should be of a consistent
format.

Your ideas all seem to be heading toward what I think I need - I will need
some guidance in how to implement these solutions, since I am now in new
territory! Thank you so much. Carolyn

This is easy if the name of the Excel spreadsheet does NOT vary: you simply
link to named ranges in the spreadsheet, as described in the Help.

If the name of the spreadsheet DOES vary, you need to construct logic to
compute the name of the spreadsheet and perform the import.

We can be more help if you provide more detail about what you're trying to
accomplish.

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies. Thanks! Carolyn

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

 

[Ed]

Carolyn: When you say a "Word template", and you say the Excel "spreadsheet
is generated from a template", I get the idea that this is a
boiler-plate-type report you run on a regular basis. A template in Word is
just that - a preformatted "master" that has some elements in it, and
"blanks for you to fill in. It's invoked by choosing File >> New. You
could add another template there that has all the standard language of your
report, a bookmark for where your Excel info lands, and a macro that
automatically runs when a new document is created from that template.

If the spreadsheet is generated from a template, is it always saved in the
same location? And is it always saved with a similar name, perhaps made
unique by adding the date to the name? (As in "C:\Desktop\MyData\ThisData
20060209.xls") If so, then a macro can be coded to automatically search
that folder directory for the latest file of type .xls, open it and copy the
data from a certain range, and then exit and paste that into your report
doc.

A different way, especially if your Excel file is not all that consistent,
but your Word doc would be, is to open the Excel file and run an Excel macro
to copy the data, open a Word doc and paste, and then save the Word doc.
The advantage this might have is you are definitely in the right Excel file
to get the current data. This can still be done from within Word, but you
would have to open a dialog to browse for the file - that's not a bad thing;
it's just one more step. It really all depends on how much is consistent
and how much is sparkling new each time.

Ed

Thank you John, Cindy and Ed - To give you the info that is needed - The name
of the spreadsheet will vary. This comlicates things to be sure. But the
spreadsheet is generated from a template, so it should be of a consistent
format.

Your ideas all seem to be heading toward what I think I need - I will need
some guidance in how to implement these solutions, since I am now in new
territory! Thank you so much. Carolyn

This is easy if the name of the Excel spreadsheet does NOT vary: you simply
link to named ranges in the spreadsheet, as described in the Help.

If the name of the spreadsheet DOES vary, you need to construct logic to
compute the name of the spreadsheet and perform the import.

We can be more help if you provide more detail about what you're trying to
accomplish.

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies. Thanks! Carolyn

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

 

[Guest]

Here's the details... We use an Excel spreadsheet to price a job. When one
of our sales people goes to price a new job, they start with a blank copy of
the Job Pricing spreadsheet from a template. (So I can keep the pricing as
current as possible) They start a new spreadsheet using the Job Pricing
template. Once it is filled in, they save it into their individual folders
on our server with the specific job name ie U:\jane\smith job pricing.xls

Once it is priced, we would like to have a Word template to generate the
beginnings of a quote letter which would itemize the columns that are
calculated in the Job Pricing spreadsheet. (If I was really asking for the
moon, I would say only non-blank rows from the spreadsheet...)

Somehow I would like to make this as easy as possible for the Sales people -
with as few steps for them, even if it takes more work on my end to set it
up. thanks.

Carolyn: When you say a "Word template", and you say the Excel "spreadsheet
is generated from a template", I get the idea that this is a
boiler-plate-type report you run on a regular basis. A template in Word is
just that - a preformatted "master" that has some elements in it, and
"blanks for you to fill in. It's invoked by choosing File >> New. You
could add another template there that has all the standard language of your
report, a bookmark for where your Excel info lands, and a macro that
automatically runs when a new document is created from that template.

If the spreadsheet is generated from a template, is it always saved in the
same location? And is it always saved with a similar name, perhaps made
unique by adding the date to the name? (As in "C:\Desktop\MyData\ThisData
20060209.xls") If so, then a macro can be coded to automatically search
that folder directory for the latest file of type .xls, open it and copy the
data from a certain range, and then exit and paste that into your report
doc.

A different way, especially if your Excel file is not all that consistent,
but your Word doc would be, is to open the Excel file and run an Excel macro
to copy the data, open a Word doc and paste, and then save the Word doc.
The advantage this might have is you are definitely in the right Excel file
to get the current data. This can still be done from within Word, but you
would have to open a dialog to browse for the file - that's not a bad thing;
it's just one more step. It really all depends on how much is consistent
and how much is sparkling new each time.

Ed

Thank you John, Cindy and Ed - To give you the info that is needed - The name
of the spreadsheet will vary. This comlicates things to be sure. But the
spreadsheet is generated from a template, so it should be of a consistent
format.

Your ideas all seem to be heading toward what I think I need - I will need
some guidance in how to implement these solutions, since I am now in new
territory! Thank you so much. Carolyn

This is easy if the name of the Excel spreadsheet does NOT vary: you simply
link to named ranges in the spreadsheet, as described in the Help.

If the name of the spreadsheet DOES vary, you need to construct logic to
compute the name of the spreadsheet and perform the import.

We can be more help if you provide more detail about what you're trying to
accomplish.


On 3/2/06 10:41 PM, in article
(e-mail address removed), "Stoke"

How can I create a Wrod template that will pull data from an Excel
spreadsheet - even copying the column would be good enough. It would have to
be a template that allows us to specify the name of the Excel spreadsheet,
since that will be what varies. Thanks! Carolyn

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

 

[Ed]

What you are asking for is really not that difficult (even getting only
non-blank rows!). It may be a bit involved, though. If the sales people
are doing it, then you run into the issue of multiple people touching
something they really don't know much about; unless you also intend on
setting them all down for an in-depth training session of "It's supposed to
do this, and if it doesn't THEN STOP!!", you have to program for the
bull-in-china-shop user.

First, though, decide if you really need to program. What mountain are you
trying to move by programming? Can the users simply open the spreadsheet,
copy the rows they need, go into the Word doc and paste? What are you
trying to accomplish with

itemize the columns that are
calculated in the Job Pricing spreadsheet.

that simple built-in functions don't accomplish? If you're trying to make
it easier than CTRL+C, CTRL+V, it's going to be a lot of effort. Or I don't
fully understand what you need beyond grabbig a few columns of data.

Ed

 

[Ed]

Carolyn: I hate to do this, but I'm going to have to be offline for a few
days. I'll try to sneak back into this as I can, but otherwise I'll be back
Monday. I have this thread watched, so I will come right back here.
Hopefully, though, someone else with more knowledge than I (which is just
about everyone else here who answers!) will jump in.

Ed

 

[Guest]

I guess what I am trying to accomplish is an easy way to give everyone a good
start on a form letter that they can use to send out their quotes, while
allowing them to incorporate the specifics of the job. I've been asked to
create a 'skeleton' that has all the basic info on it that they can cut out
or add to if needed, and the thought has been to import the description from
the procing spreadsheet. They aren't really interested in doing a lot of
their own editing and manipulating of files/ documents. Is there a simpler
way to solve the problem than what I am trying to do without asking them to
learn how to do this manually? Carolyn

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?U3Rva2U=?=,

I guess what I am trying to accomplish is an easy way to give everyone a good
start on a form letter that they can use to send out their quotes, while
allowing them to incorporate the specifics of the job. I've been asked to
create a 'skeleton' that has all the basic info on it that they can cut out
or add to if needed, and the thought has been to import the description from
the procing spreadsheet. They aren't really interested in doing a lot of
their own editing and manipulating of files/ documents. Is there a simpler
way to solve the problem than what I am trying to do without asking them to
learn how to do this manually?

I think the first thing we need to understand is: are macros allowed in the Word
side? That makes a difference as to how simple we can make it for the user.

Ed's original suggestion about letting the user pick up the file name from a
dialog box is a good way to go. There are various routes one can take from that
point on. For example, the macro could put the information in a custom document
property (File/Properties/Custom). This path could then be used in all LINK
fields pulling in the Excel information. And if it ever needed to be changed, it
would only need to be changed in the one place.

Pulling in "only the non-blank rows"...

You mean the spreadsheet should come across "in corpore" (except for the blank
rows)? Take a look at the Insert Database tool in the Data toolbar. It's a lot
like setting up a mail merge. You can use the Query Options to set a data column
to be "not blank". Does this do what you're looking for, more or less?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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