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

[アーティクル]
06/06/2024
適用対象:

Excel 2010, Office Excel 2007, Office Excel 2003

概要

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

詳細情報

Excel ブックにデータを転送するために最も一般的に使用される方法は Automation です。オートメーションを使用すると、ブック内のデータの場所を指定できる柔軟性が最大限に高まるだけでなく、ブックの書式設定や実行時のさまざまな設定を行うことができます。 Automation では、データを転送するためにいくつかの方法を使用できます。

セルごとにデータセルを転送する
配列内のデータをセル範囲に転送する
CopyFromRecordset メソッドを使用して ADO レコードセット内のデータをセル範囲に転送する
ODBC または OLEDB データソースに対するクエリの結果を含む Excel ワークシートに QueryTable を作成する
データをクリップボードに転送し、クリップボードの内容を Excel ワークシートに貼り付けます

また、必ずしもオートメーションを必要としない Excel にデータを転送するために使用できるメソッドもあります。アプリケーションサーバー側を実行している場合、これは、クライアントからデータの処理の大部分を取り除く場合に適した方法です。 Automation なしでデータを転送するには、次の方法を使用できます。

Excel が後でワークシート上のセルに解析できるタブ区切りテキストファイルまたはコンマ区切りテキストファイルにデータを転送する
ADO を使用してワークシートにデータを転送する
動的データ交換 (DDE) を使用して Excel にデータを転送する

以下のセクションでは、これらの各ソリューションについて詳しく説明します。

メモ Microsoft Office Excel 2007 を使用する場合は、ブックを保存するときに新しい Excel 2007 ブック (*.xlsx) ファイル形式を使用できます。これを行うには、次のコード例で次のコード行を見つけます。

oBook.SaveAs "C:\Book1.xls"

このコードを次のコード行に置き換えます。

oBook.SaveAs "C:\Book1.xlsx"

また、既定では、Northwind データベースは Office 2007 には含まれません。ただし、Northwind データベースは Microsoft Office Online からダウンロードできます。

Automation を使用してセルごとにデータセルを転送する

Automation を使用すると、ワークシートに一度に 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

データの量が少ない場合、セルごとにデータセルを転送することは、完全に許容される方法です。ブック内の任意の場所にデータを配置できる柔軟性があり、実行時に条件付きでセルを書式設定できます。ただし、Excel ブックに転送する大量のデータがある場合は、この方法はお勧めしません。実行時に取得した Range オブジェクトごとにインターフェイス要求が発生するため、この方法でデータを転送するのが遅くなる可能性があります。さらに、Microsoft Windows 95 と Windows 98 には、インターフェイス要求に対して 64K の制限があります。インターフェイス要求でこの 64k の制限に達した場合、Automation サーバー (Excel) が応答を停止するか、メモリ不足を示すエラーが発生する可能性があります。

もう一度、セルごとにデータセルを転送することは、少量のデータに対してのみ許容されます。大規模なデータセットを Excel に転送する必要がある場合は、後で説明するソリューションのいずれかを検討する必要があります。

Excel の自動化のサンプルコードの詳細については、「 Visual Basic から Microsoft Excel を自動化する方法」を参照してください。

オートメーションを使用して、ワークシート上の範囲にデータの配列を転送する

データの配列は、一度に複数のセルの範囲に転送できます。

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 サンプルデータベースの Orders テーブルの内容を転送する方法を示しています。

'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("Orders", , 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

メモ Northwind データベースの Office 2007 バージョンを使用する場合は、コード例の次のコード行を置き換える必要があります。

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"

このコード行を次のコード行に置き換えます。

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"

Excel 97 には CopyFromRecordset メソッドも用意されていますが、DAO レコードセットでのみ使用できます。 Excel 97 の CopyFromRecordset は ADO をサポートしていません。

ADO と CopyFromRecordset メソッドの使用方法の詳細については、「オートメーションを使用して ADO レコードセットから Excel にデータを転送する方法」を参照してください。

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

