General discussion

Locked

Open a Word Doc when a cell is populated

By matesfm ·
Looking for some help on opening a word doc when a cell (that appears blank) in Excel is populated with a character (i.e. X). I have a spredsheet that will be periodically updated and need to send confirming letter at that time. Thanks in advance.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by bschaettle In reply to Open a Word Doc when a ce ...

It will be easier to do it the other way around: use the Excel spreadsheet as a data source for a Word Mail Merge to generate the letters. You'll need to add another column to your spreadsheet called "Letter Sent" that will have values of Yes or No. You can set up a data validation rule to restrict the input on these cells to just these two values.

With Word, set up a letter template to mail merge only those records that have the "X" marked, and the Letter Sent column NOT equal to "Yes". After the letters are printed, re-open the spreadsheet and mark off the records that letters for which letters have been sent. I know this sounds like a pain, but it's about the simplest solution.

We've sent out thousands of letters to clients using a system like this. The spreadsheet is also a good tracking tool to see who letters have been sent to.

Collapse -

by matesfm In reply to

Poster rated this answer.
We only send 1 letter periodically while following the progress of a job. Still wonder if there is a method to populate a cell and have a letter printed automatically.

Collapse -

by rkuhn In reply to Open a Word Doc when a ce ...

Do it all in MS Access.

1) Design a form for data input
2) Have a report for the mail merge
3) Run a query to mark those sent

All automated, except the data entry. For that matter, using an ODBC connection you might be able to connect to your other data sources and avoid data entry too.

Collapse -

by matesfm In reply to

Poster rated this answer.
Thanks for the input.

Collapse -

by bschaettle In reply to Open a Word Doc when a ce ...

Oh, very well, we'll do it your way. Open your spreadsheet, open the VBA Editor, and "View Code" for the Worksheet object. Now paste in the code shown below. This will print a Word document whenever an "x" is typed into Column A.

---------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wdApp As Object
If Target.Cells(1, 1).Column = 1 Then ' Column "A"
If UCase(Target.Cells(1, 1).Value) = "X" Then
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
wdApp.Documents.Open "C:\folder\test Document.doc"
wdApp.ActiveDocument.PrintOut
DoEvents
' wait 5 seconds for the letter to print
Application.Wait (Now + TimeValue("0:00:5"))
wdApp.ActiveDocument.Close
wdApp.Quit
Set wdApp = Nothing
End If
End If
End Sub

Collapse -

by matesfm In reply to

Poster rated this answer.
Thanks for the help.

Collapse -

by matesfm In reply to Open a Word Doc when a ce ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums