How to force an application to be visible?
 

I have a form in Access that calls a complex Excel VBA function to
generate a report, like this...

Public Sub RunReport()
On Error Resume Next
Set oExcelApp = GetObject(, "Excel.Application")
If Err < 0 Then
' excel wasn't running, start it from code
Set oExcelApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

ret = oExcelApp.Application.Run("ReportCreate.xla!BuildR eport",
Me.ReportId)
End Sub

The code works fine, and a report is generated. The problem is that if
Excel was not running when it was called, it opens in the background
somewhere and never becomes visible. On some people's machines a new
instance of Excel is opened whether or not it was already running, so
they can never see the resulting reports.

MS has a command called "AppActivate" that apparently claims to be
able to bring the app forward, but I'm at a loss how to actually use
it:

AppActivate "Microsoft Excel"

results in "Run-time Error 5: Invalid procedure call or argument". The
documents SEEM to suggest this is because a window with that exact
name cannot be found, but if that is the case how could I possibly
know what the name is? It's creating a new workbook, it could be
called anything, Book1, Book2, Book73. The documents also state that
"it activates any application whose title string ends with Title", but
I have no idea how I could use that, Excel puts the book name AFTER
the title, so if I'm reading this correctly, that will never work
either!

I poked about on the 'net and found a function that seems to do the
same thing:

Public Function BringToFront(objApplication) As Boolean
Dim lngRet As Long

With objApplication
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
End With
End Function

However, this results in "Run-time Error 438: Object doesn't support
this property or method", which it reports for every line in the code.

Any ideas?

Maury

How to force an application to be visible?
 

Have you tried
oExcelApp.Visible = True

By default when you create a new instace of an office appliation it is not
visible. I have always used the line above.
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

I have a form in Access that calls a complex Excel VBA function to
generate a report, like this...

Public Sub RunReport()
On Error Resume Next
Set oExcelApp = GetObject(, "Excel.Application")
If Err < 0 Then
' excel wasn't running, start it from code
Set oExcelApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

ret = oExcelApp.Application.Run("ReportCreate.xla!BuildR eport",
Me.ReportId)
End Sub

The code works fine, and a report is generated. The problem is that if
Excel was not running when it was called, it opens in the background
somewhere and never becomes visible. On some people's machines a new
instance of Excel is opened whether or not it was already running, so
they can never see the resulting reports.

MS has a command called "AppActivate" that apparently claims to be
able to bring the app forward, but I'm at a loss how to actually use
it:

AppActivate "Microsoft Excel"

results in "Run-time Error 5: Invalid procedure call or argument". The
documents SEEM to suggest this is because a window with that exact
name cannot be found, but if that is the case how could I possibly
know what the name is? It's creating a new workbook, it could be
called anything, Book1, Book2, Book73. The documents also state that
"it activates any application whose title string ends with Title", but
I have no idea how I could use that, Excel puts the book name AFTER
the title, so if I'm reading this correctly, that will never work
either!

I poked about on the 'net and found a function that seems to do the
same thing:

Public Function BringToFront(objApplication) As Boolean
Dim lngRet As Long

With objApplication
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
End With
End Function

However, this results in "Run-time Error 438: Object doesn't support
this property or method", which it reports for every line in the code.

Any ideas?

Maury

How to force an application to be visible?
 

On Aug 8, 11:28*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Have you tried
oExcelApp.Visible = True

Oh geez, that easy?! Worked like a champ, thanks!

Maury

How to force an application to be visible?
 

I darn near didn't post because I figured you would have already tried that
and for some reason it would not work for you. I then thought back to all of
the times I did not try something that seemed obvious in retrospect and
posted... By the way nice lookin code.
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

On Aug 8, 11:28 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Have you tried
oExcelApp.Visible = True

Oh geez, that easy?! Worked like a champ, thanks!

Maury