Microsoft Excel にデータを入力して保存
Imports Microsoft.Office.Interop Imports System.Runtime.InteropServices.Marshal --- Dim oExcel As Excel.ApplicationClass Dim oBook As Excel.WorkbookClass Dim oBooks As Excel.Workbooks 'Start Excel and open the workbook. oExcel = CreateObject("Excel.Application") oExcel.Visible = True oBooks = oExcel.Workbooks oBook = oBooks.Open("c:\book1.xls") 'Run the macros. oExcel.Run ("DoKbTest") oExcel.Run ("DoKbTestWithParameter", "Hello from VB .NET Client") 'Clean-up: Close the workbook and quit Excel. oBook.Close (False) ReleaseComObject (oBook) oBook = Nothing ReleaseComObject (oBooks) oBooks = Nothing oExcel.Quit() ReleaseComObject (oExcel) oExcel = NothingVisual Basic .NET からオートメーションを使用して Office マクロを実行する方法
これを参考にして、データをExcelに入力していく。
Imports Microsoft.Office.Interop Imports System.Runtime.InteropServices.Marshal --- Dim oExcel As Excel.ApplicationClass Dim oBook As Excel.WorkbookClass Dim oBooks As Excel.Workbooks oExcel = CreateObject("Excel.Application") oExcel.Visible = True oBooks = oExcel.Workbooks oBook = oBooks.Add '新しいBookを作成する場合 'oBook = oBooks.Open("c:\book1.xls") '既存のBookを開く場合 Dim oSheet As Excel.Worksheet Dim oRange As Excel.Range oSheet = oBook.Worksheets(1) 'Sheet1を指定 '---データ入力処理--- oRange = oSheet.Cells.Range("A1") '入力するセルを指定 '初期値(行のタイトルを設定) oRange.Value = "No." oRange.Offset(, 1).Value = "Data" oRange = oRange.Offset(1) 'データを入力していく For Each temStr As String In temList oRange.Value = Split(temStr, "@")(0) oRange.Offset(, 1).Value = Split(temStr, "@")(1) oRange = oRange.Offset(1) Next '---データ入力処理--- '「この場所に 'c\book_test.xls' という名前のファイルが既にあります。置き換えますか?」アラートを回避する為 oExcel.DisplayAlerts = False 'アラートを表示させない oBook.Close(SaveChanges:=True, Filename:="c:\book_test.xls") '保存して閉じる 'oBook.SaveAs("c:\book_test.xls") : oBook.Close(SaveChanges:=False) 'SaveAsで保存してから閉じる ReleaseComObject(oBook) oBook = Nothing ReleaseComObject(oBooks) oBooks = Nothing oExcel.Quit() ReleaseComObject(oExcel) oExcel = Nothing