Macro help please: Excel data to word doc

[anna-maria]

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, re: 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.

 

[Ann Scharpf]

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, re: 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.

 

[Ann Scharpf]

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.

 

[anna-maria]

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!

 

[AlfD]

Hi!

Perhaps you could post your macro?

Al