[XL2003] [INFO] Visual Basic から Excel にデータを転送する方法

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

概要

この資料では、Microsoft Visual Basic アプリケーションから Microsoft Excel にデータを転送するさまざまな方法について説明します。また、最適なソリューションの選択に役立つように、それぞれの方法の長所と短所についても説明します。

詳細

Excel ブックにデータを転送するのに最もよく使用されるのは、オートメーションと呼ばれる技法です。オートメーションを使用することにより、ブック内のデータの場所の指定やブックの書式設定、その他の実行時の設定を最も柔軟に行うことができます。オートメーションを使用する場合、以下に示すように、いくつかの方法でデータを転送することができます。
  • セルごとにデータを転送する。
  • 配列のデータを特定のセル範囲に転送する。
  • CopyFromRecordset メソッドを使用して、ADO レコードセットのデータを特定のセル範囲に転送する。
  • ODBC または OLEDB データ ソースのクエリの結果を含む QueryTable オブジェクトを Excel ワークシート上に作成する。
  • クリップボードにデータを転送し、クリップボードの内容を Excel ワークシートに貼り付ける。
Excel にデータを転送する方法で、オートメーションを必要としない方法もあります。アプリケーションをサーバー側で実行している場合、この方法を使用することで、クライアント側のデータ処理の負荷の大半を回避することができます。オートメーションを使用せずにデータ転送を行うには、以下の方法を使用します。
  • データをタブ区切りまたはコンマ区切りテキスト ファイルに変換し、Excel でテキストを区切ってワークシートのセルに格納する。
  • ADO を使用してデータをワークシートに転送する。
  • 動的データ交換 (DDE) を使用してデータを Excel に転送する。
以下に、それぞれの方法について詳しく説明します。

オートメーションを使用してセルごとにデータを転送する

オートメーションを使用することにより、ブックのセルに 1 つずつデータを転送することができます。
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Last Name"
   oSheet.Range("B1").Value = "First Name"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Doe"
   oSheet.Range("B2").Value = "John"
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
セルを 1 つずつ転送する方法は、データが少量の場合にはまったく問題のない方法です。データをブックのどの場所にでも格納でき、実行時に条件に応じてセルの書式を設定することもできます。しかし、大量のデータを Excel ブックに転送する場合はこの方法は適切ではありません。実行時に取得する Range オブジェクトごとにインターフェイス要求が発生するため、データ転送の速度が低下します。また、Microsoft Windows 95 および Windows 98 ではインターフェイス要求に 64 KB の制限があります。インターフェイス要求が 64 KB の制限に達するか制限を超えた場合、オートメーション サーバー (Excel) が応答を停止するか、メモリ不足のエラー メッセージが表示されることがあります。Windows 95 および Windows 98 でのインターフェイス要求の制限について参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
216400 PRB: Cross-Process COM Automation Can Hang Client Application on Win95/98
上で説明したように、セルごとにデータを転送する方法を使用できるのは、データ量が少ない場合に限られます。Excel に大量のデータ セットを転送する必要がある場合は、この後で説明する方法のいずれかを使用することをお勧めします。

Excel のオートメーションのサンプル コードを参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
219151 HOWTO: Automate Microsoft Excel from Visual Basic

オートメーションを使用してデータの配列をブックの特定のセル範囲に転送する

データの配列を、以下のように複数のセルを含むセル範囲に一度に転送することができます。
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   'Create an array with 3 columns and 100 rows
   Dim DataArray(1 To 100, 1 To 3) As Variant
   Dim r As Integer
   For r = 1 To 100
      DataArray(r, 1) = "ORD" & Format(r, "0000")
      DataArray(r, 2) = Rnd() * 1000
      DataArray(r, 3) = DataArray(r, 2) * 0.7
   Next
   'Add headers to the worksheet on row 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
   'Transfer the array to the worksheet starting at cell A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
セルごとではなく、配列を使用してデータを転送すると、大量のデータ転送の際のパフォーマンスが著しく向上することが実感できます。上記のコードから抜粋した以下の行では、ワークシートの中にある 300 のセルにデータを転送します。
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
このコードでは、2 つのインターフェイス要求 (1 つは Range メソッドが返す Range オブジェクトで、もう 1 つは Resize メソッドが返す Range オブジェクト) が発生します。一方、セルごとにデータを転送する方法では、300 個の Range オブジェクトへのインターフェイスに対して要求が発生します。データを一度に転送することが可能な場合は、この方法を使用してインターフェイス要求の回数を少なくすることで、パフォーマンスが向上します。

オートメーションを使用して ADO レコードセットをワークシートの範囲に転送する

Excel 2000 では、ADO (または DAO) レコードセットをワークシートの特定のセル範囲に転送するための CopyFromRecordset メソッドが導入されています。以下のコードでは、Excel 2000、Excel 2002、または Office Excel 2003 で、CopyFromRecordset メソッドを使用して、ノースウィンド サンプル データベースの [受注] テーブルのデータを、自動で転送する方法を示します。
: 必要に応じて、ノースウィンド サンプル データベース (Northwind.mdb) のパスを変更してください。
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("受注", , adCmdTable)
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Transfer the data to Excel
   oSheet.Range("A1").CopyFromRecordset rs
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
   
   'Close the connection
   rs.Close
   conn.Close
