I have an excel workbook with a number of named cells and a word template with a number of bookmarks; both are 2007 versions.

The worbook will always be opened from different locations whereas the word template is always opened from the same location.

I want the macro to save the word doc in the folder where the excel workbook is saved, and name the word doc based on the value in a specific named range in the workbook followed by the current year e.g the template is saved in C:\Administration\Templates, but if I run the workbook from C:\Administration\Test, and enter the value "Test" into the named range then I want the word doc to save in C:\Administration\Test with the file name "Test 2013.docx". Any help you can provide would be most appreciated, so does anyone have any bright ideas?

My code is as follows (I've included the entire sub in case something needs to be added at the start):

Sub CertGenerator()

On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range

Dim Name As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Set myDoc = wdApp.Documents.Add(Template:="C:\Administration\Templates\template 2013.docx")

Set Name = Sheets("Data Sheet").Range("Name")

With myDoc.Bookmarks

.Item("Name").Range.InsertAfter Name


End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub