Access Tips #686 レコードセットの内容をそのままExcelのシートに出力する方法

【注】このプログラムを実行するには、VBEの[ツール]-[参照設定]メニューにおいて、「Microsoft ActiveX Data Objects 6.0 Library」などにチェックマークを付け、参照ライブラリとして追加しておく必要があります。・・・・これはAccessから外部プログラムを呼び出して利用するという設定です

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim xls As Object

'得意先テーブルを開く
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "得意先", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

'Excelオブジェクトを生成
Set xls = CreateObject("Excel.Application")

With xls
  '新しいブックを追加
  .Workbooks.Add

  '---- 例１ ----
  '1行目の1列目からレコードセットを出力
  .Cells(1, 1).CopyFromRecordset rst

  '---- 例２ ----
  'ワークシートを最後尾に追加
  .Worksheets.Add after:=.Worksheets(.Worksheets.Count)
  'レコードセットを先頭レコードに戻す
  rst.MoveFirst
  'B2セルからレコードセットを出力
  .Range("B2").CopyFromRecordset rst

  '---- 例３ ----
  .Worksheets.Add after:=.Worksheets(.Worksheets.Count)
  rst.MoveFirst
  '先頭から５レコードだけ出力
  .Cells(1, 1).CopyFromRecordset rst, 5

  '---- 例４ ----
  .Worksheets.Add after:=.Worksheets(.Worksheets.Count)
  rst.MoveFirst
  '先頭から４フィールドだけ出力
  .Cells(1, 1).CopyFromRecordset rst, , 4

  'Excelを可視状態にする
  .Visible = True
End With

Set xls = Nothing
rst.Close: Set rst = Nothing: Set cnn = Nothing

補足

CopyFromRecordsetメソッドでは「列見出し」は出力されません。
上記の例は新規のExcelブックを開いてそこにデータ出力するまでだけです。通常のExcelの操作と同様、そのままExcelを終了しようとすると保存確認のメッセージが表示されますので、結果を保存したい場合はそのファイル名を指定するなどのマニュアル操作が必要となります。
ただし、”Excel VBA”でのその操作方法を参考に上記をアレンジすることで、保存処理まで自動で行わせることもできます。またデータ出力直後にシートに対してさまざまな加工を行うこともできます。
簡単に１命令で出力できる代わりに、各レコードやフィールド個々に対する処理（たとえばデータの書式設定や計算を加えた結果を出力するなど）はできません。そのような処理が必要なときは、下記のTipsを参考にしてみてください。
参考Tips 「#432 Excelセルへ個別出力する基本パターン」


#686	レコードセットの内容をそのままExcelのシートに出力する方法	VBA
ADO（ActiveX Data Objects）の『CopyFromRecordset』メソッドを利用すると、現在開いているRecordsetオブジェクトに含まれるレコード・フィールドのデータを一括して（ひとつの命令で）Excelのワークシートに出力することができます。次の例では、「得意先」という名前のテーブルを開き、その内容をExcelのワークシートに出力しています。【注】このプログラムを実行するには、VBEの[ツール]-[参照設定]メニューにおいて、「Microsoft ActiveX Data Objects 6.0 Library」などにチェックマークを付け、参照ライブラリとして追加しておく必要があります。・・・・これはAccessから外部プログラムを呼び出して利用するという設定です Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim xls As Object '得意先テーブルを開く Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.Open "得意先", cnn, adOpenKeyset, adLockOptimistic, adCmdTable 'Excelオブジェクトを生成 Set xls = CreateObject("Excel.Application") With xls '新しいブックを追加 .Workbooks.Add '---- 例１ ---- '1行目の1列目からレコードセットを出力 .Cells(1, 1).CopyFromRecordset rst '---- 例２ ---- 'ワークシートを最後尾に追加 .Worksheets.Add after:=.Worksheets(.Worksheets.Count) 'レコードセットを先頭レコードに戻す rst.MoveFirst 'B2セルからレコードセットを出力 .Range("B2").CopyFromRecordset rst '---- 例３ ---- .Worksheets.Add after:=.Worksheets(.Worksheets.Count) rst.MoveFirst '先頭から５レコードだけ出力 .Cells(1, 1).CopyFromRecordset rst, 5 '---- 例４ ---- .Worksheets.Add after:=.Worksheets(.Worksheets.Count) rst.MoveFirst '先頭から４フィールドだけ出力 .Cells(1, 1).CopyFromRecordset rst, , 4 'Excelを可視状態にする .Visible = True End With Set xls = Nothing rst.Close: Set rst = Nothing: Set cnn = Nothing 【プログラムの説明】例１では、「Cells(1, 1)」と指定することで、ワークシートの1行目の1列目のセルを先頭としてデータを出力しています。「1」という数字を適宜書き換えることで、任意のセルから出力することができます。実行例：例２では、「Range("B2")」と指定することで、ワークシートの”B2”セルを先頭としてデータを出力しています。行番号/列番号ではなく”A1”や”D5”といったような形式でセル指定したいときはこの記法を使います。実行例： ※CopyFromRecordsetメソッドでは、Recordsetオブジェクトのカレントレコードからのデータだけが出力されます。また、出力が完了するとカレントレコードは最終レコードの次（「EOF」プロパティがTrueの状態）に移動した状態になります。よって、そのままCopyFromRecordsetメソッドを繰り返し実行してもデータはまったく出力されません。そのため、上記の例では「rst.MoveFirst」を実行することで、事前にカレントレコードを先頭に戻しています。もし１回出力して完了であればこの操作は不要です。 ※例２のデータは例１とは別のワークシートに出力するものとし、ここでは「Worksheets.Add」という命令を実行しています（これはあくまでも一般的なExcel VBAの操作です）。 CopyFromRecordsetメソッドの構文は、『Object.CopyFromRecordset(Data, MaxRows, MaxColumns)』です。「Data」にはRecordsetオブジェクトを指定します。また「MaxRows」で出力行数を、「MaxColumns」で出力列数を指定することができます（省略時は全レコード・全フィールド）。それを使って例３では、先頭から５レコードだけを出力しています。実行例：例４では、先頭から４フィールドだけ出力しています。実行例：補足 CopyFromRecordsetメソッドでは「列見出し」は出力されません。上記の例は新規のExcelブックを開いてそこにデータ出力するまでだけです。通常のExcelの操作と同様、そのままExcelを終了しようとすると保存確認のメッセージが表示されますので、結果を保存したい場合はそのファイル名を指定するなどのマニュアル操作が必要となります。ただし、”Excel VBA”でのその操作方法を参考に上記をアレンジすることで、保存処理まで自動で行わせることもできます。またデータ出力直後にシートに対してさまざまな加工を行うこともできます。簡単に１命令で出力できる代わりに、各レコードやフィールド個々に対する処理（たとえばデータの書式設定や計算を加えた結果を出力するなど）はできません。そのような処理が必要なときは、下記のTipsを参考にしてみてください。参考Tips 「#432 Excelセルへ個別出力する基本パターン」
\| Index \| Prev \| Next \|