[Elly]

I'm trying to lift information from a quote sheet in Excel to appear in a
letter in word - is this possible given that the sheet would change with
every new quote?

[Peter Jamieson]

Are you trying to get information from specific cells? (the sort of thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around, you
wil have difficulty extracting the information unless you are able to ensure
that the relevant cells are always identified using the same Range names in
Excel. The trouble is that whoever prepared the Excel sheet in Excel would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the column
names do not change, and even then it's a question of re-linking to the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to ensure
that whatever you implement puts the values you want to re-use in known
places where other applications always know how to get them - i.e. some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear in a
letter in word - is this possible given that the sheet would change with
every new quote?

[Elly]

Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to another
company) I then need various pieces of information i.e. customers name and
address, cost etc. to then be automatically tranferred to a letter in a word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of course I
could be completely wrong so therefore bow down to your infinite wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around, you
wil have difficulty extracting the information unless you are able to ensure
that the relevant cells are always identified using the same Range names in
Excel. The trouble is that whoever prepared the Excel sheet in Excel would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the column
names do not change, and even then it's a question of re-linking to the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to ensure
that whatever you implement puts the values you want to re-use in known
places where other applications always know how to get them - i.e. some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear in a
letter in word - is this possible given that the sheet would change with
every new quote?

[Doug Robbins - Word MVP]

You should probably take a look at the following series of articles, the
last in particular:

Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22

Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46

Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119

Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127

Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to another
company) I then need various pieces of information i.e. customers name and
address, cost etc. to then be automatically tranferred to a letter in a
word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around,
you
wil have difficulty extracting the information unless you are able to
ensure
that the relevant cells are always identified using the same Range names
in
Excel. The trouble is that whoever prepared the Excel sheet in Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to ensure
that whatever you implement puts the values you want to re-use in known
places where other applications always know how to get them - i.e. some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear in
a
letter in word - is this possible given that the sheet would change
with
every new quote?

[Peter Jamieson]

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you want
to use in your Word document. let's suppose that the values you want are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste Link
and select Edit|Paste Special, select the Paste Link radio button, then
select "Unformatted text", then click OK, then Word should insert a LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in fact be
able to do (a) manually using Edit|Find/Replace. You can then select the
document body using ctrl-A and re-execute the fields using F9. If you have
linked fields in other places in your document such as headers/footers then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else, please say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to another
company) I then need various pieces of information i.e. customers name and
address, cost etc. to then be automatically tranferred to a letter in a
word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around,
you
wil have difficulty extracting the information unless you are able to
ensure
that the relevant cells are always identified using the same Range names
in
Excel. The trouble is that whoever prepared the Excel sheet in Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to ensure
that whatever you implement puts the values you want to re-use in known
places where other applications always know how to get them - i.e. some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear in
a
letter in word - is this possible given that the sheet would change
with
every new quote?

[Elly]

Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out each
time it'll mean saving it under a new name and therefore not making the job
any quicker as i'll still have to alter the filename in the word document
(which could take as much time as just filling it out with the same detail as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you want
to use in your Word document. let's suppose that the values you want are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste Link
and select Edit|Paste Special, select the Paste Link radio button, then
select "Unformatted text", then click OK, then Word should insert a LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in fact be
able to do (a) manually using Edit|Find/Replace. You can then select the
document body using ctrl-A and re-execute the fields using F9. If you have
linked fields in other places in your document such as headers/footers then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else, please say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to another
company) I then need various pieces of information i.e. customers name and
address, cost etc. to then be automatically tranferred to a letter in a
word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around,
you
wil have difficulty extracting the information unless you are able to
ensure
that the relevant cells are always identified using the same Range names
in
Excel. The trouble is that whoever prepared the Excel sheet in Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to ensure
that whatever you implement puts the values you want to re-use in known
places where other applications always know how to get them - i.e. some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear in
a
letter in word - is this possible given that the sheet would change
with
every new quote?

[Peter Jamieson]

If you're only talking about re-using a really small amount of information
and you aren't doing it very often, I doubt if it is worth trying to
introduce any automation - unless you're doing everything /exactly/ the same
way and doing it often, the effort of setting up an automated approach tends
to be far greater than using a "traditional" method such as copy/paste. What
you tend to gain from automation is consistency, but only if each step in
the process always works the same way - if, for example, someone decides to
add an extra line to the worksheet, all the cell references in your Word
document would be wrong and you'd have to change them (and first you'd have
to notice that, or someone would have to tell you). Using the traditional
method, you'd probably see straight away where the info. was supposed to
come from and simply do the right thing. In other words there are benefits
associated with automation but there are also costs.


