Hi

I'm preparing some reports and paste them in Excel file with formatting etc. All works fine Except it takes a while and if meanwhile User decides to work in Excel too he opens the same instance of excel which my program uses to work. This disturbs program work and hangs It. ex. when program creates report and user exits Excel it shuts down entire application not bothering my program still uses it.
Can I create separate instance of Excel so staring new excel by user wouldn't interfere with mine?

Thanks for help

Kamil,

Yes you can.

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")

and then work in VBA as norm al, but referencing all objects via xlApp.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kamil" wrote in message
...
Hi

I'm preparing some reports and paste them in Excel file with formatting
etc. All works fine Except it takes a while and if meanwhile User decides to
work in Excel too he opens the same instance of excel which my program uses
to work. This disturbs program work and hangs It. ex. when program creates
report and user exits Excel it shuts down entire application not bothering
my program still uses it.
Can I create separate instance of Excel so staring new excel by user
wouldn't interfere with mine?

Thanks for help

On Fri, 30 Jan 2004 07:26:12 -0800, "Kamil" wrote:

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.)

----- Bob Phillips wrote: -----

Kamil,

Yes you can.

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")

and then work in VBA as norm al, but referencing all objects via xlApp.

"Kamil" wrote in message
...
Hi
I'm preparing some reports and paste them in Excel file with formatting
etc. All works fine Except it takes a while and if meanwhile User decides to
work in Excel too he opens the same instance of excel which my program uses
to work. This disturbs program work and hangs It. ex. when program creates
report and user exits Excel it shuts down entire application not bothering
my program still uses it.
Can I create separate instance of Excel so staring new excel by user
wouldn't interfere with mine?
Thanks for help

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

On Sat, 31 Jan 2004 13:02:30 +1100, Hank Scorpio
wrote:

DADBURN IT!!!

Do like I say, not like I do... the following block from near the end
of my original post should of course read:

' 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 = False

Sorry, that was lazy copying on my part. I made a last minute change
to the code and accidentally overwrote that line, then copied down the
line from the earlier part of the code, and forgot to change the value
from True to False. My bad.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *