VBA Trick of the Week: Range to Recordset Without Making Connection

Posted: July 11, 2013 by kulshresthazone in Excel, VBA
Tags: , ,

Converting a Range to Recordset is a very painfull process. Currently the process which we follow is establishing a connection and then firing a Query to get the values in to a Recordset.
But now we can make a new and better approach using the Range.Value() property.
This is a better way to move data from one workbook to another without making use of the Clipboard.

Sub Test()

        Dim xlXML             As Object
        Dim adoRecordset      As Object
        Dim rng               As Range

        Set rng = ActiveSheet.Range("A1:D20")
        Set adoRecordset = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument") 
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        adoRecordset.Open xlXML

End Sub

This adorecordset will hold the range in the form of recordset and one make use of this in the way it is required.
Happy Coding….   😉

  1. Thomas Ellebæk says:

    Why not use 2-dimensional arrays to transfer data?

    Sonething like (sorry about possible errors, on mobile now)
    Din v as variant
    Let v = wksSource.Range(“A1:C30”)
    with wksTarget
    End with

  2. Thomas Ellebæk says:

    Hey, missed something rather important 🙂

    = v
    After rang definition.

  3. Transformer says:

    Hi Thomas,
    It can be done using arrays and in this example it doesn’t make any difference either we use array or a recordset. But at times we need some data processing before pasting the data e.g. Filtering and Sorting etc. In case of recordset there are built in functions to do the same but in case of array we need to write code to do these things.So it depends on situation where we need which method.

    • Thomas Ellebæk says:

      Just the explanation I needed 🙂

      I agree, arrays are not especially handy to work with, but some worksheetfunctions can process array data.

  4. Excel Geek says:

    Yoy guys are great……Awesome 🙂

  5. John Bonham says:

    I’m trying to insert the result of your trick into an Access database and I can’t make it to work. Can somebody help me out?

    connDB.Open ConnectionString:=”Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

    strSQL = “INSERT INTO PVAnag SELECT * FROM adoRecordset”

    connDB.Execute strSQL, nr

    MsgBox (nr)

  6. Andrew says:

    Thank you very much. This is really fast. I ran into some trouble when I tried to change a value on a row in the record set. This is my update (Excel 2010).

    Private Function GetRSFromWorksheet(ws As Worksheet) As Recordset
    Dim tmpRS As Recordset, tmpXML As DOMDocument
    Dim ElementNode As IXMLDOMElement, AttributeNodes As IXMLDOMNodeList, Child As IXMLDOMElement

    ' create an XML document and put the data into it
    Set tmpXML = New DOMDocument
    tmpXML.LoadXML ws.UsedRange.Value(xlRangeValueMSPersistXML)

    ' Without the following updates to the XML, before using it as a recordset
    ' source, changing a value in a recordset field might result in the error
    ' "multiple-step operation generated errors"
    ' If you don't care about manipulating the recordset, you can skip this code
    Set ElementNode = tmpXML.SelectSingleNode("xml/x:PivotCache/s:Schema/s:ElementType")
    ElementNode.setAttribute "rs:updatable", "true"

    Set AttributeNodes = tmpXML.SelectNodes("xml/x:PivotCache/s:Schema/s:AttributeType")
    For Each Child In AttributeNodes
    Child.setAttribute "rs:write", "true"
    Next Child
    ' end of XML updates

    ' create the recordset, with the XML as the source
    Set tmpRS = New Recordset
    tmpRS.Open Source:=tmpXML, CursorType:=adOpenKeyset, LockType:=adLockBatchOptimistic

    'return to caller
    Set GetRSFromWorksheet = tmpRS
    End Function

  7. Awesome. You’re the man!

  8. ukjaybrat says:

    i used this method and the “field names” of the resulting recordset are all “field 1” “field 2” etc. Is there a way to use the field names at the top of the range?

  9. Eplegrøt says:

    This is a fantastic way of getting Listobjects in Excel into recordsets. I use it to read listobjects and export them as whatever format I want (e.g. as json, CSV or .dat-files or to a database).

    However – would it not be safest and fastest to use .value2 instead of .value for the gathering of data – but then datefields would not be converted to datetime? Dates are a pain in the butt in Excel (at least for regionalized versions), so I always use the long-value in calculations and lookups anyways.

  10. This is exactly what I’m looking for, kulshresthazone and Andrew – thank you very much!

    I need to filter and sort the recordset, however, and am battling to do so in Excel 2013 on Windows 7:

    1. The (176 x 4) range I’m converting has column names (and I can access them from the recordset (using .Fields(x)) as expected.

    2. The data is transferring into the recordset from the range perfectly (tested with .CopyFromRecordset)

    3. However each of the following generate Error 3256 (“Item cannot be found in the collection corresponding to the requested name or ordinal”):
    a. adoRecordset.Sort = “[ColumnName1] ASC” (ColumnName1 is a valid column name)
    b. adoRecordset.Filter = “[ColumnName2] = 1” (there are many 1’s in ColumnName2)
    c. adoRecordset.Filter = “[ColumnName3] = ‘Metro'” (there are two Metros in ColumnName3)
    d. adoRecordset.Filter = “2 = ‘Metro'”

    I have tried, to no avail:
    1. Different combinations of CursorType and LockType parameters on the adoRecordset.open method (e.g. CursorType:=adOpenStatic, LockType:=adLockBatchOptimistic).
    2. Setting adoRecordset.CursorLocation = adUserClient
    3. Adding a Microsoft Data Access Components Installed Version reference.

    The only possibilities I can think of are:
    1. A specific combination of parameters on the open command
    2. Some other reference

    Any advice?

    Thanks very much!


    • Ok – found out why it wasn’t working: Excel/VBA is prefacing spreadsheet column names with a space so, [ColumnName1] needs to be referred to as [ ColumnName1].

      So here’s a code example, extending kulshresthazone’ code:

      Sub Test()

      Dim xlXML As Object
      Dim adoRecordset As Object
      Dim rng As Range

      Set rng = ActiveSheet.Range(“B34:E210”)
      Set adoRecordset = CreateObject(“ADODB.Recordset”)
      Set xlXML = CreateObject(“MSXML2.DOMDocument”)
      xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
      adoRecordset.Open xlXML, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

      ‘Print first Product Name in immediate Window
      Debug.Print adoRecordset.Fields(” Product Name”)

      ‘Filter rs by Brand = 1
      adoRecordset.filter = “[ Brand] = 1”

      ‘Check filter
      [R35].CopyFromRecordset adoRecordset

      ‘Sort rs by Product Name
      adoRecordset.Sort = “[ Product Name] ASC”

      ‘Check Sort
      [S35].CopyFromRecordset adoRecordset

      End Sub

  11. Charles Hunter says:

    Is there a way to “Filter” the data prior to creating the recordset? Or filtering the recordset? Excel VBA

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s