Rich,

The subroutine below should do close to what you want. For testing, I
created a Word template named TestX.dot and placed it in my default location
for Word templates (if you do otherwise, you will need to full path to the
dot file rather than just the template name in the sub). The template
contains a bookmark named "myBookmarkA" where I chose to have the date of
document creation inserted but you don't need that if you don't want to
automatically insert items into the document you're creating. If you want,
you can make the Word document visible to allow the user to make
modifications. The routine assumes the existence of a folder named C:\Test
which is where the new Word documents will be saved to.

When you open the VB editor window, go to 'Tools References' and make sure
to set a reference to the Microsoft Word Object Library. This code need to
be placed into the particular sheet where you will be working.

Whenever you click the cell in column A that is immediately below the
previously filled cell, the cell you clicked in A will be filled with a
number one greater than the cell above it, the cell to its right will be
populated with the current date, the new Word document will be created and
saved to the folder "C:\Test" and the cell to the right in column C will be
populated with a hyperlink to the newly created Word document.

_______________________________

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim NewTopRow As Long
Dim strDocName As String
Dim strTxtInsert As String
Dim bStarted As Boolean
Dim oApp As Word.Application

NewTopRow = ActiveSheet.UsedRange.Rows.Count + 1

If Target.Address = "$A$" & CStr(NewTopRow) Then
Application.EnableEvents = False
Cells(NewTopRow, 1).Value = Cells(NewTopRow - 1, 1).Value + 1
Cells(NewTopRow, 2).Value = Date
strDocName = "RFI-" & CStr(Cells(NewTopRow, 1).Value)
strTxtInsert = Format$(Date, "mmm, d, yyyy")

On Error Resume Next
Set oApp = GetObject(, "Word.Application")
If Err < 0 Then
bStarted = True
Set oApp = CreateObject("Word.Application")
End If

oApp.Activate
oApp.Documents.Add Template:="TestX.dot"
oApp.ActiveDocument.Bookmarks("MyBookmarkA").Range .InsertAfter
strTxtInsert
oApp.ActiveDocument.SaveAs "C:\Test\" & strDocName & ".doc"
oApp.ActiveDocument.Close wdSaveChanges

' Quit Word, only if it wasn't running when this code was started
If bStarted Then
oApp.Quit
End If

ActiveSheet.Hyperlinks.Add Anchor:=Cells(NewTopRow, 3), _
Address:="C:\Test\" & strDocName & ".doc", TextToDisplay:=strDocName

Application.EnableEvents = True
End If
End Sub

______________________________

Steve




"RealmSteel" wrote in message
...
What I am trying to create is a Request for information log.

My ultimate solution is as follows:
When cell A2 is clicked, it will change it's value to the next consecutive
number from the cell above. B2 would then have the date inserted into it.
A
Word template file would open and automatically save as a .DOC with "RFI-"
and the number from A2. The Word file can be edited and then just saved.
A
hyperlink would then be created in C2 allowing you to open that word file
later to view it.

At the very least, I'd like to be able to click cell...say D2 and open the
word template file to where when Save is selected it will allow you to
save
as a DOC file without having to change the file type. It seems when I use
a
hyperlink to the template and hit save, it will just overwrite the
template.
I changed the template to read only, but it still tries to save as a
templete
type file. The hyper link seems to open the template file differently
than
dbl-clicking it in windows explorer.

Sorry it's soo long of a question.
Rich