VB でエクセル操作 Ⅱ VB 2005 8 プログラムの概要 事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルは データベースソフトで有るアクセスと共に 業界標準 ( De Facto Standard) で有ると謂う事が出来る 今回は エクセルを Visual Basic から操作する為に 最も基本と成るセルに設定された値の取得を 重点的に学ぶ 前回学んだエクセル操作の為のオブジェクトの生成と 既存のエクセルのデータが実際に入力されて居る範囲の取得を元に 総ての入力範囲のデータを フレキシブルグリッドに表示する 猶 フレキシブルグリッドは 柔軟性の有る有用なコンポーネントで有るので 其の使用法も 併せて 学習する 今回の課題項目 オブジェクトの参照設定 ( 事前バインディング 実行時バインディング ) オブジェクト変数の宣言 (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 プロパティ ) -1-
オブジェクト プロパティ一覧 フレキシブルグリッド ボタン 1 ボタン 2 ボタン 3 ファイルオープンダイアログ ボタン 4 コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel2 Text 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen フレキシブルグリッド Name fgddata AllowUserResizing flexresizecolumns ボタン1 Name btnfiler Font MS 明朝 太字 10 ボタン2 Name btnextract Font MS 明朝 太字 10 ボタン3 Name btnclose Font MS 明朝 太字 10 ボタン4 Name btnfinish Text 終了 Font MS 明朝 太字 12 オープンファイルダイアログ Name dlgfiler FileName 空白 フレキシブルグリッドに付いては ツールボックスウィンドウの アイテムの選択 より COM コンポーネント で Microsoft FlexGrid Control, version 6.0(msflxgrd.ocx) を選択して 追加する 亦 プロジェクトメニューの 参照の追加 より COM で Microsoft Excel x.x Object Library への参照を追加する -2-
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 ' エクセルファイル名 完成後は 此等のコメントを外して Object 型にすると Excel のバージョンに依存しないプログラムにする事が出来る 製作中は 此の様に事前バインディングすると コード入力時にインテリセンスが使用出来る ' フォームが読み込まれた時の処理 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 エクセルファイル丈を表示する様に設定する ' ボタン ( エクセルのファイルを指定する ) がクリックされた時の処理 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 ャンセルがクリックされたか何う かは 戻り値で判断する -3-
' ボタン ( エクセルのデータを抽出する ) がクリックされた時の処理 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 N = S.Length - 1 For I = 1 To S.Length C += ( Asc( S.Substring( I - 1, 1 )) 64 ) * ( 26 ^ N ) N -= 1 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 此処で宣言した変数は宣言したサブプロシージャ内でしか値の参照と設定を行う事が出来ない CreateObject 関数は 事前バインディングと実行時バインディングの孰れでも使用する事が出来る 指定したファイルで Workbooks コレクションを開く シート名には 操作対象とするシートの名称を指定する Address プロパティでワークシートの使用範囲を A1 形式で取得する ( 例 :A1:AD23) 右下端のセル位置をアルファベット部分 (S) と数字部分 (T) に分割する アルファベット部分 (S) を序数に変換する 猶 64 は A の1 個前の @ の文字コードで 26 はアルファベットの文字数を表す 同一のオブジェクトに対して複数の処理を行う場合 With ステートメントを用いると オブジェクトへの参照はプロパティを割り当てる度に指定するのではなく 一度丈指定される為 効率が良く成る Chr 関数は 文字コードより文字を生成する関数で有る 此処では 0 なら A 1 なら B と謂う様に変換して 列見出しを表示して居る -4-
' 行見出しの表示.Col = 0 For I = 1 To R.Row = I.CellAlignment = flexaligncentercenter.text = I.ToString.Trim ' エクセルデータの読込と表示 For I = 1 To R.Row = I For J = 1 To C.Col = J.Text = WS.cells( I, J ).value End With 行見出しは 数値で表されるので此処では 単純に 数値を文字列に変換して 前後の空白を取り除いた物を表示して居る エクセルの各セルに設定されて居る値は Worksheet オブジェクトの Cells プロパティで取得する事が出来る 第 1 引数で行を 第 2 引数で列を 1 から始まる数値で指定する ' ボタン ( エクセルのファイルを終了する ) がクリックされた時の処理 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( ) オブジェクトの比較は Is 演算子 (IsNot 演算子 ) を用いて行う ジェネラルプロシージャを呼び出 す ( 制御を移す ) には Call ステ ートメントを用いる ' ボタン ( 終了 ) がクリックされた時の処理 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 CloseExcel( ) ' エクセルの終了 EX.application.displayalerts = False EX.application.quit() ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing End Class Worksheet オブジェクトのインスタンスが存在すれば Application Workbook Worksheet オブジェクトを正しく終了するプロシージャを呼び出す 実際には 此処迄丁寧に記述する必要は無いのだが 使用した総てのフォームをメモリから消去した上で プログラムを正しく終了すると謂う癖を付けて置く事が望ましい 使用済みの Excel.Application オブジェクトや Workbook オブジェクトや Worksheet オブジェクトは 必ず 閉じて メモリから開放して置く Quit メソッドは エクセルを終了する丈で メモリから削除するには Nothing を設定する -5-
EXCEL の UsedRange プロパティ ( 再掲 ) 使用セル範囲を返すプロパティ Object.UsedRange 指定されたワークシートで使用されて居るセル範囲 (Range オブジェクト ) を返す 値の取得而巳が可能で有る Object には オブジェクトへの参照を表すオブジェクト式を指定し 必ず指定する ( 以下同じ ) EXCEL の Address プロパティ ( 再掲 ) 参照範囲を取得するプロパティ Object.Address ( 引数 1, 引数 2, 引数 3, 引数 4, 引数 5) Range オブジェクトを対象にして 範囲参照を返す 値の取得而巳が可能で有る 引数 1 には 行部分の参照を絶対参照として返すには True( 既定値 ) を指定する 引数 2 には 列部分の参照を絶対参照として返すには True( 既定値 ) を指定する 引数 3 には A1 形式 ( 既定値 定数 xla1) か R1C1 形式 ( 定数 xlr1c1) かを指定する 引数 4 には 外部参照を返すには True ローカル参照を返すには False( 既定値 ) を指定する 引数 5 には 開始点を定義する Range オブジェクトを指定する 総ての引数は 省略する事が出来る 引数 3 には 下記の定数の孰れかを指定する 定数 値 説明 xla1 1 列を A から始まるアルファベット 行を 1 から始まる数字で表す形式 xlr1c1-4150 行を R 列を C で表し 夫々れ 1 から始まる序数で表す形式 範囲 (Range オブジェクト等 ) の取得では A1 形式では "A1:D11" の様な文字列が R1C1 形式では "R1C1:R11C4" の様な文字列が返される EXCEL の Select メソッド ( 再掲 ) オブジェクトを選択するメソッド Object.Select ( 引数 ) セル 又は セル範囲を選択するには Select メソッドを使用する 亦 単一のセルをアクティブセルにするには Activate メソッドを使用する 引数には オブジェクトの選択方法を指定し 省略する事が出来る シート丈に使用する事が出来 選択位置を指定したオブジェクト而巳を選択するには True を指定し 既に選択されて居たオブジェクトと指定したオブジェクトを選択するには False を指定する -6-
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( 第 1 引数, 第 2 引数 ) Object で指定したワークシートの特定のセルを指定する 引数 1 には 1 から始まる行 (Row) を表す数値を指定する 引数 2 には 1 から始まる列 (Column) を表す数値を指定する R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで 単一セルの Range オブジェクトの取得に用いられる 実際にセルを操作する場合には Text プロパティや Value プロパティや Formula プロパティを使用して 設定や取得する種類を特定する事が多い -7-
フレキシブルグリッドの 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 プロパティの設定値に基づいて カレントセルや現在選択されて居るセルの内容を設定する -8-
フレキシブルグリッドの 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 演算子に依るオブジェクトの比較 2 個のオブジェクト変数を比較する演算子 結果 = Object1 Is Object2 オブジェクト変数が 同じオブジェクトを参照して居るかを調べる場合や オブジェクト変数にオブジェクトへの参照が代入されて居るかを調べる場合に 利用する 変数が 両方同じオブジェクトを参照して居る場合 結果は真 (True) に成り 異なるオブジェクトを参照して居る場合は 結果は偽 (False) に成る 変数にオブジェクトへの参照が代入されて居ない場合の変数の値は Nothing で有る -9-
ファイルオープンダイアログの Filter プロパティ ダイアログボックスのファイルの種類に表示されるフィルタを設定 取得するプロパティ Object.Filter = " 表示文字列 1 フィルタ 1 表示文字列 2 フィルタ 2 " フィルタを使用すると ダイアログボックスのファイル名ボックスに表示されるファイルの種類を指定する事が出来る 表示文字列には ファイルの種類を表す文字列式を指定する フィルタには ファイル名の拡張子を指定する文字列式を指定する 例えば フィルタに "*.txt" を指定すると ダイアログボックスにはテキストファイル丈が表示される ファイルオープンダイアログの InitialDirectory プロパティ ファイルの場所と仕て最初に開かれるディレクトリを設定 取得するプロパティ Object. InitialDirectory = 値 此のプロパティには ファイルを開く ダイアログボックスで ファイルの場所と仕て最初に開かれるディレクトリ ( フォルダ ) を設定する 値には ファイルの場所と仕て最初に開かれるディレクトリを指定する文字列式を指定する 此のプロパティを設定しない場合は 現在のディレクトリが最初に開かれる ファイルオープンダイアログの ShowDialog メソッド ファイルを開くダイアログボックスを表示するメソッド Object.ShowDialog ShowDialog メソッドは ダイアログボックスを表示して実行する 戻り値には ユーザーがダイアログボックスの OK をクリックした場合は DialogResult.OK 其れ以外の場合は DialogResult.Cancel が返される ファイルが選択された場合には FileName プロパティに 其のファイルのフルパス名が格納される -10-