View Full Version : Pull excel data in to word

05-06-2015, 03:10 PM
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.

05-19-2015, 02:19 PM
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

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