Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 18th 04, 07:36 PM
Rashid Khan
 
Posts: n/a
Default Extract Data for Mail Merge

Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in MS
Word. I have designed the Mail Merge file in Word with all the required
fields...

I wish to know how is it possible for me to generate the Mail Merge letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by clicking
the appropriate button the corresponding Mail Merge letter will be generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid



  #2   Report Post  
Old December 18th 04, 08:06 PM
Gord Dibben
 
Posts: n/a
Default

Rashid

For help on Word mail merge using Excel as the data source.

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

http://www.mvps.org/word/FAQs/MailMe...AMailMerge.htm

http://www.mvps.org/word/FAQs/MailMe...DataSource.htm


Gord Dibben Excel MVP

On Sun, 19 Dec 2004 00:06:04 +0530, "Rashid Khan"
wrote:

Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in MS
Word. I have designed the Mail Merge file in Word with all the required
fields...

I wish to know how is it possible for me to generate the Mail Merge letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by clicking
the appropriate button the corresponding Mail Merge letter will be generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid


  #3   Report Post  
Old December 18th 04, 08:09 PM
Arvi Laanemets
 
Posts: n/a
Default

Hi

Be sure that Sheet1 is first one in Master.xls, and that the file is saved
before you continue.
In Word, start Mail Merge and create an empty main document.
Click on Get Data button, select 'Open Data Source', set file type to Excel
files, and locate for Master.xls and open it. Select 'Entire spreadsheet'
when asked.
Continue as usually.
When your mail merge document is ready, start it. In next window you can
select rows to be processed, or set filters on some fields (columns in excel
workbook). A hint here - when you don't have any passing column in
Master.xls, you can add some column into it, which you can use for filtering
in Mail mMerge afterwards.

To use excel worksheet as mail merge source, all data MUST be in one single
continous table. The table MUST have a header row. All data for one mail
letter/envelope/label MUST be on single row. It's easier when the table is
on leftmost tab of workbook, but there are ways to make it work otherwise
too (using named range).


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in MS
Word. I have designed the Mail Merge file in Word with all the required
fields...

I wish to know how is it possible for me to generate the Mail Merge

letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by clicking
the appropriate button the corresponding Mail Merge letter will be

generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid




  #4   Report Post  
Old December 19th 04, 07:40 PM
Rashid Khan
 
Posts: n/a
Default

Hello Gord and Arvi,
Thanks for the prompt reply.
I am aware how to create the mail merge. What I was after that while the
Master.xls is open is there a possible way to conduct the mail merge...
becoz now I am going thru the following steps
Step 1) make necessary entries in master.xls.. and ofcourse save it.
Step 2) Go to MS Word and Open 20+ different mail merge files to generate
the required letters.

Is there some way of automating this whole process.

Any clue?
Rashid
"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in MS
Word. I have designed the Mail Merge file in Word with all the required
fields...

I wish to know how is it possible for me to generate the Mail Merge
letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by clicking
the appropriate button the corresponding Mail Merge letter will be
generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid




  #5   Report Post  
Old December 19th 04, 09:06 PM
Arvi Laanemets
 
Posts: n/a
Default

Hi

Somehow we aren't able to communicate or what ...
1. Make necessary entries in Master.xls. Add a column Selected into source
table, and enter p.e. "X" for all records yuo want to be processed. Save the
file.
2. Open predesigned mail merge main document in word, or create a new main
document with Master.xls as source table.
3. Select Mail Merge from menu (again), and
3.1. Press Query Options. Set filter to Selected="X". Close Options window
and continue with mail merge - your 20+ documents are
printed/mailed/faxed/created - as you select in next window.
3.2. Press Merge. In Merge window, press Query Options. Set filter to
Selected="X". Close Options window and continue with mail merge - your 20+
documents are printed/mailed/faxed/created - as you select in Merge window.
When you create a new document, a N*M -page word document is created, where
n is number of processed records, and m is number of pages for one document
matching for single row in source file.

From where do you get those 20+ merge files to open?

When you add records into your Excel table after you opened MM main
document, and want to process them, then it isn't enough to save the source
file - you have to relink the workbook to main document anew (pressing Get
Data button, etc.).


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hello Gord and Arvi,
Thanks for the prompt reply.
I am aware how to create the mail merge. What I was after that while the
Master.xls is open is there a possible way to conduct the mail merge...
becoz now I am going thru the following steps
Step 1) make necessary entries in master.xls.. and ofcourse save it.
Step 2) Go to MS Word and Open 20+ different mail merge files to generate
the required letters.

Is there some way of automating this whole process.

Any clue?
Rashid
"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in

MS
Word. I have designed the Mail Merge file in Word with all the required
fields...

I wish to know how is it possible for me to generate the Mail Merge
letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and

every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by

