vb.net から Excel を操作 2

前回の続きです。
excelを開いて、あるワークシートのコピーを最前列に追加、
適当なセルから順に配列の値を流し込んで保存して閉じる。

        Dim objExcel As Object = Nothing
        Dim objExApp As Object = Nothing
        Dim objWorkbook As Object = Nothing
        Dim objWorkbooks As Object = Nothing

        Dim fpath As String
        fpath = "ファイル.xlsのパス"

        Dim objSheets As Object = Nothing
        Dim objSheet1 As Object = Nothing
        Dim objSheet2 As Object = Nothing
        Dim objSheet3 As Object = Nothing
        Dim objCells As Object = Nothing
        Dim i As Integer
        Dim j As Integer
        Try
            objExcel = CreateObject("Excel.Application")
            objExApp = objExcel.Application
            objExApp.Visible = True
            'objExApp.EnableEvents = False
            objWorkbooks = objExApp.Workbooks
            objWorkbook = objWorkbooks.Open(fpath)
            objExcel.DisplayAlerts = False
            'System.Threading.Thread.Sleep(5000)
            objSheets = objExApp.Sheets
            objSheet1 = objSheets(1)
            objSheet1.delete '前回の入力は削除
            Marshal.ReleaseComObject(objSheet1)
            objSheet1 = objSheets(1)
            objSheet2 = objSheets("シート名")
            objSheet2.Copy(Before:=objSheet1)
            Marshal.ReleaseComObject(objSheet1)
            Marshal.ReleaseComObject(objSheet1)　'なぜか2回解放が必要
            Marshal.ReleaseComObject(objSheet2)　
            objSheet3 = objSheets("シート名 (2)")
            objCells = objSheet3.Cells

            Call Ary関数()　'別にpublic function Ary関数()で作成、二次元配列　

            For j = 0 To UBound(Ary関数, 1)
                For i = 1 To UBound(Ary関数, 2)
                    objCells(j + 1, i + 2) = Ary関数(j, i)　'この辺は入れたい場所でいろいろ変化
                Next
            Next

            Marshal.ReleaseComObject(objCells)
            Marshal.ReleaseComObject(objSheet3)
            Marshal.ReleaseComObject(objSheets)

            objWorkbook.SaveAs(fpath)

            Marshal.ReleaseComObject(objWorkbook)
            Marshal.ReleaseComObject(objWorkbooks)
            objExApp.Quit()
            objExcel.DisplayAlerts = True
            Marshal.ReleaseComObject(objExApp)
            Marshal.ReleaseComObject(objExcel)

            objCells = Nothing
            objSheets = Nothing
            objSheet1 = Nothing
            objSheet2 = Nothing
            objSheet3 = Nothing
            objWorkbook = Nothing
            objWorkbooks = Nothing
            objExApp = Nothing
            objExcel = Nothing

        Catch ex As Exception
            Throw


        End Try

Dim objExcel As Object = Nothing

Dim objExApp As Object = Nothing

Dim objWorkbook As Object = Nothing

Dim objWorkbooks As Object = Nothing

Dim fpath As String

fpath = "ファイル.xlsのパス"

Dim objSheets As Object = Nothing

Dim objSheet1 As Object = Nothing

Dim objSheet2 As Object = Nothing

Dim objSheet3 As Object = Nothing

Dim objCells As Object = Nothing

Dim i As Integer

Dim j As Integer

Try

objExcel = CreateObject("Excel.Application")

objExApp = objExcel.Application

objExApp.Visible = True

'objExApp.EnableEvents = False

objWorkbooks = objExApp.Workbooks

objWorkbook = objWorkbooks.Open(fpath)

objExcel.DisplayAlerts = False

'System.Threading.Thread.Sleep(5000)

objSheets = objExApp.Sheets

objSheet1 = objSheets(1)

objSheet1.delete '前回の入力は削除

Marshal.ReleaseComObject(objSheet1)

objSheet1 = objSheets(1)

objSheet2 = objSheets("シート名")

objSheet2.Copy(Before:=objSheet1)

Marshal.ReleaseComObject(objSheet1)

Marshal.ReleaseComObject(objSheet1)　'なぜか2回解放が必要

Marshal.ReleaseComObject(objSheet2)　

objSheet3 = objSheets("シート名 (2)")

objCells = objSheet3.Cells

Call Ary関数()　'別にpublic function Ary関数()で作成、二次元配列　

For j = 0 To UBound(Ary関数, 1)

For i = 1 To UBound(Ary関数, 2)

objCells(j + 1, i + 2) = Ary関数(j, i)　'この辺は入れたい場所でいろいろ変化

Marshal.ReleaseComObject(objCells)

Marshal.ReleaseComObject(objSheet3)

Marshal.ReleaseComObject(objSheets)

objWorkbook.SaveAs(fpath)

Marshal.ReleaseComObject(objWorkbook)

Marshal.ReleaseComObject(objWorkbooks)

objExApp.Quit()

objExcel.DisplayAlerts = True

Marshal.ReleaseComObject(objExApp)

Marshal.ReleaseComObject(objExcel)

objCells = Nothing

objSheets = Nothing

objSheet1 = Nothing

objSheet2 = Nothing

objSheet3 = Nothing

objWorkbook = Nothing

objWorkbooks = Nothing

objExApp = Nothing

objExcel = Nothing

Catch ex As Exception

Throw

End Try

難しい

月	火	水	木	金	土	日
« 12月				2月 »
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル