• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Inserting Excel data into Word Using Word Macro

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
0
rvfowler2
Asked:
rvfowler2
  • 3
  • 2
1 Solution
 
cd_morrisCommented:
0
 
rvfowler2Author Commented:
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.
0
 
cd_morrisCommented:
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 & "\DocWithTableStyle.dot"
     
     'get the range of the contiguous data from Cell A1
    Set rng = Range("A1").CurrentRegion
0
 
rvfowler2Author Commented:
Followed all instructions but received the error "variable not defined" on ThisWorkbook
0
 
rvfowler2Author Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now