Merging Data from a Quote Sheet to a Letter in Word

[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]

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

 

[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

 

[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]

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]

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]

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]

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]

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]

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