【ExcelVBA】CopyFromRecordsetの使い方

更新日：2023/12/01

ExcelのVBAで使用できるCopyFromRecordsetはメソッドは ADO または DAO の Recordsetオブジェクトのカレントレコードから内容を、Excelのシートにコピーします。
フィールドを一つ一つ読み込んでセルにセットするよりも、効率よくコピーできます。

このページの目次

1CopyFromRecordsetの使い方
2CopyFromRecordsetの使用例
テーブルの内容全てコピー
フィールド名も出力
行数と列数を指定
任意の行からコピーする
コピーする列（フィールド）を指定する
セル書式を変更しないでコピー

CopyFromRecordsetの使い方

CopyFromRecordsetはRangeオブジェクトのメソッドです。
構文は、次のようになっています。

Rangeオブジェクト.CopyFromRecordset(Data, MaxRows, MaxColumns)

Data: Recordsetオブジェクト
MaxRows: 最大レコード（行）数。省略可能
MaxColumns: 最大フィールド（列）数。省略可能

戻り値：

コピーしたレコード数がLong型で返ります。

Rangeオブジェクトの範囲：

基本的にRangeオブジェクトは一つのセルを指定して、そのセルを開始位置として右方向にフィールドを、下方向にレコードをコピーします。

Range("A1").CopyFromRecordset

複数のセルが含まれる場合は、左上のセルが基準となります。
例えば次のコードは、B8が基準となります

Range("C8:B11").CopyFromRecordset ' B8:C11の範囲

ADOについて次のページで紹介しています。参考にしてください。
■【ExcelVBA】Accessと連携してデータ取得や更新などの操作を行う方法

CopyFromRecordsetの使用例

CopyFromRecordsetのコード例です。

今回は次のようなテーブルを対象とした実行例を載せています。

テーブルの内容全てコピー

Recordset.Openでテーブルを開くと、テーブルの内容を全てシートにコピーできます。

Sub CopyFromRecordsetSample1()
    
    Dim con As New ADODB.connection
    con.Open "Provider=Microsoft.ACE.OLEDB.16.0;" _
                    & ";Data Source=Database1.accdb;"
    
    Dim rs As New ADODB.Recordset
    rs.Open "テーブル1", con
    
    Dim copyNum As Long
    copyNum = Sheets("sheet1").Range("A1").CopyFromRecordset( rs )
    
    MsgBox copyNum & "行をコピーしました"

    rs.Close: Set rs = Nothing
    con.Close: Set con = Nothing
    
End Sub

コードを実行すると、セルA1にテーブル1の内容がコピーされます。

フィールド名も出力

CopyFromRecordsetはフィールド名をコピーしてくれないので、シートに出力するときはレコードセットからフィールド名を取得してセルにセットします。

Sub CopyFromRecordsetSample2()
    
    Dim con As New ADODB.connection
    con.Open "Provider=Microsoft.ACE.OLEDB.16.0;" _
                    & ";Data Source=Database1.accdb;"
    
    Dim rs As New ADODB.Recordset
    rs.Open "テーブル1", con

    Dim i As Long, count As Long, copyNum As Long    
    count = rs.Fields.count - 1
    
    With Sheets("sheet1")
        For i = 0 To count
            Debug.Print rs.Fields(i).Name
            .Cells(1, i + 1).value = rs.Fields(i).Name
        Next
    
        copyNum = .Range("A2").CopyFromRecordset(rs)
    End With
    
    MsgBox copyNum & "行をコピーしました" 
    rs.Close: Set rs = Nothing
    con.Close: Set con = Nothing
End Sub

実行すると、次のようになります。

行数と列数を指定

CopyFromRecordsetメソッドの、MaxRowsとMaxColumnsを指定すると出力する行数（レコード数）と列数（フィールド数）制限できます。

例えば前項のコードのCopyFromRecordsetメソッド行を、次のように変更します。

copyNum = Sheets("sheet1").Range("A1").CopyFromRecordset(rs, 2, 2)

