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

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

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

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