Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Starting Excel from Word

    Hi
    I'm fairly new to VBA and can't seem to make excel start Word or vice versa. I know I'm close but can't quite do it.
    I want to write a macro that I can run in word which will start up excel or open a window in excel if it is already running.
    I've tried
    Dim xl as Excel.Application
    Set xl=CreateObject ("Excel.Application)
    but I think I'm missing something

    Can anyone help ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Starting Excel from Word

    Do you have a reference set to the object library of the application you're trying to automate? If not, go into the VBE, select Tools-->References, and find and check the Excel or Word object library. Then you'll be able to use that application's objects, methods and properties in your code. However, automation works a bit differently in Office 2000 than in Office 97, so you'll need to post more specific information to get more specific help.
    Charlotte

  3. #3
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starting Excel from Word

    WOooah ... sounds a lot more complex than I was expecting. I've just been fooling with macros (recording then editing the recorded macro), I don't really have any idea what I'm doing. I just thought opening Excel from Word would be easy

    Guess I'll have to get myself a book and do it properly. What book would Woody recommend ?

  4. #4
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starting Excel from Word

    WOooah ... sounds a lot more complex than I was expecting. I've just been fooling with macros (recording then editing the recorded macro), I don't really have any idea what I'm doing. I just thought opening Excel from Word would be easy
    (it's Office 2000 by the way on Win98)

    Guess I'll have to get myself a book and do it properly. What book would Woody recommend ?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Starting Excel from Word

    I don't know about Woody's book recommendation, but I'd recommend you download the <A target="_blank" HREF=http://download.microsoft.com/download/office2000dev/sample/2/WIN98/EN-US/OFFAUTMN.EXE>Office automation samples</A> from the Microsoft site. This includes both Office 2000 and 97 examples, information and white papers. Sometimes it's easiest to take an example apart until you understand it.
    Charlotte

  6. #6
    calacuccia
    Guest

    Re: Starting Excel from Word

    There is an error in your statement:

    Set xl = CreateObject("Excel.Application")

    If you precede this line by the statement,

    Dil xl As Object

    You don't need to set a reference to the 'Microsoft Excel 8.0 (or 9.0) Object Library' in this case.

    If you declare it as you did, you need the reference

    Dim xl As Excel.Application

    This line should work in any case

    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starting Excel from Word

    It's better to set a reference to the Microsoft Excel Object Library. Then you'll get VB's intellisense/auto-command-completion thingy. Also, if I remember correctly, CreateObject is late binding, so your app will run slower.

    'For variable declaration
    Option Explicit

    Dim xl as Excel.Application
    Set xl = New Excel.Application

    [rest of code here]

    'shut down excel
    xl.quit
    'destory the object
    Set xl = Nothing

    Good books...

    Hmmm...

    I'm not sure about some of the beginning VB/VBA books.

    Intermediate books would be...
    The Office 2000 Programmer's Guide (MS PRESS)
    Writing Excel Macros - Steve Roman(O'reilly)

    [img]/S/smile.gif[/img]

    Mike

  8. #8
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Starting Excel from Word

    Ok so I create a macro like this:

    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True

    When I run it with Excel minimised on the toolbar a window
    flashes open and disappears. With out Excel running nothing
    happens.

    I don't mind it running slowly but it I don't think it's doing what it should.

Posting Permissions

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