M
Maury Markowitz
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!BuildReport",
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
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!BuildReport",
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