VB実用⑩　エクセル操作Ⅳ

Size: px

Start display at page:

Download "VB実用⑩　エクセル操作Ⅳ"

しらんこけい
5 years ago
Views:

VB でエクセル操作 Ⅳ VB 2005 10 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 (De Facto Standard) で有ると謂う事が出来る今回は Visual Basic からエクセルのセルに値を設定する手法を重点的に学ぶ

1 VB でエクセル操作 Ⅳ VB プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 (De Facto Standard) で有ると謂う事が出来る今回は Visual Basic からエクセルのセルに値を設定する手法を重点的に学ぶ前回迄に学んだエクセル操作の為のオブジェクトの生成を元に変数や配列に格納されたデータ値や計算式をエクセルのセルに代入したりファイルやピクチャボックスから画像を挿入する亦事務処理等に活用する事の出来る実用的な手法と仕てエクセルの印刷を用いて帳票等を印刷する Visual Basic 単独では面倒な印刷がエクセルの印刷を用いる事に依りプレビュー等を含め簡単に行う事が出来る様に成る今回の課題項目オブジェクトの参照設定 ( 事前バインディング実行時バインディング ) オブジェクト変数の宣言 (Object 型 As Object) オブジェクトのインスタンス生成 (CreateObject 関数 ) エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートの操作 (Cells プロパティ Range プロパティ ) ワークシートの操作 (Value プロパティ Formula プロパティ ) ワークシートの (HorizontalAlignment プロパティ Interior プロパティ ) ワークシートの印刷 (PrintPreview メソッド PrintOut メソッド ) 今回の重点項目エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートの操作 (Cells プロパティ Range プロパティ ) ワークシートの操作 (Value プロパティ Formula プロパティ ) ワークシートの (HorizontalAlignment プロパティ Interior プロパティ ) ワークシートの印刷 (PrintPreview メソッド PrintOut メソッド ) -1-

オブジェクトプロパティ一覧ボタン 1 ボタン 2 ピクチャボックスボタン 3 ボタン 4 ボタン 5 ファイルオープンダイアログコントロールの種類プロパティプロパティの設定値フォーム Name excel4 FormBorderStyle FixedSingle StartPosition CenterScreen エクセル書込ピクチャボックス Name pictemp

2 オブジェクトプロパティ一覧ボタン 1 ボタン 2 ピクチャボックスボタン 3 ボタン 4 ボタン 5 ファイルオープンダイアログコントロールの種類プロパティプロパティの設定値フォーム Name excel4 FormBorderStyle FixedSingle StartPosition CenterScreen エクセル書込ピクチャボックス Name pictemp Appearance 0 フラット BorderStyle None Image 任意の画像 SizeMode AutoSize ボタン1 Name btnopen Font MS 明朝太字 10 エクセルを開くボタン2 Name btnset Font MS 明朝太字 10 データ設定ボタン3 Name btnpreview Font MS 明朝太字 10 印刷プレビューボタン4 Name btnprint Font MS 明朝太字 10 印刷ボタン5 Name btnfinish Font MS 明朝太字 10 終了ファイルオープンダイアログ Name dlgfiler FileName -2- 空白

3 Public Class excel4 プログラムリスト ' フォームレベルでグローバルな定数の宣言 ( エクセル定数 ) Private Const xlcenter As Integer = ' &HFFFFEFF4 ' フォームレベルでグローバルな変数の宣言 'Private EX As Object ' Excel.Application 'Private WB As Object ' Excel.Workbook 'Private WS As Object ' Excel.Worksheet Private EX As Excel.Application Private WB As Excel.Workbook Private WS As Excel.Worksheet Private SD As String ' 格納場所 ( 起動パス ) Private FN As String ' エクセルファイル名此処で宣言した変数は同じモジュール内の総てのサブプロシージャで値の参照と設定を行う事が出来る ' フォームが読み込まれた時の処理 Private Sub excel4_load(byval sender As Object, ByVal e As System.EventArgs) _ Handles Me.Load ' エクセルファイルの格納場所の設定 ( 起動パス ) SD = Application.StartupPath : If Not SD.EndsWith(" ") Then SD &= " " ' ウィンドウを常に手前に表示 Me.TopMost = True ' ファイルを開くダイアログの設定 dlgfiler.filter = " エクセルファイル (*.xls) *.xls 総てのファイル (*.*) *.*" dlgfiler.initialdirectory = SD ' ボタン ( エクセルを開く ) がクリックされた時の処理 Private Sub btnopen_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnopen.click ' ファイルを開くダイアログの表示 If dlgfiler.showdialog = Windows.Forms.DialogResult.OK Then ' エクセルのファイル名の設定 FN = dlgfiler.filename If Not FN.Substring( FN.LastIndexOf( "." ) + 1 ) = "xls" Then FN = "" : Exit Sub End If End If ' エクセルオブジェクトの新しいインスタンスの生成 EX = CreateObject( "Excel.Application" ) ' ファイルを指定してワークブックのオープン WB = EX.Workbooks.Open( FN ) ' シート名を指定してオブジェクトの定義 WS = WB.Worksheets( "Sheet1" ) ' エクセルの表示 EX.Visible = True CreateObject 関数は事前バインディングと実行時バインディングの孰れでも使用する事が出来る指定したファイルで Workbooks コレクションを開くシート名には操作対象とするシートの名称を指定する特にエクセルを表示する必要の無い場合は此の処理を省略する -3-

