VB実用⑧　エクセル操作Ⅱ

プログラムの概要

事務処理に於いて、Microsoft 社の スプレッドシートソフトで有るエク セルは、データベースソフトで有る ア ク セ ス と 共 に 、 業 界 標 準 （De Facto Standard）で有ると謂う事が 出来る。 今回は、エクセルをVisual Basic か ら操作する為に、最も基本と成るセ ルに設定された値の取得を、重点的 に学ぶ。 前回学んだエクセル操作の為のオブ ジェクトの生成と、既存のエクセル のデータが実際に入力されて居る範 囲の取得を元に、総ての入力範囲の データを、フレキシブルグリッドに表示する。猶、フレキシブルグリッドは、柔軟性の有る有用なコン ポーネントで有るので、其の使用法も、併せて、学習する。

VB でエクセル操作Ⅱ

VB 2005 ⑧ □ オブジェクトの参照設定（事前バインディング、実行時バインディング） □ オブジェクト変数の宣言（Object 型、As Object）

□ オブジェクトのインスタンス生成（CreateObject 関数） □ エクセルのオブジェクト（Excel.Application、WorkBook、WorkSheet） □ ワークシートのプロパティ（UsedRange プロパティ、Cells プロパティ） □ コモンダイアログの操作（ShowOpen、FileName、Filter、InitDir、CancelError） □ フレキシブルグリッドの操作（Cols、Rows、Col、Row、Text、TextMatrix、CellAlignment） □ フォームを最前面に表示（TopMost プロパティ） 今回の課題項目 □ オブジェクトの参照設定（事前バインディング、実行時バインディング） □ オブジェクト変数の宣言（Object 型、As Object）

□ オブジェクトのインスタンス生成（CreateObject 関数）

□ エクセルのオブジェクト（Excel.Application、WorkBook、WorkSheet） □ ワークシートのプロパティ（UsedRange プロパティ、Cells プロパティ）

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel2 Text 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen フレキシブルグリッド Name fgdData AllowUserResizing flexResizeColumns ボタン１ Name btnFiler Font ＭＳ明朝、太字、１０ ボタン２ Name btnExtract Font ＭＳ明朝、太字、１０ ボタン３ Name btnClose Font ＭＳ明朝、太字、１０ ボタン４ Name btnFinish Text 終了 Font ＭＳ明朝、太字、１２ オープンファイルダイアログ Name dlgFiler FileName 空白 ボタン４ ボタン1 フレキシブルグリッド ファイルオープンダイアログ ボタン２ ボタン３ フレキシブルグリッドに付いては、ツールボックスウィンドウの『アイテムの選択』より『COM コンポーネント』でMicrosoft FlexGrid Control, version 6.0（msflxgrd.ocx）を選択して、追加す る。

亦、プロジェクトメニューの『参照の追加』より『COM』で Microsoft Excel x.x Object Library への参照を追加する。

■ プログラムリスト ■ Public Class excel2

' フォームレベルでグローバルな定数の宣言（エクセル定数） Private Const xlA1 As Integer = 1

' フォームレベルでグローバルな定数の宣言（フレキシブルグリッド定数） Private Const flexAlignCenterCenter As Integer = 4

' フォームレベルでグローバルな変数の宣言 ' 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 excel2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load

' エクセルファイルの格納場所の設定（起動パス）

SD = Application.StartupPath : If Not SD.EndsWith("¥") Then SD &= "¥"

' ウィンドウを常に手前に表示 Me.TopMost = True

' ボタンのキャプションの設定

btnFiler.Text = "エクセルのファイルを" & vbCrLf & "指定する" btnExtract.Text = "エクセルのデータを" & vbCrLf & "抽出する" btnClose.Text = "エクセルのファイルを" & vbCrLf & "終了する" ' ファイルを開くダイアログの設定 dlgFiler.Filter = "エクセルファイル(*.xls)|*.xls|総てのファイル(*.*)|*.*" dlgFiler.InitialDirectory = SD End Sub ' ボタン（エクセルのファイルを指定する）がクリックされた時の処理

