Recordsetのデータを分割して取得する方法

前回は「CopyFromRecordsetのデータ取得数」について紹介しました。

www.excellovers.com

今回はもう１つ別の機能を紹介して、Recordsetのデータを簡単に分割して取得する方法を説明します。

１９件の元データを５件ずつ別のシートに転記する事例で説明します。

コード

コード全文は下記のようになります。

Sub DataPageSplit()
    Dim myCon As ADODB.Connection
    Set myCon = New ADODB.Connection
    Dim myRS As ADODB.Recordset
    Set myRS = New ADODB.Recordset
    Dim mySQL As String
    
'--ADODB.Connection作成
    myCon.Provider = "Microsoft.ACE.OLEDB.16.0"
    myCon.Properties("Extended Properties") = _
                    "Excel 12.0;HDR=YES;IMEX=1"
    myCon.Open ThisWorkbook.FullName
    
'--ADODB.Recordset作成
    mySQL = "select * from [Data$]"
    myRS.Open mySQL, myCon, adOpenStatic

'--Recordsetデータ分割・シート転記
    myRS.PageSize = 5
    Dim myWS As Worksheet
    Dim i As Long
    Dim p As LongPtr
    
    For p = 1 To myRS.PageCount
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Set myWS = ActiveSheet
        
        For i = 1 To myRS.Fields.Count
            myWS.Cells(1, i).Value = myRS.Fields(i - 1).Name
        Next
        myWS.Range("A2").CopyFromRecordset myRS, myRS.PageSize
    Next p
    
'--クロージング
    myRS.Close: Set myRS = Nothing
    myCon.Close: Set myCon = Nothing
End Sub

Pagesizeプロパティ

SQLを実行しRecordsetオブジェクトに取得したデータに分割線を設定します。
分割線の設定には、RecordsetオブジェクトのPagesizeプロパティを使用します。

上記コード内では以下の部分が該当します。

myRS.PageSize = 5

Pagesizeプロパティに設定した数値ごとにデータに分割線が設定されます。
今回は5を設定しましたので、５件ごとに分割線が設定されることになります。

Pagesizeを設定することで、指定した単位でデータが何分割されるのかを簡単に取得できるようになります。

For p = 1 To myRS.PageCount

RecordsetオブジェクトのPageCountプロパティを使用することで指定した単位で何ページに分割されたのかを取得することが出来ます。
今回の例では「4」ページという値が取得されます。

このPageCountをFor文に設定することで繰返し処理の回数を設定しています。
元データ数を単純に指定単位で割算して算出する場合、端数の処理が必要になりますが、PageCountを使用することでそのような処理が不要になり、可読性も向上します。

データの分割取得

データの処理回数の設定ができましたので、次はデータの取得部分を設定していきます。

データの取得数に関しては前回ご紹介したCopyFromRecordsetのデータ取得数を設定することで実現可能です。

myWS.Range("A2").CopyFromRecordset myRS, myRS.PageSize

CopyFromRecordsetの第２引数を設定することで、Recordsetから指定した数のデータを取得することができます。

CopyFromRecordsetはカレントレコードから指定した数のデータを取得します。
第２引数を省略した場合はカレントレコードから後ろのデータを全て取得します。

Recordset作成時はカレントレコードは一番最初の行に設定されています。
今回は第２引数にPageSizeを指定することで１～５行目のデータ、つまり１ページ目のデータを取得するようになっています。

CopyFromRecordsetを実行後にカレントレコードは取得したデータ数分後ろに移動します。

この状態で再びCopyFromRecordsetを行うことで、６～１０行目のデータ(２ページ目)のデータを取得することが出来ます。

これを最終ページまで繰り返すことで、指定した行数でデータを分割して取得することが可能になります。