Microsoft Office操作（EXCEL）

Transcription

1 VB から Excel にデータを転送する方法 概要 此の資料では Microsoft Visual Basic アプリケーションから Microsoft Excel にデータを転送する様々な方法に付いて説明する 亦 最適なソリューションの選択に役立つ様に 夫々れの方法の長所と短所に付いても説明する 詳細 Excel ブックにデータを転送するのに最も良く使用されるのは オートメーションと呼ばれる技法で有る オートメーションを使用する事に依り ブック内のデータの場所の指定やブックの書式設定 其他の実行時の設定を最も柔軟に行う事が出来る オートメーションを使用する場合 以下に示す様に 幾つかの方法でデータを転送する事が出来る セル毎にデータを転送する 配列のデータを特定のセル範囲に転送する CopyFromRecordset メソッドを使用して ADO レコードセットのデータを特定のセル範囲に転送する ODBC 又は OLEDB データソースのクエリの結果を含む QueryTable オブジェクトを Excel ワークシート上に作成する クリップボードにデータを転送し クリップボードの内容を Excel ワークシートに貼り付ける Excel にデータを転送する方法で オートメーションを必要としない方法も有る アプリケーションをサーバー側で実行して居る場合 此の方法を使用する事で クライアント側のデータ処理の負荷の大半を回避する事が出来る オートメーションを使用せずにデータ転送を行うには 以下の方法を使用する データをタブ区切りかコンマ区切りテキストファイルに変換し Excel でテキストを区切ってワークシートのセルに格納する ADO を使用してデータをワークシートに転送する 動的データ交換 (DDE) を使用してデータを Excel に転送する 以下に 夫々れの方法に付いて詳しく説明する オートメーションを使用してセル毎にデータを転送する オートメーションを使用する事に依り ブックのセルに 1 つ宛データを転送する事が出来る 'Start a new workbook in Excel 'Add data to cells of the first worksheet in the new workbook 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" -1-

2 セルを 1 つ宛転送する方法は データが尐量の場合には全く問題の無い方法で有る データをブックの何の場所にでも格納出来 実行時に条件に応じてセルの書式を設定する事も出来る 併し 大量のデータを Excel ブックに転送する場合は 此の方法は適切ではない 実行時に取得する Range オブジェクト毎にインターフェイス要求が発生する為 データ転送の速度が低下する 亦 Microsoft Windows 95 及び Windows 98 ではインターフェイス要求に 64 KB の制限が有る インターフェイス要求が 64 KB の制限に達するか制限を超えた場合 オートメーションサーバー (Excel) が応答を停止するか メモリ不足のエラーメッセージが表示される事が有る 上で説明した様に セル毎にデータを転送する方法を使用出来るのは データ量が尐ない場合に限られる Excel に大量のデータセットを転送する必要が有る場合は 此の後で説明する方法の孰れかを使用する事を推奨する オートメーションを使用してデータの配列をブックの特定のセル範囲に転送する データの配列を 以下の様に複数のセルを含むセル範囲に一度に転送する事が出来る 'Start a new workbook in Excel '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 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 セル毎ではなく 配列を使用してデータを転送すると 大量のデータ転送の際のパフォーマンスが著しく向上する事が実感出来る 上記のコードから抜粋した以下の行では ワークシートの中に有る 300 のセルにデータを転送する Ws.Range("A2").Resize(100, 3).Value = DataArray 此のコードでは 2 つのインターフェイス要求 (1 つは Range メソッドが返す Range オブジェクトで もう 1 つは Resize メソッドが返す Range オブジェクト ) が発生する 一方 セル毎にデータを転送する方法では 300 個の Range オブジェクトへのインターフェイスに対して要求が発生する データを一度に転送する事が可能な場合は 此の方法を使用してインターフェイス要求の回数を尐なくする事で パフォーマンスが向上する -2-

3 オートメーションを使用して 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 'Transfer the data to Excel Ws.Range("A1").CopyFromRecordset rs '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 プロパティを参照され度い ) -3-

4 以下のコードでは Excel 2000 Excel 2002 又は Office Excel 2003 を自動化し ノースウィンドサンプルデータベースのデータを使用して Excel ワークシートに新しい QueryTable オブジェクトを作成する方法を示す 注 : 必要に応じて ノースウィンドサンプルデータベース (Northwind.mdb) のパスを変更され度い 'Create a new workbook in Excel '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 クリップボードを使用する データをワークシートに転送する為の方法と仕て 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 'Paste the data Wb.Worksheets(1).Range("A1").Select Wb.Worksheets(1).Paste -4-

5 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 'Open the text file Set Wb = Ex.Workbooks.Open("C: Test.txt") 'Save as Excel workbook and Quit Excel, xlworkbooknormal -5-

6 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 つ以上のセルにレコードを追加する場合 其のセルに含まれて居たデータは総て上書きされる事に注意され度い 詰まり 新しいレコードを追加する場合 ワークシート内の行は下にシフトしない ワークシート上のデータの配置を考える際には 此の点に注意する必要が有る DDE を使用して Excel にデータを転送する Excel との通信やデータ転送を行う方法と仕て オートメーションの代わりに DDE を使用する事も可能で有る 但し オートメーションや COM の導入に依り DDE は他のアプリケーションとの通信の手段と仕て最適な方法ではなく成って居る 他の方法が使用出来ない場合に而巳 DDE を使用され度い DDE を使用して Excel にデータを転送するには 以下の方法が有る LinkPoke メソッドを使用して 特定のセル範囲にデータを挿入する 又は LinkExecute メソッドを使用して Excel で実行するコマンドを送信する 以下のサンプルコードでは Excel との DDE 通信を確立し ワークシート上のセルにデータを挿入し コマンドを実行する方法を示す 此のサンプルコードを使用して LinkTopic の Excel MyBook.xls に対する DDE 通信を確立するには 実行中の Excel で MyBook.xls と謂う名前のブックが既に開かれて居る必要が有る -6-

7 注 : 此のサンプルでは 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