How to acces ADO disconnected recordset object from another workbook

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
520
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Yes, it's possible.

Put this code to a module of the source workbook WbSource.xlsb
Rich (BB code):
' 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:
Rich (BB code):
' 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
 
Last edited:

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
520
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?
 
Last edited:

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
520
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:
' 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:
Code:
' Code in the destination workbook "WbDest.xlsb"
Option Explicit


Dim Rs As Object 'Recordset


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

Forum statistics

Threads
1,081,575
Messages
5,359,723
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top