Starting Excel from Word

Topic

New Reply

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 ?

Reply | Quote

Replies

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.

Reply | Quote

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 ?

Reply | Quote

I don’t know about Woody’s book recommendation, but I’d recommend you download the Office automation samples 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.

Reply | Quote

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 ?

Reply | Quote

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.

Reply | Quote

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)

Mike

Reply | Quote