Private Sub btnFiler_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnFiler.Click

If dlgFiler.ShowDialog = Windows.Forms.DialogResult.OK Then ' エクセルのファイル名の設定

FN = dlgFiler.FileName

If Not FN.Substring( FN.LastIndexOf(".") + 1 ) = "xls" Then FN = "" End If End If End Sub 完成後は、此等のコメントを外し て、Object 型にすると、Excel の バージョンに依存しないプログラ ムにする事が出来る。 製作中は、此の様に事前バインデ ィングすると、コード入力時にイ ンテリセンスが使用出来る。 ボタンのキャプションに改行を入 れ度い時は、此の様に記述すれば 良い（デザイン時にも出来ない事 はないが、面倒で有る）。 ファイルオープンダイアログでキ ャンセルがクリックされたか何う かは、戻り値で判断する。 エクセルファイル丈を表示する様 に設定する。

' ボタン（エクセルのデータを抽出する）がクリックされた時の処理

Private Sub btnExtract_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnExtract.Click Dim S As String = "" Dim T As String = "" Dim D As String Dim I As Integer Dim J As Integer Dim N As Integer Dim R As Integer ' 最終行 Dim C As Integer ' 最終列 ' エクセルファイルが指定されて居ない時は強制脱出 If FN = "" Then Exit Sub

' エクセルオブジェクトの新しいインスタンスの生成 EX = CreateObject( "Excel.Application" ) ' ファイルを指定してワークブックのオープン WB = EX.Workbooks.Open( FN ) ' シート名を指定してオブジェクトの定義 WS = WB.Worksheets( "Sheet1" ) ' エクセルの表示 EX.Visible = True ' シートの使用領域の取得 WS.UsedRange.Select( )

S = WS.UsedRange.Address( False, False, xlA1 ) D = S.Substring( S.IndexOf(":") + 1 )

For I = 1 To D.Length

If D.Substring( I - 1, 1 ) < "A" Then S = D.Substring( 0, I – 1 ) T = D.Substring( I – 1 ) Exit For End If Next N = S.Length - 1 For I = 1 To S.Length C += ( Asc( S.Substring( I - 1, 1 )) – 64 ) * ( 26 ^ N ) N -= 1 Next R = Val( T ) ' フレキシブルグリッドに表示 With fgdData .Cols = C + 1 .Rows = R + 1 ' 列見出しの表示 .Row = 0 For I = 0 To ( C - 1 ) .Col = I + 1 S = "" : N = I Do S = Chr(( N Mod 26 ) + 65 ) & S N = N ¥ 26 - 1 Loop Until N < 0 .CellAlignment = flexAlignCenterCenter .Text = S Next 此処で宣言した変数は宣言したサ ブプロシージャ内でしか値の参照 と設定を行う事が出来ない。 CreateObject 関数は、事前バイン ディングと実行時バインディング の孰れでも使用する事が出来る。 指定したファイルで、Workbooks コレクションを開く。 シート名には、操作対象とするシ ートの名称を指定する。 Address プロパティでワークシー トの使用範囲をA1 形式で取得す る（例：A1:AD23）。 右下端のセル位置をアルファベッ ト部分（S）と数字部分（T）に分 割する。 アルファベット部分（S）を序数 に変換する。猶、64 は A の１個 前の@の文字コードで、26 はアル ファベットの文字数を表す。 同一のオブジェクトに対して複数 の処理を行う場合、With ステート メントを用いると、オブジェクト への参照はプロパティを割り当て る度に指定するのではなく、一度 丈指定される為、効率が良く成る。 Chr 関数は、文字コードより文字 を生成する関数で有る。此処では 0 なら A、1 なら B…と謂う様に変 換して、列見出しを表示して居る。

' 行見出しの表示 .Col = 0 For I = 1 To R .Row = I .CellAlignment = flexAlignCenterCenter .Text = I.ToString.Trim Next ' エクセルデータの読込と表示 For I = 1 To R .Row = I For J = 1 To C .Col = J

.Text = WS.cells( I, J ).value Next Next End With End Sub ' ボタン（エクセルのファイルを終了する）がクリックされた時の処理

Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnClose.Click

If WS Is Nothing Then Exit Sub ' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) End Sub ' ボタン（終了）がクリックされた時の処理

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 End Sub ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( )

' エクセルの終了 EX.application.displayalerts = False EX.application.quit() ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing End Sub End Class 実際には、此処迄丁寧に記述する 必要は無いのだが、使用した総て のフォームをメモリから消去した 上で、プログラムを正しく終了す ると謂う癖を付けて置く事が望ま しい。 使用済みの Excel.Application オ ブジェクトやWorkbook オブジェ クトや Worksheet オブジェクト は、必ず、閉じて、メモリから開 放して置く。 Quit メソッドは、エクセルを終了 する丈で、メモリから削除するに はNothing を設定する。 Worksheet オブジェクトのインス タンスが存在すればApplication、 Workbook、Worksheet オブジェ クトを正しく終了するプロシージ ャを呼び出す。 行見出しは、数値で表されるので 此処では、単純に、数値を文字列 に変換して、前後の空白を取り除 いた物を表示して居る。 エクセルの各セルに設定されて居 る値は、Worksheet オブジェクト のCells プロパティで取得する事 が出来る。第１引数で行を、第２ 引数で列を、1 から始まる数値で 指定する。 オブジェクトの比較は、Is 演算子 （IsNot 演算子）を用いて行う。 ジェネラルプロシージャを呼び出 す（制御を移す）には、Call ステ ートメントを用いる。

EXCEL の UsedRange プロパティ （再掲） 使用セル範囲を返すプロパティ Object.UsedRange 指定されたワークシートで使用されて居るセル範囲（Range オブジェクト）を返す。値の取 得而巳が可能で有る。 Object には、オブジェクトへの参照を表すオブジェクト式を指定し、必ず指定する（以下同じ）。 EXCEL の Address プロパティ （再掲） 参照範囲を取得するプロパティ Object.Address (引数１,引数２,引数３,引数４,引数５) Range オブジェクトを対象にして、範囲参照を返す。値の取得而巳が可能で有る。 引数１には、行部分の参照を絶対参照として返すにはTrue（既定値）を指定する。 引数２には、列部分の参照を絶対参照として返すにはTrue（既定値）を指定する。 引数３には、A1 形式（既定値、定数 xlA1）か R1C1 形式（定数 xlR1C1）かを指定する。 引数４には、外部参照を返すにはTrue、ローカル参照を返すには False（既定値）を指定する。 引数５には、開始点を定義するRange オブジェクトを指定する。 総ての引数は、省略する事が出来る。 引数３には、下記の定数の孰れかを指定する。 定数 値 説明 xlA1 1 列をA から始まるアルファベット、行を 1 から始まる数字で表す形式 xlR1C1 -4150 行をR、列を C で表し、夫々れ 1 から始まる序数で表す形式

範囲（Range オブジェクト等）の取得では、A1 形式では "A1:D11" の様な文字列が、R1C1 形式では "R1C1:R11C4" の様な文字列が返される。 EXCEL の Select メソッド （再掲） オブジェクトを選択するメソッド Object.Select (引数) セル、又は、セル範囲を選択するには、Select メソッドを使用する。亦、単一のセルをアク ティブセルにするには、Activate メソッドを使用する。 引数には、オブジェクトの選択方法を指定し、省略する事が出来る。シート丈に使用する事が出来、選 択位置を指定したオブジェクト而巳を選択するにはTrue を指定し、既に選択されて居たオブジェクト と指定したオブジェクトを選択するにはFalse を指定する。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の DisplayAlerts プロパティ （再掲） マクロの実行中に特定の警告やメッセージの表示を制御するプロパティ Object.DisplayAlerts = 値 値がTrue（既定値）の場合、マクロの実行中に特定の警告やメッセージを表示する。 値には、True か False のブール値を指定する。 マクロを実行して居る間に、ユーザーに入力を促すメッセージや警告メッセージを表示させない場合は 此のプロパティをFalse を設定する。猶、此のプロパティは、False に設定した儘、マクロの実行を終 了しても、自動的にTrue には戻らない。

