Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Opening Excell From Word (Word XP)

    I am creating a macro where I generate a table, but I ultimately want to drop the contents into an excell template. How can I open Excel from the macro?

    -Jody

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening Excell From Word (Word XP)

    Dim oXLApp as Object
    Set oXLApp=CreateObject("Excel.Application")
    'do some stuff
    oXLApp.Quit
    Set oXLApp=Nothing
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Opening Excell From Word (Word XP)

    Controlling one application from another in code is called Automation.

    In order to do things with Excel from Word, you must first set a reference to the Excel object library.
    In the Visual Basic Editor, select Tools | References...
    Scroll down until you see Microsoft Excel 10.0 Object Library (the 10.0 is the version for Office XP; for Office 2000, it's 9.0 and for Office 97, it's 8.0)
    Check the corresponding box and click OK.
    Next, you must create an Excel application object. This will start an invisible copy of Excel. You can use Excel VBA, but you must prefix Excel instructions by the name of an Excel object. The outline of the code for this is:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub TestExcel()

    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet

    On Error Goto ErrHandler

    ' Create Excel, Workbook and Worksheet objects
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)

    ' Example code to transfer something from Word to Excel
    ActiveDocument.Tables(1).Copy
    xlWs.Paste Destination:=xlWs.Cells(1, 1)
    xlWb.SaveAs FileName:="C:Test.xls"

    ExitHandler:
    ' Mopping up
    On Error Resume Next
    xlWb.Close SaveChanges:=False
    xlApp.Quit
    Set xlWS = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    ' Report error
    MsgBox Err.Description
    Resume ExitHandler

    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    Error handling is extremely important in Automation. If you don't clean up after you, Excel will keep on running invisible if an error occurs.

    It is often a good idea to work out the Excel code to be used in Excel itself, because its easier to debug there. When you know what to do, use this to write the Word macro.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Opening Excell From Word (Word XP)

    Thank-You! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

Posting Permissions

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