I want to provide a button to permit users to open a copy of Word or Excel
from within my application.
I'm using:

Function OpenOfficeApp(vApp As Byte)
Dim obj As Object
Dim strApp As String

If vApp = 1 Then strApp = "Excel" Else strApp = "Word"

On Error Resume Next
Set obj = GetObject(, strApp & ".Application")
If Err <> 0 Then Set obj = CreateObject(strApp & ".Application")
On Error GoTo ErrorOfficeApp

obj.Visible = True
obj.WindowState = 1
'..........etc

It works OK for Word but not for Excel. No error generated (error code
removed from this post), the screen flashes but nothing happens.

--
Bob Darlington
Brisbane

On Dec 18, 10:13*pm, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
Here's some code I use to open an Excel "Template" that contains all
the formatting, etc. that I want:

Set objXL = CreateObject("Excel.Application")
Set wbk = objXL.Workbooks.Open(strTemplate)
wbk.SaveAs strFQP
DoEvents
'Give the OS 1.5 seconds to save the copy
Call MilliSleep(1500)
objXL.Visible = True
Set wsheet = objXL.Worksheets("Sheet1")

'Module Code (from modAPIFunctions):
Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)

That code has worked reliably for me in A97 through A2K3. Note that
the MilliSleep API call was from an example in this NG. The DoEvents
might not be necessary.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

--
"James A. Fortune" <CDMAPoster (AT) FortuneJames (DOT) com> wrote

On Dec 18, 10:13 pm, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
Here's some code I use to open an Excel "Template" that contains all
the formatting, etc. that I want:

Set objXL = CreateObject("Excel.Application")
Set wbk = objXL.Workbooks.Open(strTemplate)
wbk.SaveAs strFQP
DoEvents
'Give the OS 1.5 seconds to save the copy
Call MilliSleep(1500)
objXL.Visible = True
Set wsheet = objXL.Worksheets("Sheet1")

'Module Code (from modAPIFunctions):
Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)

That code has worked reliably for me in A97 through A2K3. Note that
the MilliSleep API call was from an example in this NG. The DoEvents
might not be necessary.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Thanks for the reply James.
I can open an existing file, but can't seem to just open Excel without
referencing an existing file.
It's not critical, but I just wanted to provide the Office links on one of
my switchboards (no data involved).
It works fine for Word, but not Excel.

Bob Darlington
Brisbane

On Dec 18, 8:13*pm, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
I just tried your code segment with access2007 and it opens both word
and excel
if you look in the task manager, is excel running ?

On Dec 21, 1:52*am, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
If you're opening Excel by itself, you don't need to try to find an
open instance. Just use CreateObject without trying to GetObject.
That should avoid the error trap which might be causing the problem.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

"James A. Fortune" <CDMAPoster (AT) FortuneJames (DOT) com> wrote

On Dec 21, 1:52 am, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
If you're opening Excel by itself, you don't need to try to find an
open instance. Just use CreateObject without trying to GetObject.
That should avoid the error trap which might be causing the problem.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com


--------------

Bob, I recently created a form with a command button to open a specific
Excel Workbook to a specific worksheet, and leave the worksheet "on top"
with the focus. The problem was, sometimes the worksheet would open and
remain visible, sometimes not. I eventually discovered that sometimes I
had a "phantom" copy of Excel running (I think I got that solved by
being meticulous about explicitly "SET"ing all the objects and
explicitly setting them all to nothing after I was through) -- that
stray copy if Excel is listed on the Process tab of Task Manager.

Then -- sometimes Excel would receive the focus (which I wanted) and
sometimes not. That issue I never have completely resolved .. in my
case, if Excel opens but does not receive the focus there is a tab in
the windows task bar which will give Excel focus if I click on it. The
code I use is quite similar to what you posted; here's what I use to try
and give the focus to Excel:

With xlApp
If .WindowState = xlMinimized Then
.WindowState = xlMaximized
End If
.Interactive = True
.UserControl = True
.Visible = True
End With
DoEvents

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub



--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

"James A. Fortune" <CDMAPoster (AT) FortuneJames (DOT) com> wrote

On Dec 21, 1:52 am, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
If you're opening Excel by itself, you don't need to try to find an
open instance. Just use CreateObject without trying to GetObject.
That should avoid the error trap which might be causing the problem.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Thanks James.
If the user already has a copy open, then I don't want him to open a
separate copy.
At any rate, I tried your suggestion but got the same result.
I followed Clif's suggestion of inserting '.UserControl = True' and it
worked a treat. See next post in thread.
Thanks again for your interest though.
--
Bob Darlington
Brisbane

Thanks Clif, that worked fine.
I just needed the line 'UserControl = True'.

--
Bob Darlington
Brisbane
"Clif McIrvin" <clare.nomail (AT) gmail (DOT) com> wrote

Roger,
Excel wasn't shown in Task Manager.
I followed Clif's suggestion and inserted 'UserControl = True' and all is
well.
Might be something new in Access 2010.

--
Bob Darlington
Brisbane
"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Dec 18, 8:13 pm, "Bob Darlington" <b... (AT) notheredpcman (DOT) com.au>
wrote:
I just tried your code segment with access2007 and it opens both word
and excel
if you look in the task manager, is excel running ?

Great! Thanks for the feedback.

Clif

"Bob Darlington" <bob (AT) notheredpcman (DOT) com.au> wrote



--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)