How to keep an instance of Excel away from the user?

[RzB]

Using Office 2002/3 and DAO

I have an Access application that creates a new
spreadsheet basically using the code shown at the
end of this post..

While the spreadsheet is being created I present
the user with a a simple form giving them
feedback on the progress. I make sure they are
unable to do anything else on my Access
application while this is going on.

It all works really well but there is one problem...

If the user opens Excel while the process is
executing and then closes Excel, then my Excel object
in the access code dissapears. A bit fatal.

Is there some way I can open the an instance of
Excel and stop others from using the same instance.

Or is there some better way ?

Many thanks,
Roy

--------------------------------------------
Private m_objXlApp As Excel.Application
Private m_objXlWkb As Excel.Workbook
Private m_objXlSht As Excel.Worksheet
Private m_rngCurrentRange As Excel.Range

Set m_objXlApp = New Excel.Application
m_objXlApp.Visible = False
Set m_objXlWkb = m_objXlApp.Workbooks.Add
Set m_objXlSht = m_objXlWkb.Worksheets("Sheet1")
Set m_rngCurrentRange = m_objXlSht.Range(ProgramTopLeftCell)
.......
m_rngCurrentRange.Offset(intRowPosCount, conZSize) = whatever

etc...all sorts of sheet creation, cell
filling and formatting going on here..
etc...
......
Set m_rngCurrentRange = Nothing
Set m_objXlSht = Nothing

m_objXlWkb.Close
Set m_objXlWkb = Nothing

m_objXlApp.Quit
Set m_objXlApp = Nothing
------------------------------------------

 

[DM Unseen]

don't forget m_objXlApp.IgnoreRemoteRequests = True 'to prevent user to
launch XL files through explorer
and if your really paranoid also:
m_objXlApp.Interactive = False

DM Unseen

 

[NickHK]

Roy,
You mean that the user interacts with your m_objXlApp instance of Excel ?
If they open/close another instance of Excel, it should not affect your
hidden instance.

NickHK

 

[RzB]

What I mean is ...

I set my access code running and it's opened Excel, and is quietly creating
sheets, filling in cells and doing some formatting. Lets call the file it
has created is AAA.xls.
This should all be out of sight of the user. All he can see is a small form
running with a little activity showing how progress is going. He has no
interaction with this process other than to watch it the progress in this
form.

Now - if the user opens Windows Explorer, while this is all going on, and
double clicks on BBB.wks, Excel opens and he is able to see the BBB.wks in
an Excel window. However if he were to look in command bar - Window he would
see BBB.wks and AAA.wks referenced.

If he now closes Excel he is asked if he wants to save AAA, and if he says
yes the we have a problem. We probably have a problem if he says no as
well...

Am I doing something wrong ?

Many thanks for your help,
Roy

 

[NickHK]

Roy,
So you showing an Excel userform ?

NickHK

 

[RzB]

No. Do I need to? I'm just trying to create an Excel
spreadseheet from within Access.

I don't think I need an Excel userform...There is no
interaction needed from the user...

Unless there is something here I don't understand...?

Thanks,
Roy

 

[RzB]

Brilliant... That fixed it...

Thank you very, very much...

Roy

 

[RzB]

Problem fixed by DM Unseen - see other post in this thread...

m_objXlApp.IgnoreRemoteRequests = True

fixed the problem...

Roy

 

[RzB]

Hmmm....

Since implementing this I seem to have a problem with Excel...

If I double click on a xls file in windows explorer, excel
burst into life but does not open the target file...

It just sits there with no file loaded...

Have I screwed something up ?

Many thanks,
Roy

 

[RzB]

Ok - just for information..

Setting m_objXlApp.IgnoreRemoteRequests = True
stays with excel when the application completes...

What I have done now is to save the setting, then
set is as above, then restore it to when my application
completes.

Roy