Excel Automation Problem

[software]

Hi,

I am having a problem automating excel on a machine where a user is
actively using excel. From my chosen programming application i can
createobject("excel.application") and this works create, but I want to
keep this instance of excel and the one the user might instigate by
loading a spreadsheet seperate. I have initially achieved this by
modifiying the file assocation so that excel behaves more like word
when opening a file. i.e. that each spreadsheet opens in its own
instance of excel.

This in itself has raised more issues, so I want to explore an
alternative. Is it possible for me to duplicate the excel.exe to say
myexcel.exe and modifiy or create a new registry entry so that either
issuing createobject("excel.application") or
createobject("myexcel.application") would use the 2nd duplicated exe
leaving the original excel.exe to work as microsoft intended ???

TIA

Chris.

 

[Dave Peterson]

Why not just keep the instance of excel you created hidden. The user would have
to do something special to find it and unhide it.

 

[software]

It is hidden, they dont have to do anything special to unhide it. All
they need to do is double click on a spreadsheet and it comes to the
front.

 

[Dave Peterson]

Ooh. I see.

This seemed to work ok when I used it from MSWord (with a reference to Excel):

Option Explicit
Sub testme()

Dim xlApp As Object

Set xlApp = CreateObject("Excel.application")
xlApp.Workbooks.Add
xlApp.ActiveWorkbook.Worksheets(1).Range("a1").Value = "hi there"

xlApp.IgnoreRemoteRequests = True

End Sub

 

[Dave Peterson]

Ps. You didn't ask for this, but I keep a shortcut to a file named UnHide.vbs
in case I screw up stuff while testing (so I can unhide those hidden
applications).

Just copy it into notepad and save it with the extension .vbs

dim myXL
On Error Resume Next
Set myXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
msgbox "Excel is not running"
else
myxl.visible = true
end If
On Error GoTo 0
Set myxl = nothing

dim myWord
On Error Resume Next
Set myWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
msgbox "Word is not running"
else
myWord.visible = true
end If
On Error GoTo 0
Set myWord = nothing

dim myOutlook
On Error Resume Next
Set myOutlook = GetObject(, "outlook.Application")
If Err.Number = 429 Then
msgbox "Outlook is not running"
else
myOutlook.visible = true
end If
On Error GoTo 0
Set myOutlook = nothing

 

[Dave Peterson]

One more ps.

This setting is doing the same as
tools|Options|general tab|checking "ignore other applications"

It may make it so that double clicking on that file in windows explorer won't
work at all. Opening Excel and doing File|open will still work.

And it's a setting you'll want to change back to what the user had when you're
done.

 

[software]

Dave,

Thanks for all this its great, I have tried your suggestions and here
is what I found.

I put the default office file association settings back.
I run my application which creates an instance of Excel.
I minimize this app.
I double click on a spreadsheet and as before it opens in the instance
of excel which i have created.

I started again, I added into my application the ignoreremoterequest
when it creates its instance of excel.
I run my application.
I double click on a spreadsheet and it correctly ignores my instance
and creates another instance, but it doesn't load the spreadsheet!

Any more thoughts ?

TIA

Chris

 

[Dave Peterson]

That's one of the problems of using that setting. I don't know away around it
except for starting excel and then File|Open.

Dave,

Thanks for all this its great, I have tried your suggestions and here
is what I found.

I put the default office file association settings back.
I run my application which creates an instance of Excel.
I minimize this app.
I double click on a spreadsheet and as before it opens in the instance
of excel which i have created.

I started again, I added into my application the ignoreremoterequest
when it creates its instance of excel.
I run my application.
I double click on a spreadsheet and it correctly ignores my instance
and creates another instance, but it doesn't load the spreadsheet!

Any more thoughts ?

TIA

Chris

 

[software]

I have decided to stick with my registry hack to set the file
association to open a new instance of excel when a spreadsheet is
double clicked. see how it goes ... thanks for your input there has
been a couple of gems in that unhide code which i will use.

cheers

chris.