■ VB から Excel にデータを転送する方法 ■ ■ 概要
此の資料では、Microsoft Visual Basic アプリケーションから Microsoft Excel にデータを転送する様々 な方法に付いて説明する。亦、最適なソリューションの選択に役立つ様に、夫々れの方法の長所と短所 に付いても説明する。 ■ 詳細 Excel ブックにデータを転送するのに最も良く使用されるのは、オートメーションと呼ばれる技法で有 る。オートメーションを使用する事に依り、ブック内のデータの場所の指定やブックの書式設定、其他 の実行時の設定を最も柔軟に行う事が出来る。オートメーションを使用する場合、以下に示す様に、幾 つかの方法でデータを転送する事が出来る。 ・セル毎にデータを転送する。 ・配列のデータを特定のセル範囲に転送する。 ・CopyFromRecordset メソッドを使用して、ADO レコードセットのデータを特定のセル範囲に転送す る。
・ODBC 又は OLEDB データソースのクエリの結果を含む QueryTable オブジェクトを Excel ワークシ ート上に作成する。 ・クリップボードにデータを転送し、クリップボードの内容をExcel ワークシートに貼り付ける。 Excel にデータを転送する方法で、オートメーションを必要としない方法も有る。アプリケーションを サーバー側で実行して居る場合、此の方法を使用する事で、クライアント側のデータ処理の負荷の大半 を回避する事が出来る。オートメーションを使用せずにデータ転送を行うには、以下の方法を使用する。 ・データをタブ区切りかコンマ区切りテキストファイルに変換し、Excel でテキストを区切ってワーク シートのセルに格納する。 ・ADO を使用してデータをワークシートに転送する。 ・動的データ交換（DDE）を使用してデータを Excel に転送する。 以下に、夫々れの方法に付いて詳しく説明する。 オートメーションを使用してセル毎にデータを転送する オートメーションを使用する事に依り、ブックのセルに1 つ宛データを転送する事が出来る。 Dim Ex As Object Dim Wb As Object Dim Ws As Object
'Start a new workbook in Excel
Set Ex = CreateObject("Excel.Application") Set Wb = Ex.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook Set Ws = Wb.Worksheets(1)
Ws.Range("A1").Value = "Last Name" Ws.Range("B1").Value = "First Name" Ws.Range("A1:B1").Font.Bold = True Ws.Range("A2").Value = "Doe" Ws.Range("B2").Value = "John" 'Save the Workbook and Quit Excel Wb.SaveAs "C:¥Book1.xls"
セルを1 つ宛転送する方法は、データが尐量の場合には全く問題の無い方法で有る。データをブックの 何の場所にでも格納出来、実行時に条件に応じてセルの書式を設定する事も出来る。併し、大量のデー タをExcel ブックに転送する場合は、此の方法は適切ではない。実行時に取得する Range オブジェク ト毎にインターフェイス要求が発生する為、データ転送の速度が低下する。亦、Microsoft Windows 95 及びWindows 98 ではインターフェイス要求に 64 KB の制限が有る。インターフェイス要求が 64 KB の制限に達するか制限を超えた場合、オートメーションサーバー（Excel）が応答を停止するか、メモ リ不足のエラーメッセージが表示される事が有る。 上で説明した様に、セル毎にデータを転送する方法を使用出来るのは、データ量が尐ない場合に限られ る。Excel に大量のデータセットを転送する必要が有る場合は、此の後で説明する方法の孰れかを使用 する事を推奨する。 オートメーションを使用してデータの配列をブックの特定のセル範囲に転送する データの配列を、以下の様に複数のセルを含むセル範囲に一度に転送する事が出来る。 Dim Ex As Object Dim Wb As Object Dim Ws As Object
'Start a new workbook in Excel
Set Ex = CreateObject("Excel.Application") Set Wb = Ex.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 Ws = Wb.Worksheets(1)
Ws.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax") 'Transfer the array to the worksheet starting at cell A2
Ws.Range("A2").Resize(100, 3).Value = DataArray 'Save the Workbook and Quit Excel
Wb.SaveAs "C:¥Book1.xls" Ex.Quit
セル毎ではなく、配列を使用してデータを転送すると、大量のデータ転送の際のパフォーマンスが著し く向上する事が実感出来る。上記のコードから抜粋した以下の行では、ワークシートの中に有る300 の セルにデータを転送する。
Ws.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 Ex As Object Dim Wb As Object Dim Ws As Object Set Ex = CreateObject("Excel.Application") Set Wb = Ex.Workbooks.Add Set Ws = Wb.Worksheets(1)
'Transfer the data to Excel
Ws.Range("A1").CopyFromRecordset rs
'Save the Workbook and Quit Excel Wb.SaveAs "C:¥Book1.xls"
Ex.Quit
'Close the connection rs.Close
conn.Close
Excel 97 でも CopyFromRecordset メソッドが提供されて居るが、此のメソッドは DAO レコードセッ トで而巳使用出来る。Excel 97 の CopyFromRecordset メソッドでは ADO はサポートされて居ない。 オートメーションを使用して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 Ex As Object Dim Wb As Object Dim Ws As Object Set Ex = CreateObject("Excel.Application") Set Wb = Ex.Workbooks.Add Set Ws = Wb.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 = Ws.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";", Ws.Range("A1"), "Select * from 受注")
oQryTable.RefreshStyle = xlInsertEntireRows oQryTable.Refresh False
'Save the Workbook and Quit Excel Wb.SaveAs "C:¥Book1.xls"
Ex.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 Ex As Object
Dim Wb As Object
Set Ex = CreateObject("Excel.Application") Set Wb = Ex.Workbooks.Add
'Paste the data
Wb.Worksheets(1).Range("A1").Select Wb.Worksheets(1).Paste
'Save the Workbook and Quit Excel Wb.SaveAs "C:¥Book1.xls"
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 x.x Object Library］チェックボックス をオンにする必要が有る（x.x はバージョン番号）。
'Create a new instance of Excel Dim Ex As Object
Dim Wb As Object Dim Ws As Object
Set Ex = CreateObject("Excel.Application") 'Open the text file
Set Wb = Ex.Workbooks.Open("C:¥Test.txt") 'Save as Excel workbook and Quit Excel
Wb.SaveAs "C:¥Book1.xls", xlWorkbookNormal Ex.Quit
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 つ以上のセルにレコードを追加する場合、其のセルに含まれて居たデータは総て上書 きされる事に注意され度い。詰まり、新しいレコードを追加する場合、ワークシート内の行は下にシフ トしない。ワークシート上のデータの配置を考える際には、此の点に注意する必要が有る。 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 のマニュアルは含まれて居ない。 Excel との通信に DDE を使用する事は推奨しない。オートメーションを使用する方法が最も柔軟で、 Excel の新しい機能を充分に活用する事が出来る。
■ 対象
此の資料は、以下の製品に付いて記述した物で有る。 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