Visual Basic から操作する場合、Excel シートに加えた変更を保存せずに終了する時には、此のプロパ ティを False に設定して、『変更を保存しますか？』と謂うダイアログボックスを表示しない様にする 事が多い。 EXCEL の Quit メソッド （再掲） Excel を終了するメソッド Object.Quit Object で指定した現在開いて居る Excel を終了する。 Quit メソッドを使用すると、開いて居るブックを未だ保存して居ない場合は、変更を保存するか何うか を確認するダイアログボックスが表示される。メッセージを表示させない場合は、Quit メソッドを実行 する前に、総てのブックを保存するか、又は、DisplayAlerts プロパティを False に設定する。 DisplayAlerts プロパティに False が設定されて居ると、確認メッセージは表示されず、変更したブッ クを保存しないで、Excel を終了する。 EXCEL の Cells プロパティ Range オブジェクトを取得するプロパティ Object.Cells( 第１引数, 第２引数 ) Object で指定したワークシートの特定のセルを指定する。 引数１には、１から始まる行（Row）を表す数値を指定する。 引数２には、１から始まる列（Column）を表す数値を指定する。 R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで、単一 セルのRange オブジェクトの取得に用いられる。

実際にセルを操作する場合には、Text プロパティや Value プロパティや Formula プロパティを使用し て、設定や取得する種類を特定する事が多い。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

フレキシブルグリッドのCols プロパティ、Rows プロパティ Cols ：フレキシブルグリッドコントロール内の列の総数を設定・取得するプロパティ Rows ：フレキシブルグリッドコントロール内の行の総数を設定・取得するプロパティ Object.Cols = 値 Object.Rows = 値 此等のプロパティを使用すると、実行時にフレキシブルグリッドコントロールを動的に拡大 又は、縮小する事が出来る。 値には、列、又は、行の総数を指定する長整数型の値を指定する。 行、及び、列の最小値は0 で、最大値は、コンピュータ上で使用可能なメモリ量に依り制限される。 フレキシブルグリッドのCol プロパティ、Row プロパティ フレキシブルグリッドコントロールのアクティブセルの座標を設定・取得するプロパティ Object.Col = 値 Object.Row = 値 此等のプロパティは、フレキシブルグリッドコントロール内のセルを指定したり、何の行や 列にカレントセルが有るかを判定する為に使用する。 値には、アクティブセルの位置を指定する長整数型の値を指定する。 此等のプロパティは、デザイン時には、使用する事が出来ない。 列と行には0 から番号が付けられる。行は、上から下に向けて番号が付けられ、列は左端から右端に向 けて番号が付けられる。 此等のプロパティを設定すると、RowSel プロパティと ColSel プロパティが自動的に再設定され、其の 再設定されたセルがカレントセルに成る。其の為、範囲を指定するには、Row プロパティと Col プロパ ティを最初に設定し、次にRowSel プロパティと ColSel プロパティを設定する必要が有る。 Col プロパティと Row プロパティの設定値で定義されたカレントセルの値は、其のセルに格納されたテ キストで有る。Row プロパティと Col プロパティの値を変更しないでセルの値を変更するには TextMatrix プロパティを使用する。 フレキシブルグリッドのText プロパティ 単一セルや特定範囲内のセルのテキスト内容を設定・取得するプロパティ Object.Text = 文字列 設定する場合は、Text プロパティは、FillStyle プロパティの設定値に基づいて、カレント セルや現在選択されて居るセルの内容を設定する。 機 能 書 式 解 説 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式