4 ' ボタン ( 終了 ) がクリックされた時の処理 Private Sub btnfinish_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnfinish.click ' エクセルファイルが開いて居ればクローズ If WS IsNot Nothing Then ' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) End If ' フォームをメモリから消去して終了 Me.Dispose( ) End ' ボタン ( データの設定 ) がクリックされた時の処理 Private Sub btnset_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnset.click Dim I, J, D( 4, 4 ) As Integer Dim R As Long = 1, F As String = "" ' 変数データの書込 For I = 0 To 200 Step 10 R += 1 WS.Cells( R, 1 ).Value = I Next WS.Cells( 1, 1 ).HorizontalAlignment = xlcenter WS.Range( "A1:A22" ).Interior.Color = 128 * &H * &H WS.Columns( "B:B" ).ColumnWidth /= 2 ' 計算式の書込 WS.Cells( 1, 3 ).Value = " 計算式 " WS.Cells( 1, 4 ).Value = "12+34" WS.Cells( 2, 4 ).Value = "A2+A3" WS.Cells( 1, 5 ).Formula = "=12+34" WS.Cells( 2, 5 ).Formula = "=A2+A3" WS.Range( "C1:D2" ).HorizontalAlignment = xlcenter WS.Range( "C1:E2" ).Interior.Color = 255 * &H * &H オブジェクトの比較は Is 演算子 (IsNot 演算子 ) を用いて行うジェネラルプロシージャを呼び出す ( 制御を移す ) には Call ステートメントを用いる実際には此処迄丁寧に記述する必要は無いのだが使用した総てのフォームをメモリから消去した上でプログラムを正しく終了すると謂う癖を付けて置く事が望ましい此処で宣言した変数は宣言したサブプロシージャ内でしか値の参照と設定を行う事が出来ないエクセルのセルに値を書き込むには Value プロパティを使用する Color 構造体の値を其の儘設定する事が出来ない為各要素に分解して数値化して居るエクセルのセルに計算式を書き込むには Formula プロパティを使用する ' 配列データの書込 Color.FromArgb(128, 128, 255) WS.Cells( 1, 7 ).Value = " 配列値 " For I = 0 To 4 For J = 0 To 4 セルの範囲を指定して配列の値 D( I, J ) = I * 5 + J を一括して書き込む事が出来る Next セルに個別に書き込むより高速で Next WS.Range( "G2:K6" ).Value = D 有る WS.Cells( 1, 7 ).HorizontalAlignment = xlcenter WS.Range( "G1:K1" ).MergeCells = True WS.Range( "G1:K6" ).Interior.Color = 255 * &H * &H ' 画像の貼付 ( セルに直接貼付 ) 画像が原寸大で表示されない WS.Cells( 9, 3 ).Value = " セルに直接貼付 " F = SD & "akubi.jpg" WS.Cells( 9, 3 ).Activate( ) WS.Pictures.Insert( F ).Select( ) エクセルのシートに画像を貼り付ける事も出来る -4-

