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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

  3. #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>

  4. #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
  •