Peter Jamieson
"Elly" wrote in message
...
Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out
each
time it'll mean saving it under a new name and therefore not making the
job
any quicker as i'll still have to alter the filename in the word document
(which could take as much time as just filling it out with the same detail
as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you
want
to use in your Word document. let's suppose that the values you want are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste
Link
and select Edit|Paste Special, select the Paste Link radio button, then
select "Unformatted text", then click OK, then Word should insert a LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My
Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the
text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in fact
be
able to do (a) manually using Edit|Find/Replace. You can then select the
document body using ctrl-A and re-execute the fields using F9. If you
have
linked fields in other places in your document such as headers/footers
then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else, please
say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to
another
company) I then need various pieces of information i.e. customers name
and
address, cost etc. to then be automatically tranferred to a letter in a
word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite
wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of
thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where
you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around,
you
wil have difficulty extracting the information unless you are able to
ensure
that the relevant cells are always identified using the same Range
names
in
Excel. The trouble is that whoever prepared the Excel sheet in Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to
the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to
ensure
that whatever you implement puts the values you want to re-use in
known
places where other applications always know how to get them - i.e.
some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear
in
a
letter in word - is this possible given that the sheet would change
with
every new quote?

[Elly]

No, we're talking about a lot of information, co. address, phone no.'s,
equipment required, part No.'s, cost, description and so it goes on. Not to
worry. Thanks for your input though.

"Peter Jamieson" wrote:

If you're only talking about re-using a really small amount of information
and you aren't doing it very often, I doubt if it is worth trying to
introduce any automation - unless you're doing everything /exactly/ the same
way and doing it often, the effort of setting up an automated approach tends
to be far greater than using a "traditional" method such as copy/paste. What
you tend to gain from automation is consistency, but only if each step in
the process always works the same way - if, for example, someone decides to
add an extra line to the worksheet, all the cell references in your Word
document would be wrong and you'd have to change them (and first you'd have
to notice that, or someone would have to tell you). Using the traditional
method, you'd probably see straight away where the info. was supposed to
come from and simply do the right thing. In other words there are benefits
associated with automation but there are also costs.


