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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Macro help please: Excel data to word doc
|
Thread Tools | |
Display Modes | |
|
|
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 06:25 PM |