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