Form Letter in Word from Select Excel data

  • Thread starter Thread starter Beau
  • Start date Start date
B

Beau

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
 
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
 
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
 
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
 
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!
 
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
 
Back
Top