QueryTable オブジェクトは、外部データソースから返されるデータから構築されたテーブルを表します。 Microsoft Excel の自動化中に、SQL 文字列と共に OLEDB または ODBC データソースに接続文字列を提供するだけで、QueryTable を作成できます。 Excel は、レコードセットを生成し、指定した場所のワークシートに挿入する責任を負います。 QueryTables を使用すると、CopyFromRecordset メソッドよりもいくつかの利点があります。

Excel は、レコードセットの作成とワークシートへの配置を処理します。
クエリは QueryTable と共に保存できるため、後で更新して更新されたレコードセットを取得できます。
新しい QueryTable がワークシートに追加されると、ワークシート上のセルに既に存在するデータを、新しいデータに合わせてシフトするように指定できます (詳細については、RefreshStyle プロパティを参照してください)。

次のコードは、Excel 2000、Excel 2002、または Office Excel 2003 を自動化して、Northwind サンプルデータベースのデータを使用して Excel ワークシートに新しい QueryTable を作成する方法を示しています。

'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 Orders")    oQryTable.RefreshStyle = xlInsertEntireRows    
oQryTable.Refresh False        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

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

Windows クリップボードは、ワークシートにデータを転送するためのメカニズムとしても使用できます。ワークシート上の複数のセルにデータを貼り付けるには、列がタブ文字で区切られ、行がキャリッジリターンで区切られた文字列をコピーできます。次のコードは、Visual Basic でクリップボードオブジェクトを使用して 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 では、タブ区切りまたはコンマ区切りのファイルを開き、データを正しくセルに解析できます。この機能は、オートメーションをほとんど使用している場合は、ワークシートに大量のデータを転送する場合に利用できます。テキストファイルはサーバー側で生成できるため、これはクライアント/サーバーアプリケーションに適した方法である可能性があります。その後、適切な場所で Automation を使用して、クライアントでテキストファイルを開くことができます。

次のコードは、ADO レコードセットからコンマ区切りのテキストファイルを作成する方法を示しています。

'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("Orders", , 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

注: Northwind データベースの Office 2007 バージョンを使用する場合は、コード例の次のコード行を置き換える必要があります。

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
   sNWind & ";"

このコード行を次のコード行に置き換えます。

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

テキストファイルに .CSV 拡張子がある場合、Excel はテキストインポートウィザードを表示せずにファイルを開き、ファイルがコンマ区切りであると自動的に想定します。同様に、ファイルに .TXT 拡張子がある場合、Excel はタブ区切り記号を使用してファイルを自動的に解析します。

前のコードサンプルでは、Shell ステートメントを使用して Excel が起動され、ファイルの名前がコマンドライン引数として使用されていました。前のサンプルではオートメーションは使用されませんでした。ただし、必要に応じて、最小限の量の Automation を使用してテキストファイルを開き、Excel ブック形式で保存できます。

'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

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

Microsoft Jet OLE DB プロバイダーを使用すると、既存の Excel ブック内のテーブルにレコードを追加できます。 Excel の "テーブル" は、定義された名前を持つ範囲にすぎません。範囲の最初の行にはヘッダー (またはフィールド名) を含め、後続のすべての行にレコードが含まれている必要があります。次の手順では、MyTable という名前の空のテーブルを使用してブックを作成する方法を示します。

Excel 97、Excel 2000、Excel 2003

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

Excel 2007

Excel 2007 で、新しいブックを開始します。
Sheet1 のセル A1:B1 に次のヘッダーを追加します。

A1: FirstName B1: LastName
セル B1 を右揃えに書式設定します。
[A1:B1] を選択します。
リボンの [数式] タブをクリックし、[ 名前の定義] をクリックします。 MyTable という名前を入力し、[OK] をクリック します。
新しいブックを C:\Book1.xlsx として保存し、Excel を終了します。