5 ' 画像の貼付 (OLE オブジェクトを使用 ) 画像が原寸大で表示される WS.Cells( 9, 11 ).Value = "OLE オブジェクト使用 " WS.Cells( 10, 11 ).Activate() 此の方法では画像に枠線が表示 WS.OLEObjects.Add( FileName:=F ) され何故か消去出来ない ' 画像の貼付 ( クリップボードを使用 ) 画像が原寸大で表示される WS.Cells( 9, 13 ).Value = " クリップボード使用 " My.Computer.Clipboard.Clear( ) 此の方法では枠線が表示されず My.Computer.Clipboard.SetImage( pictemp.image ) に亦原寸大で表示される WS.Range( "M10" ).Select( ) WS.Paste( ) ' ボタン ( 印刷プレビュー ) がクリックされた時の処理 Private Sub btnpreview_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnpreview.click ' フォームの非表示とエクセルの表示 Me.Hide( ) ' EX.Visible = True ' プレビュー画面の表示 WS.PrintPreview( ) ' エクセルの非表示とフォームの表示 ' EX.Visible = False Me.Show( ) ' ボタン ( 印刷 ) がクリックされた時の処理 Private Sub btnprint_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnprint.click ' 印刷用シートの印刷 WS.PrintOut( ) 此処ではフォームを最前面に表示して居る為一旦非表示に仕て居るエクセルの印刷プレビューを表示するには Worksheet オブジェクトの PrintPreview メソッドを使用するエクセルのシートを印刷するには Worksheet オブジェクトの PrintOut メソッドを使用する ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( ) ' エクセルの終了 EX.application.displayalerts = False EX.application.quit( ) ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing 使用済みの Excel.Application オブジェクトや Workbook オブジェクトや Worksheet オブジェクトは必ず閉じてメモリから開放して置く Quit メソッドはエクセルを終了する丈でメモリから削除するには Nothing を設定する End Class -5-

6 クリップボードの利用 My.Computer 名前空間の Clipboard オブジェクトを使用するとシステムのクリップボードへアクセスする事が出来る Clipboard オブジェクトはクリップボード上のテキストやグラフィックスを操作する為のオブジェクトで此れを使用してアプリケーションの中でテキストやグラフィックスの複写や切取や貼付のを実現する事が出来る Clipboard オブジェクトにデータをコピーする場合は先ず Clipboard.Clear( ) の様に Clear メソッドを実行し Clipboard オブジェクトの内容を削除して置く Clipboard オブジェクトは総ての Windows 用アプリケーションに依り共有される為他のアプリケーションに切り替えると内容が変更される可能性が有る事に注意を要する Clipboard オブジェクトは夫々れが異なる形式で有れば複数のデータを保持する事が出来る為例えば SetImage メソッドを使用して Dib 形式のビットマップを Clipboard オブジェクト上に配置し続いて Set メソッドを使用して形式のテキストを配置すると謂う事が可能で有る然して Get メソッドを使用してテキストを取得し GetImage メソッドを使用してグラフィックスを取得する事が出来る猶同じ形式の他のデータをコードやメニューコマンドを使用して Clipboard オブジェクト上に置くと古いデータは失われるクリップボードにデータを設定するには My.Computer.Clipboard オブジェクトの SetAudio SetData SetFileDropDownList SetImage Set の各メソッドを使用する亦クリップボードからデータを取得するには My.Computer.Clipboard オブジェクトの GetAudioStream GetData GetFileDropDownList GetImage Get の各メソッドを使用する猶指定した形式と一致するアイテムが Clipboard オブジェクトに存在するか何うかを調べるには My.Computer.Clipboard オブジェクトの ContainsAudio ContainsData ContainsFileDropList ContainsImage Contains の各メソッドを使用する下記に My.Computer.Clipboard オブジェクトのメソッドを示すメソッド Clear ContainsAudio ContainsData ContainsFileDropList ContainsImage Contains GetAudioStream GetData GetDataObject GetFileDropList GetImage Get SetAudio SetData SetDataObject SetFileDropList SetImage Set 説明クリップボードをクリアするクリップボードにオーディオデータが含まれて居るか何うかを示すクリップボードに指定されたカスタム形式データが含まれて居るか何うかを示すクリップボードに FileDropList が含まれるか何うかを示すクリップボードにイメージが含まれるか何うかを示すクリップボードにテキストが含まれるか何うかを示すクリップボードからオーディオストリームを取得するクリップボードからデータを取得するクリップボードからデータオブジェクトを取得するクリップボードから FileDropList を取得する配列からイメージを取得するクリップボードからテキストを取得するクリップボードにオーディオデータを書き込むデータを指定されたカスタム形式でクリップボードに書き込むクリップボードにデータオブジェクトを書き込むクリップボードに FileFropList を書き込むクリップボードにイメージを書き込むクリップボードにテキストを書き込む -6-

