Automation Error : Object Invoked Has Disconnected From its Clients

[Quanita]

I am reading in data from 2 separate databases and placing them into one recordset, I am then placing this data into an xl workbook adding 3 graphs and saving it for user manipulation.

The record set is filtered and I am saving a workbook for each filter. The first time that it moves through the loop there is no error. The second and subsequent I get the following Error <Automation Error: Object Invoked Has Disconnected from its Clients> on line 9, if I debug and step back up to line 3 and rerun the code there are no errors.

Any suggestions as to what is going wrong? or how to fix it?

Thanks
Q
PS I have tried the loop with and without closing the xl application on each loop same error


1 For k = 1 To WkCentres.Count Step 1
2 sbReport.Panels(&quot;text&quot = &quot;Creating Excel Object....&quot;
3 Set xl = CreateObject(&quot;Excel.Sheet.5&quot
4 WorkCentreFilter = &quot;WorkCentre = '&quot; & WkCentres.Item(k) & &quot;'&quot;
5 .rsAngdata.Filter = WorkCentreFilter
6 If .rsAngdata.RecordCount <> 0 Then
7 sbReport.Panels(&quot;text&quot = &quot;Adding Field Names for Work Centre = &quot; & WkCentres.Item(k) & &quot;...&quot;
8 For i = 0 To .rsAngdata.Fields.Count - 1 Step 1
9 xl.ActiveSheet.Cells(1, i + 1).Value = .rsAngdata.Fields(i).Name
10 Next i
' Additional Code in here not relevant to post
11 sbReport.Panels(&quot;text&quot = &quot;Saving the Spreadsheet...&quot;
12 Call fso.DeleteFile(&quot;C:\Temp\&quot; & xlName)
13 xl.SaveAs &quot;C:\Temp\&quot; & xlName
.rsAngdata.Filter = &quot;&quot;
14 sbReport.Panels(&quot;text&quot = &quot;Quitting Excel...&quot;
15 xl.Application.Quit
16 Next k

 

[jonscott8]

Add Set oExcel = Nothing after xl.Application.Quit.

FWIW, unnecessaryily destroying/recreating object variables adds excessive overhead to your code. You may want to refer to some of the Excel automation examples found in the FAQs of the following forums:
forum222
forum707 Jon Hawkins

 

[Quanita]

Thanks for your Help.
Q