MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question


Posted by Jereme on August 14, 2001 7:17 PM

Hi folks,

I'm wondering if there is a way to set up the following sequence of events in Excel:

If any chosen cell remains empty for two weeks from a date entered into another cell, a document in Word automatically prints out.

Any suggestions?

Thanks,

Jereme

PS
I'm using Excel 97.


Posted by Dax on August 15, 2001 3:14 AM

There certainly is. Do this:-

Open the Visual Basic editor (Alt F11), click View, Project Explorer. From the Project Explorer double click the ThisWorkbook icon. This will open a code window where you can program things to happen when a workbook opens, closes, deactivates, etc. Choose Workbook from the left hand drop down box you can see.

This will create a blank procedure for the Workbook_Open event. I'm assuming that the sheet on which the date is entered is on sheet1 in cell A1 but you can change this accordingly. Now add this code:-

Private Sub Workbook_Open()
Dim sSheetName As String
Dim rngeDate As Range
Dim dteInitialDate As Date
Dim wdApp As Word.Application, wdDoc As Document

sSheetName = "Sheet1" 'Change this to whatever you want
Set rngeDate = Sheets(sSheetName).Range("A1")

dteInitialDate = rngeDate.Value

'Is the current date more than 14 days past the date on sheet1 cell A1?
If Date > dteInitialDate + 14 Then
'Now print a Word document out. Change the filename accordingly.
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\Temp\Doc1.doc")
wdDoc.PrintOut
wdApp.Quit False
Set wdDoc = Nothing
Set wdApp = Nothing
End If
End Sub

HTH,
Dax.


Posted by Dax on August 15, 2001 3:18 AM

Forgot to say this. In the VB editor you must set a reference to the Word object library. Click Tools, References and then search for Microsoft Word n.0 Object Library and check the box. This simply makes Word available for use by an external application (in this case Excel).

Regards,
Dax.

Posted by Jereme on August 15, 2001 9:58 PM


Dax,

Thank you very much for your help. I really appreciate it.

I have a couple of more questions if you don't mind?

1. Do I need to type out the code precisely as you have it? Same spacing, caps,etc.? I keep getting an error message when I try to type the > symbol and/or the word "Then" in the line:
If Date>dteInitialDate + 14 Then

2. Will this macro achieve the following?:
If I enter a date in sheet 1 cell G5, and two weeks passes by without me entering a date in sheet 1 cell G6, then a Word document will automaticaly print out? However, if I enter a date in G6 before the two weeks is up, nothing will happen?

Thanks again for your time.

Jereme