I'm doing it like You wrote.
Iv'e establisched that user can access my data when he runs
xls file from explorer. If I start Excel app while my program runs
then I've got 2 separate instances of excel but when my program
first runs excel and then user activates xls file then its automaticly
opend in my program's instance of Excel.
I was thinking about this when I saw Bob's reply last night.
Interestingly, I have a similar situation with Word. I have an
application which is run from Excel, but which writes output to a Word
document via a hidden Word session. As long as I don't call the
DoEvents function while it's running, Excel will generally hog the
Windows interface and the user won't be able to get to Explorer
anyway. However since the Word file can take a few minutes to write, I
generally prefer not to do that and DO call DoEvents periodically.
That leaves me with the same problem; if the user opens Word or a Word
document while the application is running, the session that I have
running in the background is the one that will appear. Since I'm
(currently) the only one who really uses the application, I haven't
worried about fixing it. However your post has set me t'thinkin' again
about this whole problem.
Excel does have one advantage over Word in this respect; an
application property called IgnoreRemoteRequests. It equates to the
Ignore Other Applications checkbox in Tools -> Options -> General.
Now, it SEEMS that that property can be used to cure what ails you.
Try entering the following into a standard module of Word (or Access,
or VB6; though I wrote it in Word and can't vouch for its performance
in the other two):
Sub StartExcelWithRequestsIgnored()
Dim xlApp As Object
Dim xlWbk1 As Object
Dim l As Long
'Controls the timer; I'm too lazy to declare them
'properly.
Dim vnt_Hour, vnt_Minute, vnt_Second, vnt_waitTime
Set xlApp = CreateObject("Excel.Application")
'This should sort out your problem...
xlApp.IgnoreRemoteRequests = True
Set xlWbk1 = xlApp.Workbooks.Add
'The following allows us to identify the hidden session when
'we see it, as we will later.
xlWbk1.Worksheets(1).Cells(1, 1).Value = _
"This is my hidden session."
'The following simulates the processing that you're doing in Excel.
'During the minute that the procedure will run
'after you click [OK], go ahead and open an Excel
'file from Explorer and do whatever you like in it.
'You'll note that a NEW Excel session comes
'up rather than your hidden one.
'The new session won't be affected by your having
'set the IgnoreRemoteRequests property; check Tools ->
'Options in the new session to confirm this.
'Apparently the setting only gets saved when the
'Excel session closes.
MsgBox "I'm about to do some stuff in the hidden session " _
& " of Excel. It should take about a minute to run." & vbCrLf _
& "Click OK to start, then go and " _
& " open an Excel file from Explorer, or Excel itself."
For l = 1 To 12
vnt_Hour = Hour(Now())
vnt_Minute = Minute(Now())
vnt_Second = Second(Now()) + 5
vnt_waitTime = TimeSerial(vnt_Hour, vnt_Minute, vnt_Second)
xlApp.Wait vnt_waitTime
xlWbk1.Worksheets(1).Cells(l + 1, 1).Value = _
"This is the entry at " & CStr(l * 5) & " seconds."
Next
xlApp.Visible = True
MsgBox "Just to prove that this procedure was still running " _
& "while the other Excel session was open, take a look at " _
& "it. I'll wait here 'till you get back..."
xlApp.Visible = False
' VERY IMPORTANT!! Set the property back to false
' so that you don't stuff up the user's ability to
' launch files from Explorer.
xlApp.IgnoreRemoteRequests = True
Set xlWbk1 = Nothing
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlApp = Nothing
End Sub
As you can see, your hidden session should remain nicely isolated from
the user's one. Note that I just conceived, wrote and tested this in
the last 20 minutes and CANNOT guarantee that I haven't overlooked
some obscure problem with hanging references or some such. However
I've run it a few times in different circumstances now, and it always
seems to behave the way I expect.
(And what idea did I come up with for my Word problem, since Word
lacks the IgnoreRemoteRequests property? Opening up TWO hidden Word
sessions, and using the second one to shield the first one if
necessary. Note that I wrote something to test THAT in the last 20
minutes as well, so I can't vouch for its effectiveness yet. If worse
comes to worst, though, you could probably use that approach as an
alternative to the one above.)
