Inserting Excel data into Word Using Word Macro

Posted on 2009-12-29
Last Modified: 2013-11-27
I am trying to insert an Excel table into Word, automated by using a Word Macro.  I have been able to do this by using the insert database command.  However, the Excel file is not static (as shown below; e.g., rec10.xls), it will vary.  Can you tell me how to get my variable, MyValue, into the command below so I insert the Excel table that corresponds to my Word document?  Thank you.

Dim Message, Title, Default, MyValue
Message = "Enter Record # and Press OK"    ' Set prompt.
Title = "Enter Rec#"    ' Set title.
MyValue = InputBox(Message, Title)

    Selection.InsertBreak Type:=wdSectionBreakNextPage

    Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
        Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=R:\fmExports\WOOD_SETUPS\RENTROLLS\rec10.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet " _
        , SQLStatement:="SELECT * FROM `Sheet1$`" & "", PasswordDocument:="", _
        PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
        "", DataSource:="R:\fmExports\WOOD_SETUPS\RENTROLLS\rec10.xls", From:=-1, _
         To:=-1, IncludeFields:=True
Question by:rvfowler2
    LVL 4

    Expert Comment

    LVL 2

    Author Comment

    Will try it tomorrow.  However, I can't see where you're referencing the Excel file, unless it is Myworkbook.  However, it looks like the Excel file would have to be open.  I prefer to simply have my Word macro select the correct Excel file via a variable (by the record#) and insert the Excel data in.
    LVL 4

    Expert Comment

    No problem will look forward to your reply  FYI.. View line #8
    'initialize the Word template path
         'here, it's set to be in the same directory as our source workbook
        myWordFile = ThisWorkbook.Path & "\"
         'get the range of the contiguous data from Cell A1
        Set rng = Range("A1").CurrentRegion
    LVL 2

    Author Comment

    Followed all instructions but received the error "variable not defined" on ThisWorkbook
    LVL 2

    Accepted Solution

    I figured this one out.  I actually wanted a simpler solution.  I can either do an Insert Database or Insert Object from Word and then automate it using a Word Macro.  Did this successfully Fri, Dec 31.   I think the question was too simple for you guys.  Thanks, anyway.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now