Excel 97 でも CopyFromRecordset メソッドが提供されていますが、このメソッドは DAO レコードセットでのみ使用できます。Excel 97 の CopyFromRecordset メソッドでは ADO はサポートされていません。

ADO および CopyFromRecordset メソッドの使用方法の詳細について参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

オートメーションを使用して QueryTable オブジェクトをワークシート上に作成する

QueryTable オブジェクトは、外部のデータ ソースから返されたデータを使用して作成されたテーブルを表します。Microsoft Excel を自動化する場合、OLEDB または SQL 文字列が設定された ODBC データ ソースへの接続文字列を指定することで QueryTable を作成することができます。レコードセットの生成とワークシート内の指定位置への挿入は、Excel 側で行われます。QueryTables オブジェクトを使用する場合、CopyFromRecordset メソッドを使用する場合と比較して以下のような利点があります。
  • レコードセットの作成およびワークシートへの配置は Excel によって実行されます。
  • クエリを QueryTable オブジェクトに保存しておくことにより、後でクエリを更新して、更新後のレコードセットを取り出すことができます。
  • ワークシートに新しい QueryTable オブジェクトを追加すると、ワークシート上のセル内の既存のデータを変更して新しいデータを取得するように指定できます (詳細については RefreshStyle プロパティを参照してください)。
以下のコードでは、Excel 2000、Excel 2002、または Office Excel 2003 を自動化し、ノースウィンド サンプル データベースのデータを使用して、Excel ワークシートに新しい QueryTable オブジェクトを作成する方法を示します。
: 必要に応じて、ノースウィンド サンプル データベース (Northwind.mdb) のパスを変更してください。
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Create the QueryTable
   Dim sNWind As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   Dim oQryTable As Object
   Set oQryTable = oSheet.QueryTables.Add( _
   "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";", oSheet.Range("A1"), "Select * from 受注")
   oQryTable.RefreshStyle = xlInsertEntireRows
   oQryTable.Refresh False
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

クリップボードを使用する

データをワークシートに転送するための方法として、Windows クリップボードを使用することもできます。ワークシートの複数のセルにデータを貼り付けるには、列の区切りとしてタブ文字を、行の区切りとして改行文字を挿入した文字列をコピーします。以下のコードでは、Visual Basic で Clipboard オブジェクトを使用してデータを Excel に転送する方法を示します。
   'Copy a string to the clipboard
   Dim sData As String
   sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
           & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
           & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
   Clipboard.Clear
   Clipboard.SetText sData
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   
   'Paste the data
   oBook.Worksheets(1).Range("A1").Select
   oBook.Worksheets(1).Paste
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Excel で行と列が適切に区切られるように、区切り文字の入ったテキスト ファイルを作成する

Excel では、タブ区切りやコンマ区切りのファイルを開いて、区切りに従って適切にデータをセルに読み込むことができます。この機能は、オートメーションで使用することがほとんどないような大容量のデータをワークシートに転送するのに適しています。この方法は、テキスト ファイルをサーバー側で生成できるため、クライアント/サーバー型のアプリケーションに適しています。クライアント側では、必要に応じてオートメーションを使用して、サーバー側で生成されたテキスト ファイルを開きます。

以下のコードでは、ADO レコードセットからコンマ区切りのテキスト ファイルを作成する方法を示します。
: 必要に応じて、Excel.exe およびノースウィンド サンプル データベース (Northwind.mdb) のパスを変更してください。
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sData As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("受注", , adCmdTable)
   
   'Save the recordset as a tab-delimited file
   sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
   Open "C:\Test.txt" For Output As #1
   Print #1, sData
   Close #1
    
   'Close the connection
   rs.Close
   conn.Close
   
   'Open the new text file in Excel
   Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
      Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Excel では、テキスト ファイルの拡張子が .csv の場合、自動的にそのファイルはコンマ区切りであると見なされ、ファイルを開く際にテキスト ファイル ウィザードは表示されません。同様に、ファイルの拡張子が .txt の場合、自動的にタブを区切り文字としてそのファイルが読み込まれます。

上のコード サンプルでは、Shell ステートメントを使用して Excel を起動し、コマンド ライン引数としてファイル名を使用しています。上のサンプルではオートメーションは使用していませんが、必要に応じて、以下のように最小限のオートメーションを使用してテキスト ファイルを開き、Excel ブック形式で保存することができます。
: このコードを使用するには、[プロジェクト] メニューの [参照設定] をクリックし、[参照可能なライブラリ ファイル] ボックスの一覧の [Microsoft Excel N.N Object Library] チェック ボックス (N.N はバージョン番号) をオンにする必要があります。
   'Create a new instance of Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Open the text file
   Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
   
   'Save as Excel workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
   oExcel.Quit
Visual Basic アプリケーションでファイル入出力を使用する方法の詳細について参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
172267 RECEDIT.VBP Demonstrates File I/O in Visual Basic
以下の資料にも、Visual Basic for Applications でのファイル入出力の制御に関する説明とサンプル コードが記載されています。
File Access with Visual Basic for Applications

ADO を使用してデータをワークシートに転送する

Microsoft Jet OLE DB プロバイダを使用して、既存の Excel ブック上のテーブルにレコードを追加することができます。Excel における "テーブル" とは、単に名前が定義された特定のセル範囲を指します。セル範囲の最初の行はヘッダー (フィールド名) になっている必要があり、それ以降の行はすべてレコードです。以下の手順では、MyTable という名前の空のテーブルを含むブックを作成する方法を説明します。
  1. Excel で新しいブックを作成します。
  2. Sheet1 のセル A1:B1 に以下のヘッダーを入力します。

    A1 : FirstName B1 : LastName
  3. セル B1 に右詰めの書式を設定します。
  4. セル A1:B1 を選択します。
  5. [挿入] メニューの [名前] をポイントし、[定義] をクリックします。MyTable という名前を入力し、[OK] をクリックします。
  6. 新しいブックを C:\Book1.xls として保存し、Excel を終了します。
ADO を使用して MyTable にレコードを追加するには、以下のようなコードを使用します。
   'Create a new connection object for Book1.xls
   Dim conn As New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Bill', 'Brown')"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Joe', 'Thomas')"
   conn.Close
