CopyFromRecordsetのデータ取得数

長年使っている機能でも知らないことがあるものですね。
CopyFromRecordsetで取得するデータ数を指定できることを最近になって初めて知りました。

CopyFromRecordsetとは

ADO.Recorsetで抽出したデータをワークシートに貼り付ける機能です。

ADO
ActiveX Data Objectsの略。
アプリケーションからAccess、SQL Serverをはじめ、Oracleなどのさまざまな データベースへ同一の指示で処理を行うことができる。
ADOを経由することでExcelをデータベースと同様に扱うことができるようになり、SQLで指示を出すことが出来るようになります。

基本的な使い方は以下のようになります。
以下のコードでSQLで指定して取得したデータをA1セルに貼り付けることが出来ます。

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

    Dim myWS As Worksheet
    Dim i As Long
    
    Set myWS = Worksheets("貼付")
    
    For i = 1 To myRS.Fields.Count
        myWS.Cells(1, i).Value = myRS.Fields(i - 1).Name
    Next
    myWS.Range("A2").CopyFromRecordset myRS　　　　　　’←これ
    
    myRS.Close: Set myRS = Nothing
    myCon.Close: Set myCon = Nothing
End Sub

このようにCopyFromRecordsetを使用する際、取得した全データを貼り付けることしか出来ないと思っていました。

CopyFromRecordsetの引数

Microsoftのサイトで確認すると以下のようになっています。

Range.CopyFromRecordset (Data, MaxRows, MaxColumns)

名前	必須 / オプション	データ型	説明
Data	必須	バリアント型	セル範囲にコピーする Recordset オブジェクトを指定します。
MaxRows	省略可能	バリアント型 (Variant)	ワークシートにコピーするレコードの最大数を指定します。 この引数を省略すると、Recordset オブジェクトのすべてのレコードをコピーします。
MaxColumns	省略可能	バリアント型 (Variant)	ワークシートにコピーするフィールドの最大数を指定します。 この引数を省略すると、Recordset オブジェクトのすべてのフィールドをコピーします。

Range.CopyFromRecordset メソッド (Excel) | Microsoft Learn

CopyFromRecordsetを使用する際に第一引数しか指定してこなかったのですが、第2・第３引数が存在していました。

※明確に表示されていたにもかかわらず、気に留めていませんでした

第２引数の効果

先程のコードをCopyFromRecordsetの箇所を以下のように第２引数を指定するように変更して実行します。

myWS.Range("A2").CopyFromRecordset myRS　5

そうするとデータの取得結果はデータの先頭から５件のみを取得するように挙動が変更されました。

この事例では先頭から５件を取得していますが、第２の引数のデータ取得数指定はデータの先頭からではなく、カレントレコードから指定した件数を取得します。

カレントレコード
データベースやソフトウェア内で、現在操作対象となっている、または選択されている1つのレコードのこと

CopyFromRecordsetの箇所を以下のように変更して実行してみます。

myRS.MoveNext
myWS.Range("A2").CopyFromRecordset myRS, 5

MoveNextはカレントレコードを１つ次に進めるコードです。
つまりカレントレコードはデータの１行目ではなく２行目にある状態です。

その状態でCopyFromRecordsetを実行すると、データは１行目からではなく、２行目から５件を取得するように変更されています。

まとめ

慣れ親しんで使っているだけに思い込みも強い状態になっていました。

Recorsetの第２引数を指定する方法を活用することで、データを分割して処理する方法に利用することが出来ます。