![Quote](https://www.excelforum.com/images/misc/quote_icon.png)
Originally Posted by
drosen99
I actually I have three different templates that the end user can choose from at the end of the row.
How is that choice made (eg does the hyperlink cell they click on determine which template should be used, or should the option to choose a template be coded into the macro)? Obviously, as coded, the macro only opens a single, predefined template, extra code will be required to provide for different templates and, if the the hyperlink cell they click on determines which template should be used, you'll need rules about which cell relates to what template.
To give the user a choice, you could use code like:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim iRow As Long, LCol As Long, iCol As Long, wdCol As Long
Dim iChoice As Long, StrTmplts As String, StrList As String, i As Long
StrTmplts = "Template1,Template2,Template3"
On Error GoTo ErrExit
For i = 0 To UBound(Split(StrTmplts, ","))
StrList = StrList & vbTab & i + 1 & " - " & Split(StrTmplts, ",")(i) & vbCr
Next
iChoice = InputBox("Please choose a template:" & vbCr & StrList, "Template Selection")
If iChoice < 1 Or iChoice > UBound(Split(StrTmplts, ",")) + 1 Then
MsgBox "Invalid template choice.", vbExclamation
Exit Sub
End If
On Error GoTo 0
iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
Dim wdApp As Word.Application
Set wdApp = New Word.Application
Dim wdDoc As Word.Document
Set wdDoc = wdApp.Documents.Add(Template:=Split(StrTmplts, ",")(iChoice - 1) & ".dotx")
wdApp.Visible = True
With wdDoc.Tables(1).Rows.Last
If LCol > .Cells.Count Then
wdCol = .Cells.Count
Else
wdCol = LCol
End If
For iCol = 1 To wdCol
.Cells(iCol).Range.Text = ActiveSheet.Cells(iRow, iCol).Value
Next
End With
Set wdDoc = Nothing: Set wdApp = Nothing
ErrExit:
End Sub
where each template's name is input into the StrTmplts variable. The code is flexible enough to allow at least 20 templates.
Also, when the template opens up how do I make sure the information from the "last active row" is populated into the template?
Using 'iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row', the macro already does that.
Do I use paste special links between excel and word?
Not unless you want to establish a permanent link between the document and the cells. Doing so would cause the document content to change if you later changed the cell content, but could result in errors if the document is moved to a different PC or the workbook is renamed or moved to a different folder.
Bookmarks