7 EXCEL の Worksheet オブジェクトの Cells プロパティ ( 再掲 ) Range オブジェクトを取得するプロパティ Object.Cells( 第 1 引数, 第 2 引数 ) Object で指定したワークシートの特定のセルを指定する引数 1 には 1 から始まる行 (Row) を表す数値を指定する引数 2 には 1 から始まる列 (Column) を表す数値を指定する R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで単一セルの Range オブジェクトの取得に用いられる実際にセルを操作する場合にはプロパティや Value プロパティや Formula プロパティを使用して設定や取得する種類を特定する事が多い EXCEL の Worksheet オブジェクトの Range プロパティ Range オブジェクトを取得するプロパティ Object.Range( 引数 ) Object で指定したワークシートの特定のセル範囲を指定する引数にはマクロの言語の A1 形式での範囲を指定する範囲名には範囲を表す演算子 (:) 共通部分を表す演算子 ( スペース ) 複数の範囲を表す演算子 (,) を含める事が出来る猶ドル記号 ($) は含める事は出来るが無視される亦範囲の一部にローカルに定義した名前を使用する事も出来る名前を使用する場合其の名前はマクロの言語と看做される此のプロパティはセル範囲の Range オブジェクトの取得に用いられる下記にセル範囲の指定例を示す Range( "A3" ) Range( "B2:E8" ) Range( Object.Cells( 1, 1 ), Object.Cells( 5, 4 ) ) Range( " 範囲名 " ) 単一セルを取得セル範囲を取得セル範囲を取得セル範囲を取得上記に於いて 3 番目の例では 2 個の引数を指定する構文を使用して居る此の構文では第 1 引数で左上隅のセルを第 2 引数で右下隅のセルを指定する此の構文を利用すると上記の Cells プロパティと併用して R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う事が出来る実際にセルを操作する場合にはプロパティや Value プロパティや Formula プロパティを使用して設定や取得する種類を特定する事が多い -7-

8 EXCEL の Range オブジェクトの Value プロパティ指定したセルの値を設定取得するプロパティ Object.Value = 値セルの値を設定するオブジェクトには Range オブジェクトを指定する値を取得する場合セルが空の場合は Empty 値が返される此れを調べるには IsEmpty 関数を使用する Range オブジェクトに複数のセルが含まれて居る場合は値の配列が返される此れを調べるには IsArray 関数を使用する EXCEL の Range オブジェクトの Formula プロパティ指定したセルの数式を設定取得するプロパティ Object.Formula = 計算式セルの数式を A1 形式の表示形式でコード記述時の言語で設定するオブジェクトには Range オブジェクトを指定する値を取得する場合セルに定数が入力されて居る時は Formula プロパティは定数を返しセルが空の時は Null 値を返す亦セルに数式が格納されて居る時は数式バーでの表示と同じ形式で等号を含む数式を文字列と仕て返すセルの値 (Value) 又は数式 (Formula) に日付を設定すると Excel は其のセルに日付や時刻が既に設定されて居るか何うかを調査する此の時が設定されて居ない場合は数値は標準の日付の短い形式に成る対象セル範囲が 1 次元又は 2 次元のセル範囲の場合は同じ次元を持つ Visual Basic の配列から各セルの数式を設定する事が出来る同様に各セルの数式を Visual Basic の配列に代入する事も可能で有る亦セル範囲に数式を設定すると範囲内の総てのセルに其の数式が設定される猶セルの数式を R1C1 形式の表示形式でコード記述時の言語で設定するには FormulaR1C1 プロパティを使用する下記にセルへの数式の指定例を示す Range( "A1" ).Formula = "=A4+A10" Range( "B1" ).FormulaR1C1 = "=SQRT(R1C1)" 猶上記の様に Range( "A1" ) と謂う記述でセル A1 を取得する事も出来るが Cells プロパティでは行や列の指定に変数を使用する事が出来る為上記のセル指定で Cells( 1, 1 ) や Cells( 1, 2 ) を使用する事が多い一般に Visual Basic の文字列関数を使用して A1 形式の参照文字列を変化させる事も出来るが Cells( 1, 1 ) と謂う記述の方が簡単で有り効率的なプログラミングの方法で有る -8-

