オートメーションを使用して ADO レコードセットのデータを Excel に転送する方法

文書翻訳 文書翻訳
文書番号: 246335 - 対象製品
すべて展開する | すべて折りたたむ

目次

概要

Excel のオートメーションを使用して ADO レコードセットの内容を Microsoft Excel ワークシートに転送することができます。使用可能な方法は、自動化する Excel のバージョンによって異なります。Excel 97、Excel 2000、Excel 2002、Excel 2003 および Excel 2007 に含まれる CopyFromRecordset メソッドを使用すると、レコードセットを特定の範囲に転送することができます。Excel 2000 以降の CopyFromRecordset は、DAO レコードセットまたは ADO レコードセットをコピーするのに使用できますが、Excel 97 の CopyFromRecordset は、DAO レコードセットのみをサポートしています。ADO レコードセットを Excel 97 に転送するには、レコードセットから配列を作成し、その配列の内容を特定の範囲に入力する方法を使用します。

この資料では、両方のアプローチについて説明します。記載されているサンプル コードは、ADO レコードセットを Excel 97、Excel 2000、Excel 2002、Excel 2003、または Excel 2007 に転送する方法を示しています。

詳細

以下のサンプル コードは、Microsoft Visual Basic からオートメーションを使用して ADO レコードセットを Microsoft Excel ワークシートにコピーする方法を示しています。コードでは、最初に Excel のバージョンをチェックします。Excel 2000 以降が検出された場合は、効率がよくコードが少なくて済む CopyFromRecordset メソッドを使用します。Excel 97 以前のバージョンが検出された場合は、ADO レコードセット オブジェクトの GetRows メソッドを使用してレコードセットを配列にコピーした後、レコードが 1 次元目 (行)、フィールドが 2 次元目 (列) になるように配列の次元を入れ替えます。次に、Excel ワークシートの特定のセル範囲に配列をコピーします (配列のコピーは、ワークシートの各セルをループせず、一度に行います)。

サンプル コードでは Microsoft Office に含まれている Northwind サンプル データベースを使用しています。Microsoft Office のインストール時にデフォルトのフォルダを選択した場合、このデータベースは次の場所にあります。

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Northwind データベースがコンピュータの別のフォルダにある場合、以下のコードで指定されているデータベースのパスを変更する必要があります。

システムに Northwind データベースがインストールされていない場合は、Microsoft Office セットアップの [追加と削除] オプションを使用して、サンプル データベースをインストールすることができます。

: 2007 Microsoft Office をインストールした場合は、Northwind データベースはインストールされません。Northwind 2007 を入手するには、以下のマイクロソフト Web サイトを参照してください。
http://office.microsoft.com/ja-jp/templates/TC012289971041.aspx?pid=CT101428651041

サンプルの作成手順

  1. Visual Basic を起動し、新しい標準 EXE プロジェクトを作成します。デフォルトでは Form1 が作成されます。
  2. Form1 に CommandButton コントロールを追加します。
  3. [プロジェクト] メニューの [参照設定] をクリックします。Microsoft ActiveX Data Objects 2.1 Library への参照を追加します。
  4. Form1 のコード セクションに次のコードを貼り付けます。
    Private Sub Command1_Click()
        Dim cnt As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim xlApp As Object
        Dim xlWb As Object
        Dim xlWs As Object
    
        
        Dim recArray As Variant
        
        Dim strDB As String
        Dim fldCount As Integer
        Dim recCount As Long
        Dim iCol As Integer
        Dim iRow As Integer
        
        ' Set the string to the path of your Northwind database
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
      
        ' Open connection to the database
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
        
        ''When using the Access 2007 Northwind database
        ''comment the previous code and uncomment the following code.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
            
        ' Open recordset based on Orders table
        rst.Open "Select * From 受注", cnt
        
        ' Create an instance of Excel and add a workbook
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
      
        ' Display Excel and give user control of Excel's lifetime
        xlApp.Visible = True
        xlApp.UserControl = True
        
        ' Copy field names to the first row of the worksheet
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
            
        ' Check version of Excel
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
             
            ' Copy the recordset to the worksheet, starting in cell A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            'Note: CopyFromRecordset will fail if the recordset
            'contains an OLE object field or array data such
            'as hierarchical recordsets
            
        Else
            'EXCEL 97 or earlier: Use GetRows then copy array to Excel
        
            ' Copy recordset to an array
            recArray = rst.GetRows
            'Note: GetRows returns a 0-based array where the first
            'dimension contains fields and the second dimension
            'contains records. We will transpose this array so that
            'the first dimension contains records, allowing the
            'data to appears properly when copied to Excel
            
            ' Determine number of records
    
            recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
            
    
            ' Check the array for contents that are not valid when
            ' copying the array to an Excel worksheet
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    ' Take care of Date fields
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ' Take care of OLE object fields or array fields
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'next record
            Next iCol 'next field
                
            ' Transpose and Copy the array to the worksheet,
            ' starting in cell A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
        ' Auto-fit the column widths and row heights
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
        ' Close ADO objects
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
        ' Release Excel references
        Set xlWs = Nothing
        Set xlWb = Nothing
    
        Set xlApp = Nothing
    
    End Sub
    
    
    Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 0-based array (v)
        
        Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
        Dim tempArray As Variant
        
        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)
        
        ReDim tempArray(Xupper, Yupper)
        For X = 0 To Xupper
            For Y = 0 To Yupper
                tempArray(X, Y) = v(Y, X)
            Next Y
        Next X
        
        TransposeDim = tempArray
    
    
    End Function
    
  5. F5 キーを押してプロジェクトを実行します。Form1 が表示されます。
  6. Form1 のコマンド ボタンをクリックし、"受注" テーブルの内容が Excel の新しいブックに表示されることを確認します。
