Accessing an Application Object from separate modules

A

Andy

I have an Access subroutine which creates a new Excel Application as an
Object and adds a workbook, worksheet, writes data to the worksheet, etc.

I need to close the subroutine and call another subroutine which can access
the same instance of the Excel application object and continue to add
worksheets and data within the same workbook.

I would like to pass the reference to the object between subroutines instead
of saving the workbook to a well known destination and then opening it again
from the second subroutine - i.e. the Excel Application only gets opened once.

Any ideas?

Thanks,
Andy.
 
D

Dirk Goldgar

Andy said:
I have an Access subroutine which creates a new Excel Application as
an Object and adds a workbook, worksheet, writes data to the
worksheet, etc.

I need to close the subroutine and call another subroutine which can
access the same instance of the Excel application object and continue
to add worksheets and data within the same workbook.

I would like to pass the reference to the object between subroutines
instead of saving the workbook to a well known destination and then
opening it again from the second subroutine - i.e. the Excel
Application only gets opened once.
Click to expand...

What do you mean by "close the subroutine"? Subroutines don't "close",
they just exit -- return to the calling procedure. Do you mean to call
the second subroutine from the first one? If so, what's to stop you
from just passing the Excel Application object to it as an argument.
For example:

Sub Proc1

Dim objExcel As Object

Set objExcel = CreateObject( ... )

' ... code manipulating objExcel ...

Call Proc2(objExcel)

End Sub

Sub Proc2(objExcel As Object)

With objExcel

' ... do stuff ...

.Quit ' if this is is how you terminate an Excel
application

End With

Set objExcel = Nothing

End Sub
 
A

Andy

Thanks Dirk and sorry about not making myself very clear with the 'close
subroutine' - I did mean exit.

You have answered by question anyway - it seems that I can pass the object
as a parameter to another subroutine and continue to manipuilate that object
(as you described) which is exactly what I needed. The design could then be
to create the object in the initial procedure and then makes calls to as many
subroutines as necessary to add data etc. and finally, return to the original
calling procedure to close the application and tidy up.

Thanks again,
Andy.
 
Top