Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Arizona, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read Excel Data into Word (VBA)

    Need to read excel data into my word Doc and I have it working so far except for an annoying conversion pop up screen. No matter what I do the screen pops up. Is there another way to do this so that I do not have to open the Excel file as an open window? Or is there a way to bypass the pop up screen. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31> Any help is appreciated. Here is what I have so far.
    Thanks in advance.


    Documents.Open FileName:="C:MyFolderMyExelFile.xls", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, Visible:=True

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read Excel Data into Word (VBA)

    The code you posted will open the Excel workbook in a new window. If you want to insert it into the active document, you must use the InsertFile method of the Selection (or a Range object). This, however, will present the popup dialog too, unless you specify the range to insert. You can "click" OK in the dialog by using SendKeys.

    So your options are:

    1. Specify a range to insert:

    Selection.InsertFile FileName:="C:MyFolderMyExelFile.xls", Range:="A1:C4", _
    ConfirmConversions:=False

    2. Insert the "entire" worksheet:

    SendKeys "{enter}"
    Selection.InsertFile FileName:="C:MyFolderMyExelFile.xls", _
    ConfirmConversions:=False

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Read Excel Data into Word (VBA)

    Two alternatives for extracting the data are to (1) automate Excel from Word, or (2) open the file using OLEDB (ADO). But if you want to use copy/paste or you really want to convert it to Word (does that work?) I think you'll have to continue using what you're using. To "press" the OK key automatically , you could try SendKeys, but this often is flaky.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Read Excel Data into Word (VBA)

    Here's the Automation solution. Just replace the file and sheet names with your values. I also assumed you wanted to copy a worksheet to a Word table. The code would be slightly different if you wanted to copy a chart. Reply back if you want something different. HTH --Sam
    <pre>Option Explicit
    '
    Sub PasteFromExcel()
    ' Pastes a worksheet at the Word selection
    '
    ' Before running this macro you must use
    ' the Tools | References menu to add
    ' the Microsoft Excel Object Library
    '
    Const xlFILENAME As String = "c:test.xls"
    Const xlSHEETNAME As String = "Sheet1"
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlBook = xlApp.Workbooks.Open(xlFILENAME)
    Set xlSheet = xlBook.Sheets(xlSHEETNAME)
    xlSheet.UsedRange.Copy
    Selection.Paste
    xlApp.DisplayAlerts = False
    Set xlSheet = Nothing
    xlBook.Close savechanges:=False
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    35
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Read Excel Data into Word (VBA)

    Richard - I have a suite of Word macros which control an Excel spreadsheet. One takes data from Excel and places it in Word; the other takes from Word and places in Excel. I've added it as a .txt attachment and I'm sure you could modify it for your needs. Most of the code originated from the help files about controlling one Office app from another

  6. The Following User Says Thank You to DougieW For This Useful Post:

    RAVICHANDRAN (2014-03-03)

Posting Permissions

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