【vb.net】ひな型エクセルマクロファイル（.xlsm）を元に帳票出力する簡易サンプル

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Public Class Form1

    Public Const TEMPLATE = "D:\TenmleteFiles\Template.xlsm"
    Public Const OUTFOLDER = "D:\OutPut"

    ''' <summary>
    ''' 画面読み込み時の処理を行います。
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        txtOutFolder.Text = OUTFOLDER
    End Sub

    ''' <summary>
    ''' ［出力］ボタンクリック時の処理を行います。
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim strOutFileName As String = ""

        Dim xlsApplication As Excel.Application = Nothing
        Dim xlsWorkbooks As Excel.Workbooks = Nothing
        Dim xlsWorkbook As Excel.Workbook = Nothing
        Dim xlsSheets As Excel.Sheets = Nothing

        Dim xlsWorkSheet1 As Excel.Worksheet = Nothing
        Dim xlsRange1 As Excel.Range = Nothing
        Dim xlsWorkSheet2 As Excel.Worksheet = Nothing
        Dim xlsRange2 As Excel.Range = Nothing

        Try
            strOutFileName = txtOutFolder.Text & "\" & DateTime.Now.ToString("yyyyMMdd-HHmmss") & ".xlsm"

            'Excelアプリケーションを起動
            xlsApplication = New Excel.Application
            xlsApplication.Visible = False
            xlsApplication.DisplayAlerts = False

            'Workbooksオブジェクトを生成
            xlsWorkbooks = xlsApplication.Workbooks

            'ブック１への書き込み
            xlsWorkbook = xlsWorkbooks.Open(TEMPLATE)
            xlsSheets = xlsWorkbook.Sheets

            'ブック１のシート１への書き込み
            xlsWorkSheet1 = xlsSheets.Item("テンプレート１")
            xlsWorkSheet1.Name = "出力１"
            xlsRange1 = xlsWorkSheet1.Cells()
            xlsRange1(1, 2) = "TEST PJ １"
            xlsRange1(4, 1) = 1
            xlsRange1(4, 2) = "１１１１"
            xlsRange1(5, 1) = 1234
            xlsRange1(6, 1) = -1234
            xlsRange1(7, 2) = -12
            xlsRange1(7, 3) = -24
            xlsRange1(7, 4) = 48

            'ブック１のシート２への書き込み
            xlsWorkSheet2 = xlsSheets.Item("テンプレート２")
            xlsWorkSheet2.Name = "出力２"
            xlsRange2 = xlsWorkSheet2.Cells()
            xlsRange2(1, 2) = "TEST PJ ２"
            xlsRange2(4, 1) = 2
            xlsRange2(4, 2) = "２２２２"

            'xksm形式で名前をつけて保存
            xlsWorkbook.SaveAs(Filename:=strOutFileName, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

            MessageBox.Show("完了しました")

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        Finally

            'Rangeオブジェクトの解放
            Marshal.ReleaseComObject(xlsRange1)
            Marshal.ReleaseComObject(xlsRange2)

            'WorkSheetオブジェクトの解放
            Marshal.ReleaseComObject(xlsWorkSheet1)
            Marshal.ReleaseComObject(xlsWorkSheet2)

            'Sheetsオブジェクトの解放
            Marshal.ReleaseComObject(xlsSheets)

            'Workbookオブジェクトを解放
            Marshal.ReleaseComObject(xlsWorkbook)

            'Workbooksオブジェクトを解放
            Marshal.ReleaseComObject(xlsWorkbooks)

            '保存時の問合せダイアログ表示を有りに戻す
            xlsApplication.DisplayAlerts = True

            'Excelアプリケーションを閉じる 
            xlsApplication.Quit()

            'Excel.Applicationオブジェクトを解放 
            Marshal.ReleaseComObject(xlsApplication)

        End Try

    End Sub

End Class

Public Const TEMPLATE = "D:\TenmleteFiles\Template.xlsx"

strOutFileName = txtOutFolder.Text & "\" & DateTime.Now.ToString("yyyyMMdd-HHmmss") & ".xlsx"

'.xlsx形式で名前を付けて保存
xlsWorkbook.SaveAs(Filename:=strOutFileName, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook)