Public Class Form1
Private app As Excel.Application = Nothing
Private book As Excel.Workbook = Nothing
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
LoadExcel()
OpenBook("C:\TMP\Book1.xls")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Quit()
End Sub
Private Sub LoadExcel()
' Excel が存在しない場合のみ作成
If app Is Nothing Then
app = New Excel.Application
End If
End Sub
Private Sub OpenBook(ByVal path As String)
If app Is Nothing Then
' Excel が存在しなければなにもしない
Return
End If
Dim books As Object
books = app.Workbooks
If Not book Is Nothing Then
book.Saved = True
book.Close()
' 初期化
System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
book = Nothing
End If
book = books.Open(path)
System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
app.Visible = True
End Sub
Private Sub Quit()
If app Is Nothing Then
' Excel が存在しなければなにもしない
Return
End If
' ブックが OPEN されている場合は、保存した事にする
If Not book Is Nothing Then
book.Saved = True
End If
' Excel を終了
app.Quit()
' 初期化
System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
book = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End Sub
Private Sub SetCell(ByVal SheetName As String, ByVal x As Integer, ByVal y As Integer, ByVal str As String)
If app Is Nothing Then
' Excel が存在しなければなにもしない
Return
End If
' ブックが OPEN されていない場合は、なにもしない
If book Is Nothing Then
Return
End If
Dim A As Object, B As Object, C As Object
A = book.Sheets
Try
B = A(SheetName)
Catch ex As Exception
System.Runtime.InteropServices.Marshal.ReleaseComObject(A)
MessageBox.Show("シート名が存在しません")
Return
End Try
C = B.Cells
C(y, x) = str
System.Runtime.InteropServices.Marshal.ReleaseComObject(C)
System.Runtime.InteropServices.Marshal.ReleaseComObject(B)
System.Runtime.InteropServices.Marshal.ReleaseComObject(A)
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
SetCell("Sheet15", 2, 5, "ああああ")
End Sub
' ***********************************************************' VBScript のコードをメモリに乗せて実行する' ***********************************************************
Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
Dim vbs As New MSScriptControl.ScriptControl()
Dim code As String
code = "Function VbsTest(target,x,y)" + ControlChars.CrLf
code += "MsgBox(target&""|""&x&""|""&y)" + ControlChars.CrLf
code += "Set ExcelApp = CreateObject(""Excel.Application"")" + ControlChars.CrLf
code += "ExcelApp.DisplayAlerts = False" + ControlChars.CrLf
code += "ExcelApp.Visible = true" + ControlChars.CrLf
code += "Set ExcelBook = ExcelApp.Workbooks.Open(target)" + ControlChars.CrLf
code += "ExcelBook.Sheets(""Sheet1"").Cells(y, x) = ""あいうえお""" + ControlChars.CrLf
code += "ExcelBook.Close()" + ControlChars.CrLf
code += "Set ExcelBook = Nothing" + ControlChars.CrLf
code += "ExcelApp.Quit()" + ControlChars.CrLf
code += "Set ExcelApp = Nothing" + ControlChars.CrLf
code += "End Function" + ControlChars.CrLf
vbs.Language = "VBScript"
vbs.AddCode(code)
Dim A As String = "C:\TMP\Book1.xls"
Dim B As Integer = 2
Dim C As Integer = 5
Dim param As Object() = {A, B, C}
vbs.Run("VbsTest", param)
End Sub
End Class
以下、Excel2003 テスト
拡張子:
Public Class Form1
Dim obj As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim books As Microsoft.Office.Interop.Excel.Workbooks
Dim book As Microsoft.Office.Interop.Excel.Workbook
' **************************************************
' 一つ一つ取得( 後でリリース用 )
' グローバルで使用するオブジェクト
' **************************************************
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
books = obj.Workbooks
book = books.Open("C:\user\book1.xls")
obj.Visible = True
End Sub
' **************************************************
' セルにデータセット
' **************************************************
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
book.Saved = True
obj.Quit()
'ひとつ一つリリース
'System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
'System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
'System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
' 一括キャンセル
obj = Nothing
GC.Collect()
End Sub
' **************************************************
' セルにデータセット
' ブロック内でオブジェクトを使用後リリースする
' **************************************************
Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
' book.Sheets("Sheet1").Cells(10, 10) = "文字列"
Dim A As Object, B As Object, C As Object
A = book.Sheets
B = A("Sheet1")
C = B.Cells
C(10, 10) = "文字列"
System.Runtime.InteropServices.Marshal.ReleaseComObject(C)
System.Runtime.InteropServices.Marshal.ReleaseComObject(B)
System.Runtime.InteropServices.Marshal.ReleaseComObject(A)
End Sub
End Class
拡張子:
Excel をきちんと終了させる為に少し調査しましたが、
Microsoft の陰謀でそれぞれの環境で動作違うのでしょうか、
ヨソで「できないと記述されてた」事ができたりしてます。
ま、よくある事。