CreateObject for Excel + Break Button

[CLarkou]

I have an MSAccess 2000 program which opens excel for sending data.
Many times while trying to open Excel, it stops and excel does not
open. By pressing BREAK from keyboard, excel opens and continues
without any problem. Why this is happening ?

For opening excel I am using the following code:

'get EXCEL if open
Set exl_APP = GetObject(, "Excel.application")
var_obj_name = exl_APP.Name

'EXCEL is not open
If Err Then
var_EXCEL_ID = Shell("Excel", vbMaximizedFocus) 'open excel

DoEvents
SendKeys "%{TAB}"
SendKeys "%{TAB}"

Do
'select form in order focus from excel to return to access
DoCmd.SelectObject acForm, "interrupt"

'get EXCEL if open
Set exl_APP = GetObject(, "Excel.application")
On Error Resume Next
var_obj_name = exl_APP.Name
Loop While Err And (Timer - var_timer < 20)
End If

 

[Harald Staff]

Hi

Shell and Sendkeys gives your application no control whatsoever of what's
happening. Try this instead:

Private Sub Btn1_Click()
Dim XLapp As Object
Dim Wb As Object
On Error Resume Next
Set XLapp = GetObject(, "Excel.Application")
If XLapp Is Nothing Then
Set XLapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
XLapp.Visible = True
' and your Excel code here.
''''
End Sub

 

[CLarkou]

Thanks a lot, I tried on a machine with problem and it seems to be working fine.