Code runs every other time
 

This code works the first time. It will give the right
answer in the msgbox 1 every other time I run it the
msgbox returns 0. If I remove the getobject call and use
only creatobject then the code runs every other time. The
code will open Excel on the second run but then hangs. I
have to close Excel manually. After that, the code will
run a 3rd time but fail the fourth run.

It seems that Excel is not closing properly. I am calling
Excel from Outlook.

Any help would be greatly appreciated
Thanks in advance
Mike

public dim goodfiles()
Sub test2()
Dim goodfiles(5) As Integer
Dim xl As Object

On Error Resume Next
Set xl = GetObject("excel.application")
If Err < 0 Then
Err.Clear
Set xl = CreateObject("Excel.Application")
End If
xl.Application.Workbooks.Open _
FileName:=("c:\windows\application
data\microsoft\excel\xlstart\personal.xls")
xl.Application.Visible = True
xl.Parent.Windows(1).Visible = True
xl.Application.Workbooks.Open _
FileName:=("c:\DVW\REPORTS\finchk.xls")

goodfiles(1) = Range("a30").Value
xl.Application.ActiveWorkbook.Save
xl.Application.Quit
Set xl = Nothing

MsgBox goodfiles(1)
End Sub

Code runs every other time
 

public dim goodfiles() as Integer

Sub test2()
ReDim Preserve goodfiles(1 to 5)
Dim xl As Object

On Error Resume Next
Set xl = GetObject("excel.application")
If Err < 0 Then
Err.Clear
Set xl = CreateObject("Excel.Application")
End If
xl.Workbooks.Open _
FileName:=("c:\windows\application
data\microsoft\excel\xlstart\personal.xls")
xl.Visible = True
xl.Windows(1).Visible = True
xl.Workbooks.Open _
FileName:=("c:\DVW\REPORTS\finchk.xls")

goodfiles(1) = xl.ActiveSheet.Range("a30").Value
xl.ActiveWorkbook.Save
xl.Quit
Set xl = Nothing

MsgBox goodfiles(1)
End Sub

xl is the application object.

qualifying Range("a30") should clear up your problem.

--
Regards,
Tom Ogilvy


Mike Waldron wrote in message
...
This code works the first time. It will give the right
answer in the msgbox 1 every other time I run it the
msgbox returns 0. If I remove the getobject call and use
only creatobject then the code runs every other time. The
code will open Excel on the second run but then hangs. I
have to close Excel manually. After that, the code will
run a 3rd time but fail the fourth run.

It seems that Excel is not closing properly. I am calling
Excel from Outlook.

Any help would be greatly appreciated
Thanks in advance
Mike

public dim goodfiles()
Sub test2()
Dim goodfiles(5) As Integer
Dim xl As Object

On Error Resume Next
Set xl = GetObject("excel.application")
If Err < 0 Then
Err.Clear
Set xl = CreateObject("Excel.Application")
End If
xl.Application.Workbooks.Open _
FileName:=("c:\windows\application
data\microsoft\excel\xlstart\personal.xls")
xl.Application.Visible = True
xl.Parent.Windows(1).Visible = True
xl.Application.Workbooks.Open _
FileName:=("c:\DVW\REPORTS\finchk.xls")

goodfiles(1) = Range("a30").Value
xl.Application.ActiveWorkbook.Save
xl.Application.Quit
Set xl = Nothing

MsgBox goodfiles(1)
End Sub