active x can't create object of excel

[beeleegie]

I have a piece of code like this:

------------------------------------
Dim objExcel As Excel.Application
Dim scores(1) As Variant
Dim rowNum As Long

Set objExcel = CreateObject("Excel.Application&quot

With objExcel
.Workbooks.Open (sLLSFile)
'The First sheet is selected
.Sheets(1).Select
rowNum = .Range("A1&quot.EntireColumn.Find(FundId).Row
If Trim(.Range(&quot;F&quot; & rowNum).Value) <> &quot;&quot; Then
scores(1) = .Range(&quot;F&quot; & rowNum).Value
End If
End With

objExcel.Quit
Set objExcel = Nothing
------------------------------------------------------
I loop this piece of code in a function and generate a report. It works fine in some computer and doesn't work in my computer. An error occur in createObject line and the error is 429: ActiveX can't create object. It is really troublesome when i use my program unattendedly. (I don't know will it hang or not.)
I suspect the vb programme can't create an Excel object due to the bug of Excel.Quit. Any method to solve my problem?

 

[Custom24]

Hmmmm

Not sure,

1 - if it is just not working on your computer, then do you have XL on your computer? (or at least, is the XL type library correctly registered? - EXCEL8.OLB for example)

2 - the way you are instantiating XL is not taking advantage of the fact that there may be an instance already running. This is what I use

Function xlAppMyReturnExcelApp() As Excel.Application
'this works using the create/get object thing
'this is better than referring to excel.workbooks or whatever directly because
'that way is not gauranteed to release excel correctly
On Error Resume Next
Set xlAppMyReturnExcelApp = GetObject(, &quot;Excel.Application&quot
If Err.Number = 429 Then
Set xlAppMyReturnExcelApp = CreateObject(&quot;Excel.Application&quot
Else
Err.Raise Err.Number
End If

End Function

Mark

 

[beeleegie]

my computer already have EXCEL9.OLB

 

[Custom24]

OK - I just noticed something else. You said that you loop this piece of code - I would take the Create Object outside of the loop (better still to use the Function I posted, I think)
Otherwise, you are asking your app to instantiate Excel every time in the loop and this is wasteful and could be causing your problem.

Mark

 

[beeleegie]

yup, i have put outside the loop, thank you.
by the way, if I use getObject code, then when i xlAppMyReturnExcelApp.Quit, it will close all the object of excel file that I currently open.
So now my solution is just put the code outside the loop, and createobject for my programme to use and quit it by itself. Any better method?