Consulting

Results 1 to 2 of 2

Thread: Pull excel data in to word

  1. #1
    VBAX Regular
    Joined
    May 2015
    Posts
    9
    Location

    Question 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.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,717
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •