サインイン

Select the product you need help with

[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

(http://support.microsoft.com/kb/216400/EN-US/ )

PRB: Cross-Process COM Automation Can Hang Client Application on Win95/98

上で説明したように、セルごとにデータを転送する方法を使用できるのは、データ量が少ない場合に限られます。Excel に大量のデータセットを転送する必要がある場合は、この後で説明する方法のいずれかを使用することをお勧めします。

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

219151

(http://support.microsoft.com/kb/219151/EN-US/ )

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

(http://support.microsoft.com/kb/246335/EN-US/ )

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

(http://support.microsoft.com/kb/172267/EN-US/ )

RECEDIT.VBP Demonstrates File I/O in Visual Basic

以下の資料にも、Visual Basic for Applications でのファイル入出力の制御に関する説明とサンプルコードが記載されています。

File Access with Visual Basic for Applications

(http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fexcel%2fcontent%2ffileio%2ffileio.asp)

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

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

Excel で新しいブックを作成します。
Sheet1 のセル A1:B1 に以下のヘッダーを入力します。

A1 : FirstName B1 : LastName
セル B1 に右詰めの書式を設定します。
セル A1:B1 を選択します。
[挿入] メニューの [名前] をポイントし、[定義] をクリックします。MyTable という名前を入力し、[OK] をクリックします。
新しいブックを 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

(http://support.microsoft.com/kb/195951/EN-US/ )

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

(http://support.microsoft.com/kb/143466/EN-US/ )

Download the Excel 97 Macro Functions Help File for XLM Documentation

Excel との通信に DDE を使用することはお勧めしません。オートメーションを使用する方法が最も柔軟で、Excel の新しい機能を十分に活用することができます。

先頭へ戻る | フィードバック

プロパティ

文書番号: 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、その関連会社またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

先頭へ戻る | フィードバック