clicking
the appropriate button the corresponding Mail Merge letter will be
generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid








  #6   Report Post  
Old December 22nd 04, 06:46 PM
Rashid Khan
 
Posts: n/a
Default

Hi Arvi,
Sorry my system was down.
Your suggestion is good if it is a single document. I wish to generate 20+
different files, some portrait and some landscape format. All 20+ will not
be required always... there may be instance when only 1 or 2 files will be
required.. The solution u suggested would generate all 20+ letter which is
not what I require.

What can u suggest in this matter

Thanks for your time and reply

"Arvi Laanemets" wrote in message
...
Hi

Somehow we aren't able to communicate or what ...
1. Make necessary entries in Master.xls. Add a column Selected into source
table, and enter p.e. "X" for all records yuo want to be processed. Save
the
file.
2. Open predesigned mail merge main document in word, or create a new main
document with Master.xls as source table.
3. Select Mail Merge from menu (again), and
3.1. Press Query Options. Set filter to Selected="X". Close Options window
and continue with mail merge - your 20+ documents are
printed/mailed/faxed/created - as you select in next window.
3.2. Press Merge. In Merge window, press Query Options. Set filter to
Selected="X". Close Options window and continue with mail merge - your 20+
documents are printed/mailed/faxed/created - as you select in Merge
window.
When you create a new document, a N*M -page word document is created,
where
n is number of processed records, and m is number of pages for one
document
matching for single row in source file.

From where do you get those 20+ merge files to open?

When you add records into your Excel table after you opened MM main
document, and want to process them, then it isn't enough to save the
source
file - you have to relink the workbook to main document anew (pressing Get
Data button, etc.).


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hello Gord and Arvi,
Thanks for the prompt reply.
I am aware how to create the mail merge. What I was after that while the
Master.xls is open is there a possible way to conduct the mail merge...
becoz now I am going thru the following steps
Step 1) make necessary entries in master.xls.. and ofcourse save it.
Step 2) Go to MS Word and Open 20+ different mail merge files to generate
the required letters.

Is there some way of automating this whole process.

Any clue?
Rashid
"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters in

MS
Word. I have designed the Mail Merge file in Word with all the
required
fields...

I wish to know how is it possible for me to generate the Mail Merge
letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and

every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by

clicking
the appropriate button the corresponding Mail Merge letter will be
generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid








  #7   Report Post  
Old December 22nd 04, 08:25 PM
Arvi Laanemets
 
Posts: n/a
Default

Hi

You can have 20+ different mail merge main documents, all based on same
Excel table. In Source table, you can have columns for every different
document type, where you can mark records to be processed for this type of
document.
So you open p.e. mail merge main documnet Doc1, set filter to field Doc1 =
"X" in source table, and generate all letters based on this main document.
Then you open main document Doc2, set filter to Doc2="X", etc.

But principially, mail merge is meant to generate lot of letters of same
design at one go - not exactly what is your task. Another approach will be
20+ report sheets in same workbook, or in separate workbook. In your table
exist some identifier column with unique values. On one report sheet, you
select the row to be processed, and on all report sheet are different
reports for this record displayed.


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hi Arvi,
Sorry my system was down.
Your suggestion is good if it is a single document. I wish to generate

20+
different files, some portrait and some landscape format. All 20+ will

not
be required always... there may be instance when only 1 or 2 files will be
required.. The solution u suggested would generate all 20+ letter which is
not what I require.

What can u suggest in this matter

Thanks for your time and reply

"Arvi Laanemets" wrote in message
...
Hi

Somehow we aren't able to communicate or what ...
1. Make necessary entries in Master.xls. Add a column Selected into

source
table, and enter p.e. "X" for all records yuo want to be processed. Save
the
file.
2. Open predesigned mail merge main document in word, or create a new

main
document with Master.xls as source table.
3. Select Mail Merge from menu (again), and
3.1. Press Query Options. Set filter to Selected="X". Close Options

window
and continue with mail merge - your 20+ documents are
printed/mailed/faxed/created - as you select in next window.
3.2. Press Merge. In Merge window, press Query Options. Set filter to
Selected="X". Close Options window and continue with mail merge - your

20+
documents are printed/mailed/faxed/created - as you select in Merge
window.
When you create a new document, a N*M -page word document is created,
where
n is number of processed records, and m is number of pages for one
document
matching for single row in source file.

From where do you get those 20+ merge files to open?

When you add records into your Excel table after you opened MM main
document, and want to process them, then it isn't enough to save the
source
file - you have to relink the workbook to main document anew (pressing

Get
Data button, etc.).


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hello Gord and Arvi,
Thanks for the prompt reply.
I am aware how to create the mail merge. What I was after that while

