Excel Rows to Word Docs

[Andy Roberts]

I have an excel sheet where I log all my company quotations (1 quotation per excel row). The row contains such columns as :-
a.. Date Received
b.. Client Compnay
c.. Client Name
d.. Tender Price
e.. Lead-in period etc.
I would like to create a word document (letter) which uses certain cells to populate various elements of the letter which is saved as a word template. This would seem to suggest a mail merge would be the answer. If it is then I have no problem sorting this.

However...

There are some advanced things I'd also like to do. For example in my spreadsheet one of the columns has a lookup dropdown option to select the quotation type from a predetermined list. I'd like the selected list item to appear in the word document as well. The list item is an abbreviation and I'd like it to come across into the word document as a full text (eg. In Excel the Quote Type maybe RQ but I'd like this to come across as "Revised Quote" into the word doc.

Also there is a second dropdown list in the spreadsheet and I'd like the selected value to check a specific checkbox in the word doc. e.g. there is a list of staff members in a dropdown list identified by their initials i.e. AB, CD, DE etc. In the word doc there is a list of staff memebers, each with a checkbox next to their name. If I select AB as the staff member in the spreadsheet, I'd like the word doc to tick the relevant checkbox (hope that makes sense).

Regards

Andy

 

[macropod]

Hi Andy,

There's nothing in what you've specified that can't be incorporated into a mailmerge. As Doug says, though, a userform-based
template might suit your needs better.

--
Cheers
macropod
[Microsoft MVP - Word]


Thanks for your quick response Bernard, but I think what I need to do goes beyond Mail Merge as per my examples.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
Merge (Excel - Word)
For help on Word mail merge using Excel as the data source have a look here

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm

http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
I have an excel sheet where I log all my company quotations (1 quotation per excel row). The row contains such columns as :-
a.. Date Received
b.. Client Compnay
c.. Client Name
d.. Tender Price
e.. Lead-in period etc.
I would like to create a word document (letter) which uses certain cells to populate various elements of the letter which is
saved as a word template. This would seem to suggest a mail merge would be the answer. If it is then I have no problem sorting
this.

However...

There are some advanced things I'd also like to do. For example in my spreadsheet one of the columns has a lookup dropdown
option to select the quotation type from a predetermined list. I'd like the selected list item to appear in the word document as
well. The list item is an abbreviation and I'd like it to come across into the word document as a full text (eg. In Excel the Quote
Type maybe RQ but I'd like this to come across as "Revised Quote" into the word doc.

Also there is a second dropdown list in the spreadsheet and I'd like the selected value to check a specific checkbox in the word
doc. e.g. there is a list of staff members in a dropdown list identified by their initials i.e. AB, CD, DE etc. In the word doc
there is a list of staff memebers, each with a checkbox next to their name. If I select AB as the staff member in the spreadsheet,
I'd like the word doc to tick the relevant checkbox (hope that makes sense).

Regards

Andy

 

[Doug Robbins - Word MVP]

Thanks Doug This looks exactly what I want. Just to clarify, can I
populate a userform with excel data as the examples suggest only another
word document or xml source.

For example I have a row in excel with a unique ref number which if I
type into my userform (and everything is linked correctly) will populate
all the relevant fields in the userform using all the cells in the row
of the spreadsheet. Is this possible?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

"Doug Robbins - Word MVP" <[email protected]
I do not believe that mailmerge is the way to go.

Rather, you should be creating a template containing a userform.

See http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

and

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my
services on a paid professional basis.

"Andy Roberts" <[email protected]
Thanks for your quick response Bernard, but I think what I need
to do goes beyond Mail Merge as per my examples.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

"Bernard Liengme" <[email protected]
Merge (Excel - Word)
For help on Word mail merge using Excel as the data source
have a look here

http://www.mvps.org/dmcritchie/excel/mailmerg.htm

http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm

http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Andy Roberts" <[email protected]
I have an excel sheet where I log all my company
quotations (1 quotation per excel row). The row
contains such columns as :-

* Date Received
* Client Compnay
* Client Name
* Tender Price
* Lead-in period etc.

I would like to create a word document (letter) which
uses certain cells to populate various elements of the
letter which is saved as a word template. This would
seem to suggest a mail merge would be the answer. If it
is then I have no problem sorting this.

However...

There are some advanced things I'd also like to do. For
example in my spreadsheet one of the columns has a
lookup dropdown option to select the quotation type from
a predetermined list. I'd like the selected list item
to appear in the word document as well. The list item
is an abbreviation and I'd like it to come across into
the word document as a full text (eg. In Excel the Quote
Type maybe RQ but I'd like this to come across as
"Revised Quote" into the word doc.

Also there is a second dropdown list in the spreadsheet
and I'd like the selected value to check a specific
checkbox in the word doc. e.g. there is a list of staff
members in a dropdown list identified by their initials
i.e. AB, CD, DE etc. In the word doc there is a list of
staff memebers, each with a checkbox next to their
name. If I select AB as the staff member in the
spreadsheet, I'd like the word doc to tick the relevant
checkbox (hope that makes sense).

Regards

Andy

In the second link that I gave you, there are three examples of loading
data into a userform listbox (or it can be a combobox if you wish) from
Excel.

In the listbox (or combobox), you can select the record that contains
the desired reference number, and then use code that either populates
textboxes on the userform or sets the values of variables in the
document to the values applicable to that reference number.

You will find all that you need to know about doing this in the links to
which I directed you.
--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my
services on a paid professional basis.