[AccessD] Excel Automation Question

[Bill Benson]

Rocky, I use a couple of techniques. When I am automating Excel I create my
own instances only if I do not expect the user to interact with Excel. So
as others have pointed out, you want to use GetObject (,"Excel.Application)
and if this is nothing, then set your object variable equal to a new
instance.

But here's the rub - as you pointed out, if the user closes it, you have an
orphaned pointer to a memory location that no longer exists. Attempts to
use it probably result in :"Automation Error" kind of message. But that is
easily dealt with (I think) by just setting to nothing and re-instancing if
you need to use it again.

Another technique I use so as to know which instance of Excel I want to
work with, especially if spread out over time, is create a hidden workbook
stored in the user's temp folder, with objWB.Windows(1).Visible = False (or
maybe the syntax is objWB.Windows(1).Hidden = True ... can't remember just
now). I forget why I wanted that but it became something I could check to
make sure my instance was still open. It is quite unlikely the user would
find and close it while working in Excel.

Getting the real handle of an Excel instance is a huge API chore,
fortunately I have never had to figure out how to do it.



On Thu, Aug 25, 2016 at 3:55 PM, Rocky Smolin <rockysmolin at bchacc.com>
wrote:

> Dear List:
>
>
>
> I want to open a spreadsheet from Access and am using:
>
>
>
>     Set objXLApp = New Excel.Application
>
>     objXLApp.Workbooks.Open gstrBackEndPath & "\PO_" &
> Me.cboPOList.Column(1) & ".xlsx"
>
>     objXLApp.Visible = True
>
>
>
> which works just fine.  However, when the spreadsheet is closed by the user
> the instance of Excel remains - I can see it in the Task Manager.
>
>
>
> If I close the spreadsheet from Access:
>
>
>
>     objXLApp.ActiveWorkbook.Save
>
>     objXLApp.ActiveWorkbook.Close True
>
>     objXLApp.Quit
>
>     Set objXLApp = Nothing
>
>
>
> Of course, Excel goes away.
>
>
>
> Is there a technique for closing that instance of access when the user
> closes a spreadsheet opened by an access program?
>
>
>
> MTIA
>
>
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 760-683-5777
>
>  <http://www.bchacc.com> www.bchacc.com
>
>  <http://www.e-z-mrp.com> www.e-z-mrp.com
>
> Skype: rocky.smolin
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>