フレキシブルグリッドのset_TextMatrix メソッド 任意のセルのテキストを設定するメソッド Object.TextMatrix( 行番号, 列番号, 文字列 ) 此のメソッドを使用すると、Row プロパティと Col プロパティを変更する事無く、セルの内 容を設定する事が出来る。 行番号と列番号は、読み取りや書き込みを行うセルを指定する数式（整数）を指定する。 フレキシブルグリッドのCellAlignment プロパティ セル内の水平配置や垂直配置を決定する値を設定・取得するプロパティ Object.CellAlignment = 値 カレントセル内でのデータの水平配置や垂直配置を決定する値を設定・取得する。 値には、テキストのセル内での配置方法を示す整数、又は、定数を指定する。値の設定値は、下記の通 りで有る。 定数 値 内容 flexAlignLeftTop 0 セルの内容は左側の上に配置される。 flexAlignLeftCenter 1 セルの内容は左側の中央に配置される（文字列の既定値）。 flexAlignLeftBottom 2 セルの内容は左側の下に配置される。 flexAlignCenterTop 3 セルの内容は中央の上に配置される。 flexAlignCenterCenter 4 セルの内容は中央の中央に配置される。 flexAlignCenterBottom 5 セルの内容は中央の下に配置される。 flexAlignRightTop 6 セルの内容は右側の上に配置される。 flexAlignRightCenter 7 セルの内容は右側の中央に配置される（数値の既定値）。 flexAlignRightBottom 8 セルの内容は右側の下に配置される。。 flexAlignGeneral 9 文字列は左側の中央に配置され、数値は右側の中央に配置される。。 此等のプロパティは、デザイン時には、使用する事が出来ない。 Is 演算子に依るオブジェクトの比較 ２個のオブジェクト変数を比較する演算子 結果 = Object1 Is Object2 オブジェクト変数が、同じオブジェクトを参照して居るかを調べる場合や、オブジェクト変 数にオブジェクトへの参照が代入されて居るかを調べる場合に、利用する。 変数が、両方同じオブジェクトを参照して居る場合、結果は真（True）に成り、異なるオブジェクトを 参照して居る場合は、結果は偽（False）に成る。変数にオブジェクトへの参照が代入されて居ない場 合の変数の値はNothing で有る。 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式

ファイルオープンダイアログのFilter プロパティ ダイアログボックスのファイルの種類に表示されるフィルタを設定・取得するプロパティ Object.Filter = " 表示文字列１|フィルタ１|表示文字列２|フィルタ２… " フィルタを使用すると、ダイアログボックスのファイル名ボックスに表示されるファイルの 種類を指定する事が出来る。 表示文字列には、ファイルの種類を表す文字列式を指定する。 フィルタには、ファイル名の拡張子を指定する文字列式を指定する。 例えば、フィルタに "*.txt" を指定すると、ダイアログボックスにはテキストファイル丈が表示される。 ファイルオープンダイアログのInitialDirectory プロパティ ファイルの場所と仕て最初に開かれるディレクトリを設定・取得するプロパティ Object. InitialDirectory = 値 此のプロパティには、『ファイルを開く』ダイアログボックスで、ファイルの場所と仕て最 初に開かれるディレクトリ（フォルダ）を設定する。 値には、ファイルの場所と仕て最初に開かれるディレクトリを指定する文字列式を指定する。 此のプロパティを設定しない場合は、現在のディレクトリが最初に開かれる。 ファイルオープンダイアログのShowDialog メソッド ファイルを開くダイアログボックスを表示するメソッド Object.ShowDialog ShowDialog メソッドは、ダイアログボックスを表示して実行する。 戻り値には、ユーザーがダイアログボックスのOK をクリックした場合は DialogResult.OK、其れ以外 の場合はDialogResult.Cancel が返される。 ファイルが選択された場合には、FileName プロパティに、其のファイルのフルパス名が格納される。 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式 機 能 書 式 解 説 書 式