Hiding spreadsheet creation/processing from the user

[RzB]

Using Access 2002/3 and DAO and Office 2002/3..

I have an Access application that creates a new
spreadsheet basically using the code below...
---------------------------------------------------------------
Set m_objXlApp = New Excel.Application
m_objXlApp.Visible = False

'Now create sheets, fill in cells and format stuff
'This may take a few minutes as there is a lot to process...

m_objXlApp.Quit
Set m_objXlApp = Nothing
---------------------------------------------------------------
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 they have Excel open or they try to open
Excel while the process is executing then it
all screws up. Is there some way that I can
stop this from happening? What am I missing..

Thanks,
Roy

 

[Klatuu]

This will prevent user from making any changes to the instance of Excel you
are using. It also avoids any Excel Alert messages, and turns off screen
updating to save time.

m_objXlApp.DisplayAlerts = False
m_objXlApp.Interactive = False
m_objXlApp.ScreenUpdating = False

 

[RzB]

Klatuu,
Many thanks for you response. That didn't
seem to help.. If I set the Access application
running and then open a any other spreadsheet
it seems to interfere with the Access generation...
I get all sorts of range selection errors...

Is there some way I can start my own
instance of Excel that the user is not going
to be able to break into?
Hmmm..

Many thanks,
Roy

 

[Klatuu]

That I don't know. Creating a new instance will not interfer with another
instance the user already has open, but as far as a user sharing the instance
you already have open, I have not had that problem.

 

[RzB]

Klatuu,
Hmm - I was wrong in my original post
when I said "If they have Excel open ".
If the user has Excel open, you are indeded
right - the Access code appears to start a new
instance of Excel.

The problem only occurs if the user opens
another spreadsheet once the Access code is
running. This appears to use the instance of
Excel that the Access code is running in and
that seems to screw it up.

You would think there would be a way to stop
others using the the instance you are running.

Many thanks anyway...
Roy

 

[Klatuu]

Someone with more knowledge than I may have a way to do that. In the
meantime, publish a notice to your users not to open any Excel spreadsheets
while the process is excuting. If they do, go the their desk and slap the
crap out of them

 

[RzB]

Ahhh - Now I like you approach to customers...

 

[John Nurick]

Roy,

You say "range selection errors". That sounds as if your code is using
Excel's Selection object, and maybe ActiveWorkbook and ActiveSheet - which
could be the cause of the trouble.

If you avoid those and work with specific workbooks etc. things should be
more stable, e.g.:

Set wbkW = m_objXlApp.Open("C:\folder\workbook.xls")
Set wksS = wbkW.Sheets("Sheet1")
With wksS.Range("A110")
....
End With

 

[RzB]

John,
Thank you for your help...
I have trapped the sort of thing that's going wrong.
Below is (I think) the pertinent code....

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
m_objXlApp.SheetsInNewWorkbook = 1
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, 8).select

When I do the select I get a 1004
"Select method of Range class failed"

I have coded round this so that I don't do .select anywhere
and that seems to fix that particular situation. Thanks.

However I have also just found something even more
concerning. If the user opens Excel after the Access
code is running, and then closes Excel the code is left with no
Excel object ! Hmm there must be a better way!

Mnay thanks,
Roy

 

[John Nurick]

I have coded round this so that I don't do .select anywhere
and that seems to fix that particular situation. Thanks.

That's almost always a good idea when automating an Office app.

However I have also just found something even more
concerning. If the user opens Excel after the Access
code is running, and then closes Excel the code is left with no
Excel object ! Hmm there must be a better way!

I haven't hit that problem myself, possibly because I tend to go and
make a cup of tea when there's a long-running automation process at
work. There's a detailed white paper on Office automation at
http://support.microsoft.com/?id=253235 which will probably repay study.
It says it applies to Office 97 and 2000, but is probably about 98%
relevant to later versions.

 

[RzB]

John many thanks for your help.
I will indeed look at the referenced white paper. I think I might have
looked at it some years ago - be good to refresh.

I too would go for the cup of tea but I know I have users out there who
would delight in making an application fail if they can It a sort of
sport for them - some even do it for a living

I might try over on the Excel forum..

Thanks,
Roy

 

[RzB]

Klatuu & John,

I posted on the Excel.Programming newsgroup and got a resolution from one DM
Unseen...

the answer is....
----------------------------
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

 

[John Nurick]

Thanks for the feedback. I hate to admit it, but those guys know more about
Excel than we do<g>.