ADODBを利用して指定した件数ずつ新しいシートにデータを転記する方法

いつも勉強させていただいているinfomentさんのblogで以下のような記事を公開されていました。

infoment.hatenablog.com

ADODBにまさに上記のような機能があったはずと思いだして自分なりに作ってみた。

早速コード

Sub ページ分割()

'参照設定でMicrosoft ActiveX Data Objects XXX(最新版) Libraryにチェックを入れておく
     Dim myCon As ADODB.Connection
     Dim myRS As ADODB.Recordset
     
     Set myCon = New ADODB.Connection
     Set myRS = New ADODB.Recordset
     
'Excel2007以降の設定。2003まではコメントアウト部分。
     myCon.Provider = "Microsoft.ACE.OLEDB.12.0"
     myCon.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
'     myCon.Provider = "Microsoft.JET.OLEDB.4.0"
'     myCon.Properties("Extended Properties") = "Excel 8.0;IMEX=1"
     
     myCon.Open ThisWorkbook.FullName
     
     Dim mySQL As String
     mySQL = "select * from [元データ$]"
     myRS.Open mySQL, myCon, adOpenStatic
     
'転記する単位を設定。設定した件数を転記したら次のシートを作成する。
     myRS.PageSize = 10
     
'見出し行の取得
     Dim myHeader As Variant
     Dim n As Long
     ReDim myHeader(myRS.Fields.Count - 1)
     For n = 0 To myRS.Fields.Count - 1
          myHeader(n) = myRS.Fields(n).Name
     Next
     
     Dim i As Long
     Dim myVar As Variant
     For i = 1 To myRS.PageCount
          myRS.AbsolutePage = i
          Worksheets.Add after:=Worksheets(Worksheets.Count)
          myVar = Application.WorksheetFunction.Transpose(myRS.GetRows(myRS.PageSize))

          Range("A1").Resize(, UBound(myHeader, 1) + 1).Value = myHeader
          Range("A2").Resize(UBound(myVar), UBound(myVar, 2)).Value = myVar
     Next
     
     myRS.Close
     Set myRS = Nothing
     myCon.Close
     Set myCon = Nothing
End Sub

Recordset.Pagesize

例によってなんちゃって個人情報で作成したデータでテストしてみます。

今回のシートに指定した件数ずつ転記するにはADODBのRecordset.Pagesizeを利用する。

Pagesizeに指定した数字の箇所毎にページ番号をつけるようなイメージ。
f:id:ExcelLover:20191102142140j:plain

Recorset.AblolutePageは指定したページの１行目に移動します。

Pagesizeを10に指定するのであれば

1ページ→1行目

2ページ→11行目

GetRowsメソッドでRecordsetオブジェクトの内容を配列として返す。

Recorset.GetRows([Rows],[Start],[Fields])

引数	内容
Rows	取得するレコードの数を示す値。既定値はadGetRowsRestします。未指定の場合は全レコードを返す。
Start	読み込みを開始するレコードの位置を指定。指定しない場合は現在のレコード位置から。
Fields	指定したフィールドのデータのみ返します。