Peter Jamieson
"Elly" wrote in message
...
Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out
each
time it'll mean saving it under a new name and therefore not making the
job
any quicker as i'll still have to alter the filename in the word document
(which could take as much time as just filling it out with the same detail
as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you
want
to use in your Word document. let's suppose that the values you want are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste
Link
and select Edit|Paste Special, select the Paste Link radio button, then
select "Unformatted text", then click OK, then Word should insert a LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My
Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the
text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in fact
be
able to do (a) manually using Edit|Find/Replace. You can then select the
document body using ctrl-A and re-execute the fields using F9. If you
have
linked fields in other places in your document such as headers/footers
then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else, please
say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our company.

Basically, I have a spreadsheet which guys fill in (as a quote to
another
company) I then need various pieces of information i.e. customers name
and
address, cost etc. to then be automatically tranferred to a letter in a
word
document. My issue is that new quote sheets will be raised (from the
standard) and I don't think that with a new quote sheet the info can be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite
wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of
thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel (Where
you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move around,
you
wil have difficulty extracting the information unless you are able to
ensure
that the relevant cells are always identified using the same Range
names
in
Excel. The trouble is that whoever prepared the Excel sheet in Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to
the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to
ensure
that whatever you implement puts the values you want to re-use in
known
places where other applications always know how to get them - i.e.
some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to appear
in
a
letter in word - is this possible given that the sheet would change
with
every new quote?

[Peter Jamieson]

In that case I'd have thought doing a global edit find/replace on the file
name would work well. If the fiels are all in the same folder, all you need
to do is replace the file name globally then ctrl-A and F9. I'd say that's
got to be quicker than filling in loads of different bits of information
manually.

Peter Jamieson


"Elly" wrote in message
...
No, we're talking about a lot of information, co. address, phone no.'s,
equipment required, part No.'s, cost, description and so it goes on. Not
to
worry. Thanks for your input though.

"Peter Jamieson" wrote:

If you're only talking about re-using a really small amount of
information
and you aren't doing it very often, I doubt if it is worth trying to
introduce any automation - unless you're doing everything /exactly/ the
same
way and doing it often, the effort of setting up an automated approach
tends
to be far greater than using a "traditional" method such as copy/paste.
What
you tend to gain from automation is consistency, but only if each step in
the process always works the same way - if, for example, someone decides
to
add an extra line to the worksheet, all the cell references in your Word
document would be wrong and you'd have to change them (and first you'd
have
to notice that, or someone would have to tell you). Using the traditional
method, you'd probably see straight away where the info. was supposed to
come from and simply do the right thing. In other words there are
benefits
associated with automation but there are also costs.


Peter Jamieson
"Elly" wrote in message
...
Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out
each
time it'll mean saving it under a new name and therefore not making the
job
any quicker as i'll still have to alter the filename in the word
document
(which could take as much time as just filling it out with the same
detail
as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you
want
to use in your Word document. let's suppose that the values you want
are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste
Link
and select Edit|Paste Special, select the Paste Link radio button,
then
select "Unformatted text", then click OK, then Word should insert a
LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My
Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the
text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in
fact
be
able to do (a) manually using Edit|Find/Replace. You can then select
the
document body using ctrl-A and re-execute the fields using F9. If you
have
linked fields in other places in your document such as headers/footers
then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else,
please
say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our
company.

Basically, I have a spreadsheet which guys fill in (as a quote to
another
company) I then need various pieces of information i.e. customers
name
and
address, cost etc. to then be automatically tranferred to a letter
in a
word
document. My issue is that new quote sheets will be raised (from
the
standard) and I don't think that with a new quote sheet the info can
be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite
wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of
thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel
(Where
you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move
around,
you
wil have difficulty extracting the information unless you are able
to
ensure
that the relevant cells are always identified using the same Range
names
in
Excel. The trouble is that whoever prepared the Excel sheet in
Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to
the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to
ensure
that whatever you implement puts the values you want to re-use in
known
places where other applications always know how to get them - i.e.
some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to
appear
in
a
letter in word - is this possible given that the sheet would
change
with
every new quote?

[Elly]

What about creating a new sheet that takes all the information you'd use and
puts it into database form so you can just merge it that way?

"Peter Jamieson" wrote:

In that case I'd have thought doing a global edit find/replace on the file
name would work well. If the fiels are all in the same folder, all you need
to do is replace the file name globally then ctrl-A and F9. I'd say that's
got to be quicker than filling in loads of different bits of information
manually.

Peter Jamieson


"Elly" wrote in message
...
No, we're talking about a lot of information, co. address, phone no.'s,
equipment required, part No.'s, cost, description and so it goes on. Not
to
worry. Thanks for your input though.

"Peter Jamieson" wrote:

If you're only talking about re-using a really small amount of
information
and you aren't doing it very often, I doubt if it is worth trying to
introduce any automation - unless you're doing everything /exactly/ the
same
way and doing it often, the effort of setting up an automated approach
tends
to be far greater than using a "traditional" method such as copy/paste.
What
you tend to gain from automation is consistency, but only if each step in
the process always works the same way - if, for example, someone decides
to
add an extra line to the worksheet, all the cell references in your Word
document would be wrong and you'd have to change them (and first you'd
have
to notice that, or someone would have to tell you). Using the traditional
method, you'd probably see straight away where the info. was supposed to
come from and simply do the right thing. In other words there are
benefits
associated with automation but there are also costs.


Peter Jamieson
"Elly" wrote in message
...
Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out
each
time it'll mean saving it under a new name and therefore not making the
job
any quicker as i'll still have to alter the filename in the word
document
(which could take as much time as just filling it out with the same
detail
as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you
want
to use in your Word document. let's suppose that the values you want
are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste
Link
and select Edit|Paste Special, select the Paste Link radio button,
then
select "Unformatted text", then click OK, then Word should insert a
LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My
Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the
text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in
fact
be
able to do (a) manually using Edit|Find/Replace. You can then select
the
document body using ctrl-A and re-execute the fields using F9. If you
have
linked fields in other places in your document such as headers/footers
then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else,
please
say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our
company.

Basically, I have a spreadsheet which guys fill in (as a quote to
another
company) I then need various pieces of information i.e. customers
name
and
address, cost etc. to then be automatically tranferred to a letter
in a
word
document. My issue is that new quote sheets will be raised (from
the
standard) and I don't think that with a new quote sheet the info can
be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite
wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of
thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel
(Where
you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move
around,
you
wil have difficulty extracting the information unless you are able
to
ensure
that the relevant cells are always identified using the same Range
names
in
Excel. The trouble is that whoever prepared the Excel sheet in
Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to
the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to
ensure
that whatever you implement puts the values you want to re-use in
known
places where other applications always know how to get them - i.e.
some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to
appear
in
a
letter in word - is this possible given that the sheet would
change
with
every new quote?

[Peter Jamieson]

Yes, you could do that. MERGEFIELD fields are typically more
straightforward than having a load of links. But you may find yourself
having to apply formatting switches to those merge fields and so on. At
some point you would still have to point either Word or the new sheet
(if it was not the one containing the source data) to the correct source
of the data.

Why not just try it? Only you can really judge which approach is simpler
and more reliable in the situation you're in.

Peter Jamieson


Elly wrote:
What about creating a new sheet that takes all the information you'd use and
puts it into database form so you can just merge it that way?

"Peter Jamieson" wrote:

In that case I'd have thought doing a global edit find/replace on the file
name would work well. If the fiels are all in the same folder, all you need
to do is replace the file name globally then ctrl-A and F9. I'd say that's
got to be quicker than filling in loads of different bits of information
manually.

Peter Jamieson


"Elly" wrote in message
...
No, we're talking about a lot of information, co. address, phone no.'s,
equipment required, part No.'s, cost, description and so it goes on. Not
to
worry. Thanks for your input though.

"Peter Jamieson" wrote:

If you're only talking about re-using a really small amount of
information
and you aren't doing it very often, I doubt if it is worth trying to
introduce any automation - unless you're doing everything /exactly/ the
same
way and doing it often, the effort of setting up an automated approach
tends
to be far greater than using a "traditional" method such as copy/paste.
What
you tend to gain from automation is consistency, but only if each step in
the process always works the same way - if, for example, someone decides
to
add an extra line to the worksheet, all the cell references in your Word
document would be wrong and you'd have to change them (and first you'd
have
to notice that, or someone would have to tell you). Using the traditional
method, you'd probably see straight away where the info. was supposed to
come from and simply do the right thing. In other words there are
benefits
associated with automation but there are also costs.


Peter Jamieson
"Elly" wrote in message
...
Peter, thanks. That is a great way of doing things and I'll be sure to
remember it. However, given that a new quote sheet will be filled out
each
time it'll mean saving it under a new name and therefore not making the
job
any quicker as i'll still have to alter the filename in the word
document
(which could take as much time as just filling it out with the same
detail
as
the spreadsheet). Any other ideas?

Thanks

"Peter Jamieson" wrote:

Let's suppose your quote sheet has 2 cells at (say) C7 and E3 that you
want
to use in your Word document. let's suppose that the values you want
are
always in those cells and only the filename changes.

If you select cell C7 in Excel and Edit|Copy, then in Word, Edit|Paste
Link
and select Edit|Paste Special, select the Paste Link radio button,
then
select "Unformatted text", then click OK, then Word should insert a
LINK
field - use Alt-F9 to view its code, which might be something like

{ LINK Excel.Sheet.8 "C:\\Documents and Settings\\me\\My Documents\\My
Data
Sources\\mysheet1.xls" "Sheet1!R7C3" \a \t }

The result should be the text from the Excel cell.

So assuming all the data comes from the same worksheet, to insert the
text
from another worksheet, what you need to do is
a. replace the full path name
C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet1.xls
by the new path name - perhaps

C:\\Documents and Settings\\me\\My Documents\\My Data
Sources\\mysheet2.xls
b. select all the LINK fields and re-execute them.

If you have used Alt-F9 to reveal all the field codes, you should in
fact
be
able to do (a) manually using Edit|Find/Replace. You can then select
the
document body using ctrl-A and re-execute the fields using F9. If you
have
linked fields in other places in your document such as headers/footers
then
you have to update them separately.

You could also do all that programmatically.

In fact there is a "proper" way to change the link path name
programmatically, but let's not go too far.

Is that the kind of thing you need to do? if it's something else,
please
say
how your situation differs.

Peter Jamieson

"Elly" wrote in message
...
Wow Peter, you really know your stuff.

It's a little task I've been given as the IT geek within our
company.

Basically, I have a spreadsheet which guys fill in (as a quote to
another
company) I then need various pieces of information i.e. customers
name
and
address, cost etc. to then be automatically tranferred to a letter
in a
word
document. My issue is that new quote sheets will be raised (from
the
standard) and I don't think that with a new quote sheet the info can
be
transferred without redirecting word to look in the right place. Of
course I
could be completely wrong so therefore bow down to your infinite
wisdom.

Thanks

Elly

"Peter Jamieson" wrote:

Are you trying to get information from specific cells? (the sort of
thing
you might do using copy/paste from Excel into Word?)

Or are you trying to get information from a table within Excel
(Where
you
might be mailmerging from Excel to Word?)

If it's "specific cells", then yes, if the cell locations move
around,
you
wil have difficulty extracting the information unless you are able
to
ensure
that the relevant cells are always identified using the same Range
names
in
Excel. The trouble is that whoever prepared the Excel sheet in
Excel
would
have to know how to do that.

If it's "table and mailmerge", then you should be OK as long as the
column
names do not change, and even then it's a question of re-linking to
the
Excel sheet and altering your Word document to match.

If you are trying to automate this kind of stuff it's important to
ensure
that whatever you implement puts the values you want to re-use in
known
places where other applications always know how to get them - i.e.
some
overall system design is needed.

Peter Jamieson
"Elly" wrote in message
...
I'm trying to lift information from a quote sheet in Excel to
appear
in
a
letter in word - is this possible given that the sheet would
change
with
every new quote?