How to acces ADO disconnected recordset object from another workbook

[bradyboyy88]

Is it possible to have a recordset object created in one workbook but access this recordset object from another workbook's vba code? I was thinking maybe getobject could be used to capture it from another workbook but I cannot find any resources on this.

 

[ZVI]

Yes, it's possible.

Put this code to a module of the source workbook WbSource.xlsb

' Code in the source workbook "WbSource.xlsb"
Option Explicit

Function DisconnectedRs() As Object
  
  Set DisconnectedRs = CreateObject("ADODB.Recordset")
  
  With DisconnectedRs
    
    ' Add some fields
    .Fields.Append "ID", 3
    .Fields.Append "Value", 129, 20
    
    'Open it up
    .Open , , 3, 3
    
    'Add new record
    .AddNew
    'Put values
    .Fields(0).Value = 1
    .Fields(1).Value = "Description1"
    
    'Add new record
    .AddNew
    'Put values
    .Fields(0).Value = 2
    .Fields(1).Value = "Description2"

    'Pass it for the fast updating (without using .Update for each record)
    .MoveFirst
    
  End With

End Function

The code below goes to a module of the destination workbook:

' Code in the destination workbook "WbDest.xlsb"
Option Explicit

Dim Rs As Object 'Recordset

Sub GetExternalRs()
  Set Rs = Application.Run("'WbSource.xlsb'!DisconnectedRs")
  ThisWorkbook.Sheets(1).Range("A2").CopyFromRecordset Rs
End Sub

Then run GetExternalRs() to see it is working.

Regards

 

[bradyboyy88]

This is great if you want to create the disconnected recordset from another workbook and utilize it in either workbook which is really cool too. However, Is there a way to locate the object in memory if it was already created in the other workbook and use it in the other which did not create it?

 

[bradyboyy88]

Looking at your example. I thought this would be the solution but I hit a roadblock because how would the user find the recordset to set it in the function.

' Code in the source workbook "WbSource.xlsb"
Option Explicit
'ado recordset created somewhere in this module

Function DisconnectedRs() As ADODB.Recordset
   'If we could find all the disconnected recordset or loop through them somehow? IDK how to accomplish this.
  Set DisconnectedRs = DisconnectedRecordset 

End function

The code below goes to a module of the destination workbook:

' Code in the destination workbook "WbDest.xlsb"
Option Explicit


Dim Rs As Object 'Recordset


Sub GetExternalRs()
  Set Rs = Application.Run("'WbSource.xlsb'!DisconnectedRs")