現在オフラインです。再接続するためにインターネットの接続を待っています

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

Office 2003 のサポートは終了しました

マイクロソフトでは、2014 年 4 月 8 日に Office 2003 のサポートを終了しました。この変更は、ソフトウェアの更新プログラムおよびセキュリティ オプションに影響しています。 この変更の意味および保護された状態を維持する方法について説明します。

概要
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 サイトを参照してください。

サンプルの作成手順

  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 = NothingEnd SubFunction 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 = tempArrayEnd 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 にデータを転送する方法
Transpose Mismatch XL2007
プロパティ

文書番号:246335 - 最終更新日: 06/14/2007 02:35:48 - リビジョン: 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
フィードバック
tml>