VB.NET Excelの操作　その1

VB.NETでExcelを操作するその1
参照設定でMicrosoft Excel xx.x object libraryを参照設定
注意　 Microsoft office xx.x object libraryではない
ちなみに　excel2016　2019は16.0
使い方はMS本家の「オブジェクトモデル (Excel)」
https://docs.microsoft.com/ja-jp/office/vba/api/overview/excel/object-model

簡易な方法

Imports Microsoft.Office.Interop
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ex As New Excel.Application
        Dim sh As Excel.Worksheet
        Dim wb As Excel.Workbook
        'ファイルオープン
        wb = ex.Workbooks.Open(“C:\work\test1.xlsx”)
        sh = wb.Sheets(“一覧表”)
        MsgBox(sh.Range(“B2”).Value)
        ex.Quit()


    End Sub
End Class

本格的な方法

Imports Microsoft.Office.Interop
Public Class Form1

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim xlApp As Excel.Application = Nothing

        Dim xlBook As Excel.Workbook = Nothing
        Dim xlSheet As Excel.Worksheet = Nothing

        Dim xlBookWrite As Excel.Workbook = Nothing
        Dim xlSheetWrite As Excel.Worksheet = Nothing

        Try
            '起動していればそのExcelを使用します
            xlApp = GetObject(, "Excel.Application")

        Catch ex As Exception

            'Excel が起動していないなら新規にインスタンスを生成します
            xlApp = CreateObject("Excel.Application")

        End Try

        xlApp.Visible = True
        xlBook = xlApp.Workbooks.Open(“C:\work\test1.xlsx”)
        xlSheet = xlBook.Sheets(“一覧表”)
        'MsgBox(xlSheet.Range(“B2”).Value)



        xlSheet.Cells(1, 1).Value = "APP_Name"

        xlBook.Save()
        xlSheet.Close(False)


        MRComObject(xlSheet)
        MRComObject(xlBook)

        xlApp.Quit()
        MRComObject(xlApp)

        xlSheet = Nothing
        xlBook = Nothing

        xlApp = Nothing
    End Sub
    Private Sub MRComObject(ByVal objCom As Object) '

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)
        Catch ex As Exception
            objCom = Nothing
        Finally
            objCom = Nothing
        End Try

    End Sub
End Class