ADO を使用して MyTable テーブルにレコードを追加するには、次のコード例のようなコードを使用します。

'Create a new connection object for Book1.xls    
Dim conn As New ADODB.Connection    
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _       
   "Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Scott', 'Brown')"    
conn.Execute "Insert into MyTable (FirstName, LastName)" & _       
   " values ('Jane', 'Dow')"    
conn.Close

この方法でテーブルにレコードを追加すると、ブック内の書式設定が維持されます。前の例では、列 B に追加された新しいフィールドは、正しい配置で書式設定されています。行に追加された各レコードは、その上の行から形式を借用します。

レコードがワークシート内のセルまたはセルに追加されると、それらのセル内のデータが上書きされることに注意してください。つまり、新しいレコードを追加しても、ワークシート内の行は "プッシュダウン" されません。ワークシート上のデータのレイアウトを設計するときは、この点に留意する必要があります。

注:

ADO を使用するか DAO を使用して Excel ワークシート内のデータを更新する方法は、Office 2003 Service Pack 2 (SP2) をインストールした後、または Microsoft サポート技術情報の記事904018に含まれる Access 2002 の更新プログラムをインストールした後に、Access 内の Visual Basic for Application 環境では機能しません。このメソッドは、Word、Excel、Outlook などの他の Office アプリケーションの Visual Basic for Application 環境で適切に機能します。

詳細については、以下の記事をご覧ください。

Office Access 2003 または Access 2002 で Excel ブックにリンクされているテーブルのデータを変更、追加、または削除することはできません

ADO を使用して Excel ブックにアクセスする方法の詳細については、「 ASP から ADO を使用して Excel データを照会および更新する方法」を参照してください。

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

DDE は、Excel と通信し、データを転送するための手段として Automation に代わる手段です。ただし、Automation と COM の登場により、DDE は他のアプリケーションと通信するための推奨される方法ではなくなり、他のソリューションが利用できない場合にのみ使用する必要があります。

DDE を使用して Excel にデータを転送するには、LinkPoke メソッドを使用してデータを特定のセル範囲に突き刺すか、LinkExecute メソッドを使用して Excel が実行するコマンドを送信します。

次のコード例は、ワークシート上のセルにデータを突き刺してコマンドを実行できるように、Excel との DDE 会話を確立する方法を示しています。このサンプルを使用して、DDE 会話を LinkTopic Excel に正常に確立する |MyBook.xls、MyBook.xls という名前のブックは、実行中の Excel インスタンスで既に開かれている必要があります。

注:

Excel 2007 を使用する場合は、新しい .xlsx ファイル形式を使用してブックを保存できます。次のコード例のファイル名を必ず更新してください。この例では、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 マクロ言語 (XLM) の構文で Excel にコマンドを指定する必要があります。 XLM ドキュメントは、Excel バージョン 97 以降には含まれていません。
DDE は、Excel との通信に推奨されるソリューションではありません。自動化により、最大限の柔軟性が提供され、Excel が提供する新機能へのアクセスが増えます。

次の方法で共有

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

概要

詳細情報

Automation を使用してセルごとにデータセルを転送する

オートメーションを使用して、ワークシート上の範囲にデータの配列を転送する

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

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

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

Excel が行と列に解析できる区切りテキストファイルを作成する

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

Excel 97、Excel 2000、Excel 2003

Excel 2007

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

フィードバック

その他のリソース

次の方法で共有

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

概要

詳細情報

Automation を使用してセルごとにデータ セルを転送する

オートメーションを使用して、ワークシート上の範囲にデータの配列を転送する

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

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

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

Excel が行と列に解析できる区切りテキスト ファイルを作成する

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

Excel 97、Excel 2000、Excel 2003

Excel 2007

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

フィードバック

その他のリソース

Automation を使用してセルごとにデータセルを転送する

Excel が行と列に解析できる区切りテキストファイルを作成する