9 EXCEL の Range オブジェクトの Activate メソッド単一のセルをアクティブにするメソッド Object.Activate 選択範囲の中の単一セルをアクティブにする時に使用するオブジェクトには Range オブジェクトを指定するセル範囲を選択する時は Select メソッドを使用する EXCEL の Range オブジェクトの Select メソッドオブジェクトを選択するメソッド Object.Select セルやセル範囲を選択する時に使用するオブジェクトには Range オブジェクトを指定する単一のセルをアクティブセルにするには Activate メソッドを使用する EXCEL の Worksheet オブジェクトの PrintPreview メソッド印刷プレビュー ( 印刷時のイメージ ) を表示するメソッド Object.PrintPreview オブジェクトには Worksheet オブジェクトを指定する EXCEL の Worksheet オブジェクトの PrintOut メソッドオブジェクトを印刷するメソッド Object.PrintOut オブジェクトには Worksheet オブジェクトを指定する下記の引数を指定する事も出来る ( 孰れも省略可能 ) 第 1 引数で印刷を開始するページの番号を指定する第 2 引数で印刷を終了するページの番号を指定する第 3 引数で印刷部数を指定する第 4 引数で印刷をする前に印刷プレビューを実行するか何うかを指定する (True か False) 第 5 引数でアクティブなプリンタの名前を指定する第 6 引数でファイルへ出力するか何うかを指定する (True か False) 第 7 引数で部単位で印刷するか何うかを指定する (True か False) 第 8 引数で出力するファイルの名前を指定する ( 第 6 引数が True の場合 ) -9-

10 名前付き引数の利用 Visual Basic では多くの組込関数ステートメント及びメソッドで引数を指定する時に名前付き引数を使用する事が出来る名前付き引数を使用すると構文で定められた引数の順序に関係無く任意の順序で必要な引数丈を渡す事が出来る名前付き引数に値を代入するには下記の様に引数名コロンの後に等号 (:=) と値を記述する WS.OLEObjects.Add FileName := F 指定する引数が複数有る場合は各引数指定の間をカンマで区切る指定する順序は自由で有る下記の記述例ではユーザー定義サブプロシージャ List の構文で定められた引数の順序とは逆の順序で引数を指定して居る ' ユーザー定義サブプロシージャ ( ジェネラルプロシージャ ) Private Sub List( strname As String, Optional straddress As String ) List1.AddItem strname List2.AddItem straddress ' コマンドボタンがクリックされた時の処理 ( イベントプロシージャ ) Private Sub Command1_Click( ) List straddress:="12345", strname:=" 名前 " 此のは省略可能な引数が多数有るプロシージャの場合に特に便利で有る名前付き引数のサポートを調べる特定の関数ステートメントメソッドに付いて名前付き引数の使用が可能か何うかを調べるにはコードエディタウィンドウの自動クイックヒントを使うかオブジェクトブラウザで調べるか又はランゲージリファレンスを参照する名前付き引数を使う場合下記の点に注意する必要が有る Visual Basic(VB) オブジェクトライブラリで提供されるオブジェクトのメソッドでは名前付き引数はサポートされて居ない Visual Basic for applications(vba) オブジェクトライブラリで提供される総てのキーワードでは名前付き引数がサポートされて居る示されて居る構文では名前付き引数は太字の斜体で表記されて居る名前付き引数以外の引数は標準の斜体で表記されて居る重要 : 名前付き引数を使用する場合でも必ず指定する必要の有る引数を省略する事は出来ない省略する事の出来るのは省略可能な引数丈で有る Visual Basic(VB) オブジェクトライブラリ及び Visual Basic for Applications(VBA) オブジェクトライブラリに付いてオブジェクトブラウザで参照した場合省略可能な引数は角括弧 ( [ ] ) で囲まれて表示される -10-

VB実用⑦　エクセル操作Ⅰ

VB実用⑦　エクセル操作Ⅰ VB でエクセル操作 Ⅰ VB 2005 7 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 (De Facto Standard) で有ると謂う事が出来る此処では其のエクセルを Visual Basic から操作する方法を重点的に学ぶ今回は Visual Basic でエクセルを利用する基本と成るオブジェクト生成と

VB実用⑩ エクセル操作Ⅳ

VB実用⑩　エクセル操作Ⅳ