この方法でテーブルにレコードを追加すると、ブック内の書式は維持されます。上の例では、列 B に追加される新しいフィールドは右詰めになります。行に追加される各レコードには、直前の行の書式が適用されます。

ワークシートの 1 つ以上のセルにレコードを追加する場合、そのセルに含まれていたデータはすべて上書きされることに注意してください。つまり、新しいレコードを追加する場合、ワークシート内の行は下にシフトしません。ワークシート上のデータの配置を考える際にはこの点に注意する必要があります。

ADO を使用して Excel ブックにアクセスする方法の詳細について参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
195951 HOWTO: Query and Update Excel Data Using ADO From ASP

DDE を使用して Excel にデータを転送する

Excel との通信およびデータ転送を行う方法として、オートメーションの代わりに DDE を使用することも可能です。ただし、オートメーションおよび COM の導入により、DDE は他のアプリケーションとの通信の手段として最適な方法ではなくなっています。他の方法が使用できない場合にのみ DDE を使用してください。

DDE を使用して Excel にデータを転送するには以下の方法があります。
  • LinkPoke メソッドを使用して特定のセル範囲にデータを挿入する。

    または
  • LinkExecute メソッドを使用して Excel で実行するコマンドを送信する。
以下のサンプル コードでは、Excel との DDE 通信を確立し、ワークシート上のセルにデータを挿入し、コマンドを実行する方法を示します。このサンプル コードを使用して LinkTopic の Excel|MyBook.xls に対する DDE 通信を確立するには、実行中の Excel で MyBook.xls という名前のブックが既に開かれている必要があります。

: このサンプルでは、Text1 は Visual Basic フォームのテキスト ボックス コントロールを表します。
   'Initiate a DDE communication with Excel
   Text1.LinkMode = 0
   Text1.LinkTopic = "Excel|MyBook.xls"
   Text1.LinkItem = "R1C1:R2C3"
   Text1.LinkMode = 1
   
   'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
   Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
                "four" & vbTab & "five" & vbTab & "six"
   Text1.LinkPoke
   
   'Execute commands to select cell A1 (same as R1C1) and change the font
   'format
   Text1.LinkExecute "[SELECT(""R1C1"")]"
   Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
   
   'Terminate the DDE communication
   Text1.LinkMode = 0
Excel で LinkPoke を使用する場合、LinkItem のセル範囲を列行 (R1C1) 形式で指定します。複数のセルにデータを挿入する場合、タブで列を、改行で行を区切った文字列を使用できます。

LinkExecute を使用して Excel でコマンドを実行する場合、Excel Macro Language (XLM) の構文でコマンドを使用する必要があります。Excel 97 以降のバージョンには XLM のマニュアルは含まれていません。XLM のマニュアルの入手方法について参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
143466 Download the Excel 97 Macro Functions Help File for XLM Documentation
Excel との通信に DDE を使用することはお勧めしません。オートメーションを使用する方法が最も柔軟で、Excel の新しい機能を十分に活用することができます。

関連情報

関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
306022 HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET
306022 [HOW TO] Visual Basic .NET を使用してデータを Excel ブックに転送する方法

関連情報

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 247412 (最終更新日 2003-10-10) を基に作成したものです。

この資料に含まれているサンプル コード/プログラムは英語版を前提に書かれたものをありのままに記述しており、日本語環境での動作は確認されておりません。

プロパティ

文書番号: 247412 - 最終更新日: 2003年11月12日 - リビジョン: 4.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
キーワード:?
kbinfo kbautomation kbdde KB247412
"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