Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Excel Wbk from Word Doc (VBA/Excel & Word/2000)

    I have a Word doc I am creating. As I create it, I must work through an Excel wbk. Rather than find both and open both files; I am wondering if I could create a macro to open Excel and the particular wbk while I am working in Word.

    Hope that makes sense?!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Open Excel Wbk from Word Doc (VBA/Excel & Word/2000)

    If you know Excel VBA, you can use Automation to control Excel from Word. See for instance <post#=214736>post 214736</post#>. Note: that post is for Office 97; you must use the Microsoft Excel 9.0 Object Library instead of 8.0 for Office 2000. For more examples, do a search for Excel.Application in this Forum.

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

    Re: Open Excel Wbk from Word Doc (VBA/Excel & Word/2000)

    Easy to do. Here's an example that assumes the workbook has the same name as the document. HTH --Sam
    <pre>Option Explicit

    Sub OpenExcel()
    ' Opens name.xls, where name is the name of the current document
    ' Use Tools | References menu to add Microsoft Excel Object Library
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim strName As String
    strName = ActiveDocument.FullName
    strName = Left(strName, Len(strName) - 4) & ".xls"
    Set xlBook = xlApp.Workbooks.Open(strName)
    xlApp.Visible = True
    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. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Excel Wbk from Word Doc (VBA/Excel & Word/2000)

    SammyB's code assumes that you have a reference set to the Excel Object Library.

    Here is a similar routine that doesn't need a reference to the Excel Object Library set.

    Dim xlApp As Object
    Dim xlBook As Object
    Dim strName As String

    Set xlApp = CreateObject("Excel.Application")

    strName = ActiveDocument.FullName
    strName = Left(strName, Len(strName) - 4) & ".xls"
    Set xlBook = xlApp.Workbooks.Open(strName)
    xlApp.Visible = True

    'release object pointers to avoid problems with memory leakage
    set xlBook = Nothing
    set xlApp = Nothing
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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