the
Master.xls is open is there a possible way to conduct the mail merge...
becoz now I am going thru the following steps
Step 1) make necessary entries in master.xls.. and ofcourse save it.
Step 2) Go to MS Word and Open 20+ different mail merge files to

generate
the required letters.

Is there some way of automating this whole process.

Any clue?
Rashid
"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with

Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters

in
MS
Word. I have designed the Mail Merge file in Word with all the
required
fields...

I wish to know how is it possible for me to generate the Mail Merge
letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and

every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by

clicking
the appropriate button the corresponding Mail Merge letter will be
generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to 20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid










  #8   Report Post  
Old December 24th 04, 06:47 PM
Rashid Khan
 
Posts: n/a
Default

Thanks for the reply.
I would try your suggestion though that is not what I am after.
However, thanks for your time and help
Rashid
"Arvi Laanemets" wrote in message
...
Hi

You can have 20+ different mail merge main documents, all based on same
Excel table. In Source table, you can have columns for every different
document type, where you can mark records to be processed for this type of
document.
So you open p.e. mail merge main documnet Doc1, set filter to field Doc1 =
"X" in source table, and generate all letters based on this main document.
Then you open main document Doc2, set filter to Doc2="X", etc.

But principially, mail merge is meant to generate lot of letters of same
design at one go - not exactly what is your task. Another approach will be
20+ report sheets in same workbook, or in separate workbook. In your table
exist some identifier column with unique values. On one report sheet, you
select the row to be processed, and on all report sheet are different
reports for this record displayed.


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hi Arvi,
Sorry my system was down.
Your suggestion is good if it is a single document. I wish to generate

20+
different files, some portrait and some landscape format. All 20+ will

not
be required always... there may be instance when only 1 or 2 files will
be
required.. The solution u suggested would generate all 20+ letter which
is
not what I require.

What can u suggest in this matter

Thanks for your time and reply

"Arvi Laanemets" wrote in message
...
Hi

Somehow we aren't able to communicate or what ...
1. Make necessary entries in Master.xls. Add a column Selected into

source
table, and enter p.e. "X" for all records yuo want to be processed.
Save
the
file.
2. Open predesigned mail merge main document in word, or create a new

main
document with Master.xls as source table.
3. Select Mail Merge from menu (again), and
3.1. Press Query Options. Set filter to Selected="X". Close Options

window
and continue with mail merge - your 20+ documents are
printed/mailed/faxed/created - as you select in next window.
3.2. Press Merge. In Merge window, press Query Options. Set filter to
Selected="X". Close Options window and continue with mail merge - your

20+
documents are printed/mailed/faxed/created - as you select in Merge
window.
When you create a new document, a N*M -page word document is created,
where
n is number of processed records, and m is number of pages for one
document
matching for single row in source file.

From where do you get those 20+ merge files to open?

When you add records into your Excel table after you opened MM main
document, and want to process them, then it isn't enough to save the
source
file - you have to relink the workbook to main document anew (pressing

Get
Data button, etc.).


Arvi Laanemets


"Rashid Khan" wrote in message
...
Hello Gord and Arvi,
Thanks for the prompt reply.
I am aware how to create the mail merge. What I was after that while

the
Master.xls is open is there a possible way to conduct the mail
merge...
becoz now I am going thru the following steps
Step 1) make necessary entries in master.xls.. and ofcourse save it.
Step 2) Go to MS Word and Open 20+ different mail merge files to

generate
the required letters.

Is there some way of automating this whole process.

Any clue?
Rashid
"Rashid Khan" wrote in message
...
Hello All,
I am using Office 2000 and have a workbook named Master.xls with

Sheet1
having 100+ columns of data running several hundred rows down.

A1, B1, C1....... are headers like Name, LastName, Address1,
Address2............and so on

I want to use Master.xls for generating about 20+ mail merge letters

in
MS
Word. I have designed the Mail Merge file in Word with all the
required
fields...

I wish to know how is it possible for me to generate the Mail Merge
letters
in MS Word while I am working on the Master.xls file.

1) Do I need to close the Master.xls file and then generate each and
every
letter in Word?
2) Do I need to make 20 odd Command Buttons in Master.xls and by
clicking
the appropriate button the corresponding Mail Merge letter will be
generated
(I am not sure whether this is possible while Master.xls is open!)
3) Do I need to extract the relevant data from Master.xls file to
20+
different Worksheet and then create Mail Merge accordingly..
I am totally confused how to go about this problem.

Any suggestions, lead in this matter would be greatly appreciated.

TIA

Rashid














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Charting data ranges that change mikelee101 Charts and Charting in Excel 2 December 17th 04 12:07 AM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 12:56 PM
Extract Data Tucson Guy Excel Discussion (Misc queries) 5 December 15th 04 10:41 PM
how do i merge 2 rows of data into one willid Excel Discussion (Misc queries) 3 November 30th 04 08:43 PM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017