結果は、最初の２つのレコードと、２つのフィールドがコピーされます。

任意の行からコピーする

カレントレコードを移動しておくと、1行目と2行目を飛ばして3行目からコピーするなど、任意の行からコピーすることもできます。
前項のコードのCopyFromRecordsetメソッド行を、次のように変更します。

rs.Move 3 ' 4レコード目に移動
copyNum = Sheets("sheet1").Range("A1").CopyFromRecordset(rs)

最初のレコードは0なので、上のコードは4番目のレコードに移動しています。
CopyFromRecordsetは、その位置からシートにコピーしています。

コピーする列（フィールド）を指定する

一部のフィールドのみをコピーしたいときは、Access側でフィールドのみを選択したクエリを作成して、そのクエリ名でレコードセットをOpenします。
または、SQL文でレコードセットをOpenします。

次のコードはSQL文で、あいさつフィールドと日付フィールドのみを抽出しています。

Sub CopyFromRecordsetSample3()
    Dim con As New ADODB.connection
    con.Open "Provider=Microsoft.ACE.OLEDB.16.0;" _
                    & ";Data Source=Database1.accdb;"
    
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT あいさつ,日付 FROM テーブル1", con
    
    Dim copyNum As Long
    copyNum = Sheets("sheet1").Range("A1").CopyFromRecordset(rs)

    MsgBox copyNum & "行をコピーしました"
    
    rs.Close: Set rs = Nothing
    con.Close: Set con = Nothing
    
End Sub

セル書式を変更しないでコピー

CopyFromRecordsetは、フィールドの型に合わせてセルの書式を特定のものに変更します。
例えば日付を2023年1月1日のような形式で表示しようと思って書式設定していても、CopyFromRecordsetでコピーされた日付は、2023/1/1のような "/" で区切ったものに変更されます。

対策方法の一つ目は、CopyFromRecordsetをやめて、1フィールドごとにセルにセットします。
こうすることで、セルの書式を保ったままシートにコピーできます。

Dim rg As Range
Set rg = Sheets("sheet1").Range("A1")
    
Dim i As Long, count As Long, recCount As Long
count = rs.Fields.count - 1
recCount = 0
    
Do Until rs.EOF
    For i = 0 To count
        rg.Offset(recCount, i).value = rs.Fields(i).value
    Next
    rs.MoveNext
    recCount = recCount + 1
Loop

もう一つの方法は、コピー先の外側のセルに書式を設定しておき、CopyFromRecordset実行後に書式をコピーします。

Dim filedNum As Long
filedNum = rs.Fields.count

Dim rg As Range
Set rg = Sheets("sheet1").Range("A2")

Dim recCount As Long
copyNum = rg.CopyFromRecordset(rs)

 ' E1からfiledNum個のセルをコピー
Range(Range("E1"), Range("E1").Offset(0, filedNum - 1)).Copy
 ' コピーしたセルの書式をCopyFromRecordsetの出力先セルにペースト
Range(rg, rg.Offset(copyNum - 1, filedNum - 1)).PasteSpecial (xlPasteFormats)
　' コピーモード終了
Application.CutCopyMode = False

更新日：2023/12/01

書いた人（管理人）：けーちゃん

TOPページ ▶ プログラミング ▶ VBA ▶

記事の内容について

こんにちはけーちゃんです。
説明するのって難しいですね。

「なんか言ってることおかしくない？」
たぶん、こんなご意見あると思います。

裏付けを取りながら記事を作成していますが、僕の勘違いだったり、そもそも情報源の内容が間違えていたりで、正確でないことが多いと思います。
そんなときは、ご意見もらえたら嬉しいです。

掲載コードについては事前に動作確認をしていますが、貼り付け後に体裁を整えるなどをした結果動作しないものになっていることがあります。
生暖かい視線でスルーするか、ご指摘ください。

ご意見、ご指摘はこちら。
https://note.affi-sapo-sv.com/info.php

このサイトは、リンクフリーです。大歓迎です。