Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
I have 5 cells in a row (DOLLAR AMOUNT in numbers, DOLLAR AMOUNT i words, TAX, TOTAL, INVOICE ID) that I want to send to WORD to do m invoice. How can I either 1) Activate a form letter with the selected data t populate or 2) create a new letter (with letterhead) with the data fro excel. I am familiar with VBA and actuall have code to convert numbers t words so I thought I would just "call on WORD" to create my invoice fo mailing. Any advice or help would be great and if you need more info, let m know.. Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
Beau,
Write your Word doc with formfields in the places you want to insert data. (Don't use mergefields). Set the bookmarks for these fields as DollarN, DollarW,Tax,Total and ID Save your Word Doc in the same folder as your XL program is. (Not necessary, but it's easier that way) In XL Private Sub MyDocPrint() Dim appWD As Word.Application 'Set-up appWD Set appWD = CreateObject("Word.Application.9") 'Open M.S. Word appWD.Visible = False 'Hide word window (if you want) appWD.ChangeFileOpenDirectory ActiveWorkbook.Path 'Word looks here for file appWD.Documents.Open Filename:="YourDocNameHere", ConfirmConversions:=False, ReadOnly:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto 'Open doc With ActiveSheet appWD.ActiveDocument.FormFields("DollarN").Select appWD.ActiveDocument.FormFields("DollarN").Result = .Cells(3, 1).Value appWD.ActiveDocument.FormFields("DollarW").Select appWD.ActiveDocument.FormFields("DollarW").Result = .Cells(3, 2).Value appWD.ActiveDocument.FormFields("Tax").Select appWD.ActiveDocument.FormFields("Tax").Result = .Cells(3, 3).Value appWD.ActiveDocument.FormFields("Total").Select appWD.ActiveDocument.FormFields("Total").Result = .Cells(3, 4).Value appWD.ActiveDocument.FormFields("ID").Select appWD.ActiveDocument.FormFields("ID").Result = .Cells(3, 5).Value End With DoEvents appWD.PrintOut 'Print letter Application.Wait Time + TimeValue("00:00:05") '5 seconds delay to let printing finish appWD.DisplayAlerts = wdAlertsNone 'Turn off alerts appWD.Quit SaveChanges:=False End Sub HTH Henry "Beau" wrote in message ... I have 5 cells in a row (DOLLAR AMOUNT in numbers, DOLLAR AMOUNT in words, TAX, TOTAL, INVOICE ID) that I want to send to WORD to do my invoice. How can I either 1) Activate a form letter with the selected data to populate or 2) create a new letter (with letterhead) with the data from excel. I am familiar with VBA and actuall have code to convert numbers to words so I thought I would just "call on WORD" to create my invoice for mailing. Any advice or help would be great and if you need more info, let me know.. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
Henry.. Awesome.. thank you so much for the reply. I have not incorporated i yet, but reading it over, it makes total sense. Appreciate your time and effort! One stupid thing.. How can I get a macro / vba script to run everytime I LEAVE a cell i COLUMN 3 (Either by ENTER or a click)... C is where my NUMBER is and want the conversion script to convert it to WORDS and write to th adjacent cell in D. From my VB days, I know I am looking for something like "LostFOCUS" bu I just cannot find it for a paticular cell! Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
Beau,
Could you turn your 'NumbersToText' sub into a function? Call the function from the cell in Col D and reference it to the cell in Col C That way it will automatically update when a valid number is put into the cell in Col C HTH Henry "Beau" wrote in message ... Henry.. Awesome.. thank you so much for the reply. I have not incorporated it yet, but reading it over, it makes total sense. Appreciate your time and effort! One stupid thing.. How can I get a macro / vba script to run everytime I LEAVE a cell in COLUMN 3 (Either by ENTER or a click)... C is where my NUMBER is and I want the conversion script to convert it to WORDS and write to the adjacent cell in D. From my VB days, I know I am looking for something like "LostFOCUS" but I just cannot find it for a paticular cell! Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
Henry.. I know this is probably the wrong website but it is worth a try... Is there any way to Open WORD and populate the TEMPLATE FORM with results from a query? I cut and pasted the exact code you wrote for excel with obvious problems. In trying to change the code so that it refers to an "ACCESS SESSION" is not... well.. I have no clue what I am doing. I understand activesession in Excel and even Word for that matter. However.. my search for the same in ACCESS is not working. Any pointers on how I will llink the FORMS in words to the QUERY result? lets assume I have a simple query that pulls PRICE AND a NAME from an INVOICE NUMBER and I am working with the fields: INVOICE, PRICE and NAME. I know this is probably WAY vague but I will be happy to provide any more info. THanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Letter in Word from Select Excel data
Beau,
I didn't realise you were calling Word from Access! I don't know much about Access so I can only give you some general VBA hints here. Make sure the Doc is saved in the same directory as your DB project file. Copy and paste my code into Access VBA Go Tools References and tick the reference to Microsoft Word (9 for Word 2000, 10 for Word XP) Change the line appWD.Visible = False to appWD.Visible = True This is so you can see Word open and then the document open. Change the line appWD.ChangeFileOpenDirectory ActiveWorkbook.Path 'Word looks here for file to appWD.ChangeFileOpenDirectory Application.CurrentProject..Path 'Word looks here for file Comment out all the lines from With ActiveSheet appWD.ActiveDocument.FormFields("DollarN").Select appWD.ActiveDocument.FormFields("DollarN").Result = .Cells(3, 1).Value appWD.ActiveDocument.FormFields("DollarW").Select appWD.ActiveDocument.FormFields("DollarW").Result = .Cells(3, 2).Value appWD.ActiveDocument.FormFields("Tax").Select appWD.ActiveDocument.FormFields("Tax").Result = .Cells(3, 3).Value appWD.ActiveDocument.FormFields("Total").Select appWD.ActiveDocument.FormFields("Total").Result = .Cells(3, 4).Value appWD.ActiveDocument.FormFields("ID").Select appWD.ActiveDocument.FormFields("ID").Result = .Cells(3, 5).Value End With To here ... and then run it from Access. Word will open and then the Doc. Then the Doc will print out with the fields blank ('cos we've put nothing in them!). You'll then have to find out how to put the results of your Query into the form fields. My knowledge of AccessVBA runs out here. You'll have to figure it out yourself or try one of the Access Newsgroups. Probably, you can keep most of the commented out lines but you'll need to change the Result = .Cells(X,Y).Value bits to something else and the With ActiveSheet line as well. Sorry I can't be of more help. Henry "Beau" wrote in message ... Henry.. I know this is probably the wrong website but it is worth a try... Is there any way to Open WORD and populate the TEMPLATE FORM with results from a query? I cut and pasted the exact code you wrote for excel with obvious problems. In trying to change the code so that it refers to an "ACCESS SESSION" is not... well.. I have no clue what I am doing. I understand activesession in Excel and even Word for that matter. However.. my search for the same in ACCESS is not working. Any pointers on how I will llink the FORMS in words to the QUERY result? lets assume I have a simple query that pulls PRICE AND a NAME from an INVOICE NUMBER and I am working with the fields: INVOICE, PRICE and NAME. I know this is probably WAY vague but I will be happy to provide any more info. THanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
best way to put list data from Excel to a Word form | Excel Discussion (Misc queries) | |||
merge data into form letter | Excel Discussion (Misc queries) | |||
Excel Form attached to a Word Document Letter | Excel Discussion (Misc queries) | |||
Form Letter and Embeded Word Object | Excel Discussion (Misc queries) | |||
can i select from list by typing first letter of word? | Excel Discussion (Misc queries) |