CopyFromRecordset の使用

効率性とパフォーマンス上の理由から、CopyFromRecordset メソッドをお勧めします。Excel 97 の CopyFromRecordset メソッドは DAO レコードセットのみをサポートしているため、Excel 97 で ADO レコードセットを CopyFromRecordset に渡すと、次のエラーが表示されます。
実行時エラー '430':
クラスはオートメーションまたは予測したインターフェースをサポートしていません。
サンプル コードでは、Excel のバージョンをチェックして、Excel 97 には CopyFromRecordset を使用しないようにしているため、このエラーを回避できます。

: CopyFromRecordset を使用するときは、ADO レコードセットまたは DAO レコードセットに OLE オブジェクト フィールド、または階層レコードセットなどの配列データが含まれていないことを確認する必要があります。レコードセットにいずれかの種類のフィールドが含まれていると、CopyFromRecordset メソッドは失敗し、次のエラー メッセージが表示されます。
実行時エラー '-2147467259':
'CopyFromRecordset' メソッドは失敗しました: 'Range' オブジェクト
GetRows の使用

Excel 97 が検出された場合は、ADO レコードセットの GetRows メソッドを使用して、レコードセットを配列にコピーします。GetRows から返された配列をそのままワークシート内の特定のセル範囲に割り当てると、データは行に沿って表示されず、列に沿って表示されます。たとえば、レコードセットに 2 つのフィールドと 10 個の行がある場合、配列は 2 行と 10 列で表示されます。そのため、配列を特定のセル範囲に割り当てる前に、TransposeDim() 関数を使用して配列の次元を入れ替える必要があります。配列を特定のセル範囲に割り当てるときには、次のような制限があります。

次の制限は、配列を Excel の Range オブジェクトに割り当てる際に適用されます。
  • 配列に OLE オブジェクト フィールド、または階層レコードセットなどの配列データを含めることはできません。サンプル コードでは、この条件をチェックし、"Array Field" を表示して、Excel でフィールドを表示できないことをユーザーに伝えます。

  • 配列に 1900 年以前の日付の日付型フィールドを含めることはできません (「関連情報」に記載されている資料を参照してください)。この問題を回避するために、サンプル コードでは、日付型フィールドをバリアント型文字列に変更しています。
配列を Excel ワークシートにコピーする前に、TransposeDim() 関数を使用して配列の次元を入れ替えます。配列の次元を入れ替えるための独自の関数を作成する代わりに、サンプル コードを修正して Excel の Transpose 関数を使用し、次のように配列をセルに割り当てることができます。
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
TransposeDim() 関数の代わりに Excel の Transpose 関数を使用して配列を入れ替える場合は、Transpose 関数の次の制限に注意してください。
  • 配列に 255 文字を超える要素を含めることはできません。
  • 配列に Null 値を含めることはできません。
  • 配列内の要素数が 5461 を超えることはできません。
配列を Excel ワークシートにコピーする際に上記の制限を考慮に入れないと、次のいずれかの実行時エラーが発生する場合があります。
実行時エラー '13':
型が一致しません。
実行時エラー '5':
プロシージャ呼び出しまたは引数が不正です。
実行時エラー '1004':
アプリケーション定義またはオブジェクト定義のエラーです。

関連情報

異なるバージョンの Excel に配列を渡す場合の制限の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
177991 [XL] オートメーションを使用して Excel に配列を渡すときの制限事項
関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
146406 [XL] DAO を使用して Access から Excel にテーブルを取得する方法
215965 [XL2000] 1900 年より前の日付が含まれると、12:00:00 AM と表示される
243394 [HOWTO] MFC を使用して、DAO レコードセットをオートメーションで Excel にコピーする方法
247412 [XL2003] [INFO] Visual Basic から Excel にデータを転送する方法

プロパティ

文書番号: 246335 - 最終更新日: 2007年6月14日 - リビジョン: 5.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
キーワード:?
kbexpertiseinter kbautomation kbhowto KB246335
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com