Opening Embedded Spreadsheet throws error

[Yaanmonkey]

Hi,



I am trying to loop through embedded spreadsheet objects (1 per row of a table) by iterating a Me.RecordsetClone, grabbing the control with the embedded spreadsheet and setting the control.Object to an Excel.WorkBook object where it can be manipulated (read) and then on to the next record.

This used to work fine last time I used it and even worked fine when I opened it again recently but is now far from fine.

It will run through the 1st object then throws the error "Automation Error Object is not connected to server" when it tries to assign the object on the 2nd record. Also when it runs through the 1st iteration when it gets to the "Set excelWbk = Me.oleServiceHistory.Object" command if I am stepping through with F8 idoesn;t step to the next command it just jumps all the way through the method to the breakpoint at the start of the loop again unless i put a breakpoint after the command.

If anyone has any ideas please let me know as I am pulling out what little hair I have left.

The code in essence is:

Dim rst As Recordset
Dim excelWbk As Excel.Workbook

Set rst = Me.RecordsetClone
rst.MoveFirst

Do While Not rst.EOF

Me.Bookmark = rst.Bookmark
Debug.Print Me.ClientId


Set excelWbk = Me.oleServiceHistory.Object

excelWbk.Application.DisplayAlerts = False


GetClientMachines Me.ClientId, excelWbk


rst.MoveNext
Loop

And the GetClientMachines method has the signature:

Public Sub GetClientMachines(ByVal lngClientID As Long, ByRef excelWbk As Excel.Workbook)

What happens inside this method does not affect the behaviour described because I commented it out altogether and it does the same.


Thanks,

Ian

[Yaanmonkey]

Any help would be greatly appreciated.

Ian

[Yaanmonkey]

Also, the embedded objects are displaying as: "Microsoft Excel 97-2003" and "Microsoft Excel Woorksheet" in the table with the majority being 97-2003 so I was thinking this may be a factor

[Yaanmonkey]

Just tested it using solely rows with "Microsoft Excel Worksheet" objects and it got to the 3rd row and same error