Form1クラス ExcelExテスト用起動処理 ---------------------------------------------------------------------------------------------------- Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click ExEx1 = New ExcelEx() ExEx1.WorkBook_Open(見積書 & "【テスト】見積り一覧.xlsx") ExEx1.xlsApplication.WindowState = Excel.XlWindowState.xlMaximized ExEx1.WorkSheet_Select("見積一覧") ExEx1.WorkBook_Close() ExEx1 = Nothing End Sub ----------------------------------------------------------------------------------------------------
ExcelExクラス（参照しているプロパティ、メソッドを全記載） ---------------------------------------------------------------------------------------------------- Option Explicit On Option Strict On
Imports Microsoft.Office.Interop
Public Class ExcelEx
'Excel のアプリケーション参照用オブジェクト Private _xlsApplication As Excel.Application = Nothing Friend ReadOnly Property xlsApplication As Excel.Application Get Return _xlsApplication End Get End Property
'Excel の Workbooks 参照用オブジェクト (Workbook の Collection) Private _xlsWorkBooks As Excel.Workbooks = Nothing Friend ReadOnly Property xlsWorkBooks As Excel.Workbooks Get Return _xlsWorkBooks End Get End Property
'Excel の Workbooks 内の1個の Workbook 参照用オブジェクト Private _xlsWorkBook As Excel.Workbook = Nothing Friend ReadOnly Property xlsWorkBook As Excel.Workbook Get Return _xlsWorkBook End Get End Property
'Excel の Workbook 内の Worksheets 参照用オブジェクト (Worksheet の Collection) Private _xlsSheets As Excel.Sheets = Nothing Friend ReadOnly Property xlsSheets As Excel.Sheets Get Return _xlsSheets End Get End Property
'Excel の Sheets 内の1個の Worksheet 参照用オブジェクト Private _xlsWorkSheet As Excel.Worksheet = Nothing Friend ReadOnly Property xlsWorkSheet As Excel.Worksheet Get Return _xlsWorkSheet End Get End Property
'Excel の Sheet 内の1個の Range 参照用オブジェクト Friend Property xlsRange As Excel.Range
Private _ExcelVisible As Boolean = True Friend Property ExcelVisible As Boolean Set(value As Boolean) _ExcelVisible = value If Not _xlsApplication Is Nothing Then _xlsApplication.Visible = _ExcelVisible End Set Get Return _ExcelVisible End Get End Property
Private _SheetVisible As Boolean = True Friend Property SheetVisible As Boolean Set(value As Boolean) _SheetVisible = value If Not _xlsApplication Is Nothing And _SheetVisible Then _xlsWorkSheet.Select() End Set Get Return _SheetVisible End Get End Property
Friend Property CellsVisible As Boolean = True
Friend Property WorkBookName As String = Nothing
Friend Sub New() 'Excel アプリケーション起動 _xlsApplication = New Excel.Application '←(a 'Excel の Workbooks 取得 _xlsWorkBooks = _xlsApplication.Workbooks 'Excel非表示 _xlsApplication.Visible = False _xlsApplication.DisplayAlerts = False End Sub
Friend Sub WorkBook_Open(strFileName As String) '既存 Excel ファイルを開く _xlsWorkBook = xlsWorkBooks.Open(strFileName) 'Excel の Worksheets 取得 xlsSheets = xlsWorkBook.Worksheets 'Excel の Worksheet 取得 _xlsWorkSheet = CType(xlsSheets.Item(1), Excel.Worksheet) _xlsWorkSheet.Select() _xlsApplication.Visible = ExcelVisible '←(b End Sub
Friend Sub WorkSheet_Select(strSheetName As String) Dim check As Boolean = False For Each sh In xlsSheets If CType(sh, Excel.Worksheet).Name = strSheetName Then check = True Exit For End If Next If Not check Then _xlsWorkSheet = CType(xlsSheets.Add(), Excel.Worksheet) xlsWorkSheet.Name = strSheetName Else _xlsWorkSheet = CType(xlsSheets(strSheetName), Excel.Worksheet) End If If SheetVisible Then xlsWorkSheet.Select() End Sub
'COM オブジェクトへの参照を解放 ''' <summary> ''' COMオブジェクトの参照カウントをデクリメントします。 ''' </summary> ''' <typeparam name="T">(省略可能)</typeparam> ''' <param name="objCom"> ''' COM オブジェクト持った変数を指定します。 ''' このメソッドの呼出し後、この引数の内容は Nothing となります。 ''' </param> ''' <param name="force"> ''' すべての参照を強制解放する場合はTrue、現在の参照のみを減ずる場合はFalse。 ''' </param> Friend Sub MRComObject(Of T As Class)(ByRef objCom As T, Optional ByVal force As Boolean = False) If objCom Is Nothing Then Return Try If System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then If force Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom) Else System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom) End If End If Finally objCom = Nothing End Try End Sub ----------------------------------------------------------------------------------------------------
