Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

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は指定したページの1行目に移動します。

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

1ページ→1行目

2ページ→11行目

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

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

今回の場合はGetRows(Rows =10,Start=それぞれのページの最初の行)という設定になっている。

つまり1ページめは1行目から10行のデータ。

2ページ目は11行目から10行のデータをそれぞれ配列に返すようになっている。

しかしこのGetRowsメソッドはちょっとクセがあって、データの1行目が1列目に、2行目がが2列目という風に順に記載されている。

そのためTranspose関数を利用して行列変換を行っている。

GetRowsではタイトル行を取得できないので別で取得していたタイトル行とデータ部分をセルに転記して次のページ番号の処理を開始する。

最終ページの指定行数に満たない場合もよろしく処理を行ってくれます。
f:id:ExcelLover:20191102142411j:plain
59件のレコードでテストしました

まとめ

正直Pagesizeを使用するのは初めてでした。

過去に本を読んでそんな機能あったよなぁと思い読み返しながら作成してみました。

うろ覚えでも知識が頭に入ってるのは大事だなぁと思った次第です。

仕事に役立つExcel&Accessデータベース連携テクニック

仕事に役立つExcel&Accessデータベース連携テクニック