Pull excel data in to word

Hi,
I am currently trying to build a word template that will pull data directly from an excel file and deposit the values in to a table. I have worked out how to do this with some VBA code if I specifically reference the path and file name of the doc which has the data. On clicking a button that I place in the ribbon, the table updates. It works well and achieves my goals.

The issue that I have however is that I want the file to be a template and hence function time after time without any further intervention. Given that the excel file name will be different on each use, the VBA code will immediately error. The word and excel files will however always have the same name and will simply differ by the suffix. Is it therefore possible to code for the following scenario:

word file with table is called ABCDE.doc and will need to pull data from ABCDE.xls or
word file with table is called 12345.doc and will need to pull data from 12345.xls or
word file with table is called asdfg.doc and will need to pull data from asdfg.xls....etc...etc

I am an enthusiastic newbie so please be gentle if you are able to help.

You might have more responses if you posted this in the MS Word forum, instead of the Ribbon forum

More Word guys hang on there


But try something like this

Code:

---

Option Explicit
Sub demo()
    Dim sDoc As String, sXls As String
    Dim i As Long
   
    sDoc = ActiveDocument.FullName
    i = InStrRev(sDoc, ".")
    sXls = Left(sDoc, i) & "xls"
   
    MsgBox sDoc
    MsgBox sXls

End Sub

---