A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Macro help please: Excel data to word doc



 
 
Thread Tools Display Modes
  #1  
Old June 28th, 2004, 06:36 PM
anna-maria
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

Hello,

I've read through all of the other posts regarding this matter and it's still not working. Here's my situation:

I have created an excel file in which sheet 1, named "customer info", is where information is entered, and automatically transferred to appropriate cells throughout the rest of the workbook (ex. ='customer info'!a1). One worksheet is in the form of a letter; however, the user of this file is slightly excel-illiterate and he's finding this method to be a pain when the letter exceeds a single page (closing is autoformatted at the bottom of page 1). Having this person insert rows or otherwise is not an option (lazy, i know) and was trying to create a macro in which the necessary data (addressee info, info, etc.) can be transferred to a word document in a letter form. I was able to find a macro in which clicking a button on the excel worksheet automatically opens a blank (saved as "WordReportLetter") in Word. This seems to work fine.
From the blank Word doc, I have tried to do a mail merge (I was going to create a separate macro in Word for this) using the Excel data (i inserted a new "sheet1" at the beginning of my workbook, as suggested, and entered labels in Row 1 and then formulas to receive data from "customer info" sheet in Row 2, I've also named these cells, as suggested). The mail merge works fine.
However, when I close everything down and try to run the Excel macro again, it opens up a duplicate of my Excel workbook, instead of the Word doc. It seems that this macro only works if my Word doc is left blank, without mail merge fields.

PLEASE HELP!

Even better, if someone can help me with a single macro to run from a button on the "Report Letter" worksheet (outside the page margins so it doesn't show) to open up a Word doc which holds the same format, that would be great. All of this is basically just so this guy can write a two page letter in Word with all of the Addressee, reference info, open/close paragraphs already entered.

Thanks so much.
  #2  
Old June 28th, 2004, 07:15 PM
Ann Scharpf
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

Anna-Maria:

FORGET Excel!!! Do this whole letter in Word!!!! This is
a word processing task, not a spreadsheet task.

If need be, you can still keep the "field" information in
an Excel worksheet. Enter all the text of the letter in
Word. You do not specify which version of Office you are
running. Unbelieveably, here at work I am on 97 so, if
you are on XP, these steps may not exactly match what you
will see, but you will get the idea...

First of all, make sure that your Excel worksheet is set
up like a database. Column headings with "field names"
and one row per person/form letter.


1. Create a new word document.
2. Click Tools Mail Merge.
3. Under Main Document, click Create.
4. Select Form Letters then Active Window
5. Under Data Source, Click Get Data.
6. Change "Files of Type" to be Excel spreadsheets.
7. Browse to the Excel Workbook with the data table.
8. Select "Entire spreadsheet" and click OK
9. You will see a message saying Word found no fields in
the main document. That just means you haven't linked the
files yet. It is NOT an error.
10. Paste the main text of the letter into the document.
11. Click at the point in the document where you want a
field to be merged. Click the Insert Merge Field button
and Word will list all the column headings from your Excel
sheet.
12. When you are ready to print the document, mouse over
the buttons in the Mail/Merge toolbar (Word automatically
put it into your document when you made it a mail merge.)
You will see buttons for merge to printer, merge to
document, and so on.

Look at the Word Help for mail/merge.

Hope this helps.

Ann Scharpf



-----Original Message-----
Hello,

I've read through all of the other posts regarding this

matter and it's still not working. Here's my situation:

I have created an excel file in which sheet 1,

named "customer info", is where information is entered,
and automatically transferred to appropriate cells
throughout the rest of the workbook (ex. ='customer info'!
a1). One worksheet is in the form of a letter; however,
the user of this file is slightly excel-illiterate and
he's finding this method to be a pain when the letter
exceeds a single page (closing is autoformatted at the
bottom of page 1). Having this person insert rows or
otherwise is not an option (lazy, i know) and was trying
to create a macro in which the necessary data (addressee
info, info, etc.) can be transferred to a word
document in a letter form. I was able to find a macro in
which clicking a button on the excel worksheet
automatically opens a blank (saved as "WordReportLetter")
in Word. This seems to work fine.
From the blank Word doc, I have tried to do a mail merge

(I was going to create a separate macro in Word for this)
using the Excel data (i inserted a new "sheet1" at the
beginning of my workbook, as suggested, and entered labels
in Row 1 and then formulas to receive data from "customer
info" sheet in Row 2, I've also named these cells, as
suggested). The mail merge works fine.
However, when I close everything down and try to run the

Excel macro again, it opens up a duplicate of my Excel
workbook, instead of the Word doc. It seems that this
macro only works if my Word doc is left blank, without
mail merge fields.

PLEASE HELP!

Even better, if someone can help me with a single macro

to run from a button on the "Report Letter" worksheet
(outside the page margins so it doesn't show) to open up a
Word doc which holds the same format, that would be
great. All of this is basically just so this guy can
write a two page letter in Word with all of the Addressee,
reference info, open/close paragraphs already entered.

Thanks so much.
.

  #3  
Old June 28th, 2004, 07:49 PM
anna-maria
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

Ann...thanks for your reply.

I know how to do a mail merge in Word, the problem is that I am catering to a very lazy, computer-illiterate person here. Each of our clients has their own workbook in Excel, which holds a number of sheets for different purposes. I agree that the best way to write a letter is in Word; however, the bulk of our tasks makes more sense in Excel, and my boss would prefer everything together in one place (have his cake and eat it too), or rather, one excel workbook. Anyways, it is possible to do a letter in Excel so I have done so and it's fine. The letter is already formatted with opening/closing text in one large, page-sized column (to enable text wrapping for the body of the letter). The body of the letter (which they type themselves) is placed in one single, page-sized, text-wrapping cell. Another way to fix this problem would be if they could hit enter to begin a new paragraph within this cell, instead of automatically transferring to the cell below. Is there a way to create spaces between paragraphs within a single cell?

Otherwise, the file they work from is in an Excel workbook, so they would prefer if there was a link within the workbook that would open a blank word document with the opening/closing text autmatically inserted from the Excel workbook.

I know it's a lot to ask (or is it? maybe i'm just not that bright! haha).

Thanks!

"Ann Scharpf" wrote:

Anna-Maria:

FORGET Excel!!! Do this whole letter in Word!!!! This is
a word processing task, not a spreadsheet task.

If need be, you can still keep the "field" information in
an Excel worksheet. Enter all the text of the letter in
Word. You do not specify which version of Office you are
running. Unbelieveably, here at work I am on 97 so, if
you are on XP, these steps may not exactly match what you
will see, but you will get the idea...

First of all, make sure that your Excel worksheet is set
up like a database. Column headings with "field names"
and one row per person/form letter.


1. Create a new word document.
2. Click Tools Mail Merge.
3. Under Main Document, click Create.
4. Select Form Letters then Active Window
5. Under Data Source, Click Get Data.
6. Change "Files of Type" to be Excel spreadsheets.
7. Browse to the Excel Workbook with the data table.
8. Select "Entire spreadsheet" and click OK
9. You will see a message saying Word found no fields in
the main document. That just means you haven't linked the
files yet. It is NOT an error.
10. Paste the main text of the letter into the document.
11. Click at the point in the document where you want a
field to be merged. Click the Insert Merge Field button
and Word will list all the column headings from your Excel
sheet.
12. When you are ready to print the document, mouse over
the buttons in the Mail/Merge toolbar (Word automatically
put it into your document when you made it a mail merge.)
You will see buttons for merge to printer, merge to
document, and so on.

Look at the Word Help for mail/merge.

Hope this helps.

Ann Scharpf



-----Original Message-----
Hello,

I've read through all of the other posts regarding this

matter and it's still not working. Here's my situation:

I have created an excel file in which sheet 1,

named "customer info", is where information is entered,
and automatically transferred to appropriate cells
throughout the rest of the workbook (ex. ='customer info'!
a1). One worksheet is in the form of a letter; however,
the user of this file is slightly excel-illiterate and
he's finding this method to be a pain when the letter
exceeds a single page (closing is autoformatted at the
bottom of page 1). Having this person insert rows or
otherwise is not an option (lazy, i know) and was trying
to create a macro in which the necessary data (addressee
info, info, etc.) can be transferred to a word
document in a letter form. I was able to find a macro in
which clicking a button on the excel worksheet
automatically opens a blank (saved as "WordReportLetter")
in Word. This seems to work fine.
From the blank Word doc, I have tried to do a mail merge

(I was going to create a separate macro in Word for this)
using the Excel data (i inserted a new "sheet1" at the
beginning of my workbook, as suggested, and entered labels
in Row 1 and then formulas to receive data from "customer
info" sheet in Row 2, I've also named these cells, as
suggested). The mail merge works fine.
However, when I close everything down and try to run the

Excel macro again, it opens up a duplicate of my Excel
workbook, instead of the Word doc. It seems that this
macro only works if my Word doc is left blank, without
mail merge fields.

PLEASE HELP!

Even better, if someone can help me with a single macro

to run from a button on the "Report Letter" worksheet
(outside the page margins so it doesn't show) to open up a
Word doc which holds the same format, that would be
great. All of this is basically just so this guy can
write a two page letter in Word with all of the Addressee,
reference info, open/close paragraphs already entered.

Thanks so much.
.


  #4  
Old June 28th, 2004, 07:56 PM
Ann Scharpf
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

Oh man! What a lot of hassle to avoid using the proper
tool for the task! You have my sympathy.

And, yes, you can insert line feeds inside a cell. Just
press ALT-Enter to get a new line. Do it twice to insert
a blank space between paragraphs in the one-cell "letter."

Ann
-----Original Message-----
Ann...thanks for your reply.

I know how to do a mail merge in Word, the problem is

that I am catering to a very lazy, computer-illiterate
person here. Each of our clients has their own workbook
in Excel, which holds a number of sheets for different
purposes. I agree that the best way to write a letter is
in Word; however, the bulk of our tasks makes more sense
in Excel, and my boss would prefer everything together in
one place (have his cake and eat it too), or rather, one
excel workbook. Anyways, it is possible to do a letter in
Excel so I have done so and it's fine. The letter is
already formatted with opening/closing text in one large,
page-sized column (to enable text wrapping for the body of
the letter). The body of the letter (which they type
themselves) is placed in one single, page-sized, text-
wrapping cell. Another way to fix this problem would be
if they could hit enter to begin a new paragraph within
this cell, instead of automatically transferring to the
cell below. Is there a way to create spaces between
paragraphs within a single cell?

Otherwise, the file they work from is in an Excel

workbook, so they would prefer if there was a link within
the workbook that would open a blank word document with
the opening/closing text autmatically inserted from the
Excel workbook.

I know it's a lot to ask (or is it? maybe i'm just not

that bright! haha).

Thanks!


  #5  
Old June 28th, 2004, 08:47 PM
anna-maria
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

whaaat?! oh, i wish i had asked that question earlier, thanks so much! i couldn't figure out how to do it! i'll make them use that method instead. heaven forbid they should have to press "alt" at the same time as "enter"! well, now they'll just have to.

thanks so much!

"Ann Scharpf" wrote:

Oh man! What a lot of hassle to avoid using the proper
tool for the task! You have my sympathy.

And, yes, you can insert line feeds inside a cell. Just
press ALT-Enter to get a new line. Do it twice to insert
a blank space between paragraphs in the one-cell "letter."

Ann
-----Original Message-----
Ann...thanks for your reply.

I know how to do a mail merge in Word, the problem is

that I am catering to a very lazy, computer-illiterate
person here. Each of our clients has their own workbook
in Excel, which holds a number of sheets for different
purposes. I agree that the best way to write a letter is
in Word; however, the bulk of our tasks makes more sense
in Excel, and my boss would prefer everything together in
one place (have his cake and eat it too), or rather, one
excel workbook. Anyways, it is possible to do a letter in
Excel so I have done so and it's fine. The letter is
already formatted with opening/closing text in one large,
page-sized column (to enable text wrapping for the body of
the letter). The body of the letter (which they type
themselves) is placed in one single, page-sized, text-
wrapping cell. Another way to fix this problem would be
if they could hit enter to begin a new paragraph within
this cell, instead of automatically transferring to the
cell below. Is there a way to create spaces between
paragraphs within a single cell?

Otherwise, the file they work from is in an Excel

workbook, so they would prefer if there was a link within
the workbook that would open a blank word document with
the opening/closing text autmatically inserted from the
Excel workbook.

I know it's a lot to ask (or is it? maybe i'm just not

that bright! haha).

Thanks!



  #6  
Old June 30th, 2004, 11:53 PM
AlfD
external usenet poster
 
Posts: n/a
Default Macro help please: Excel data to word doc

Hi!

Perhaps you could post your macro?

Alf


---
Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge help again DrB Worksheet Functions 4 June 20th, 2004 05:59 PM
How to run word and pass a mail merge values and fax it to the recipient Belinda Mailmerge 2 June 13th, 2004 12:49 AM
Data Missing in Excel & Word files Sam General Discussions 0 May 25th, 2004 03:21 AM
Merge to Fax with SBS/Exchange? Tom Mailmerge 3 May 18th, 2004 11:22 PM
Using a Excel macro or VB to call up a WORD mail merge document Bob Reynolds Worksheet Functions 0 January 7th, 2004 07:25 PM


All times are GMT +1. The time now is 01:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.