copy existing worksheet to end of workbook & name ctrl.text 1

[ImSimple]

I have a windows app with check boxes & a button. I want a copy of the existing sheet in the excel workbook to be made and named for every check box that is checked. I am getting an error that I need a new instance but i'm not sure where to put it.

Please help

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim sheets As Microsoft.Office.Interop.Excel.Worksheets
Dim sheet As Microsoft.Office.Interop.Excel.Worksheet
Dim range As Microsoft.Office.Interop.Excel.Range

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
excel = New Microsoft.Office.Interop.Excel.Application
sheet = New Microsoft.Office.Interop.Excel.Worksheet
'sheets = New Microsoft.Office.Interop.Excel.Worksheet
wb = excel.Workbooks.Add("F:\Work Stuff\Scope Sheet Creator\Scope Sheet Template.xlt")
excel.Visible = True
wb.Activate()

For Each ctrl As Control In Me.Controls
If (TypeOf ctrl Is CheckBox AndAlso CType(ctrl, CheckBox).Checked) Then
wb.Sheets("Template").activate()
wb.Sheets("Template").select()
wb.Sheets.Copy(After:=sheets.Count)
'sheet.Name("crtl.text")
End If
Next
excel = Nothing
wb = Nothing
End Sub

Jacque

 

[DaveInIowa]

Imports Microsoft.Office.Interop

...

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlWs As Excel.Worksheet

        xlWb = xlApp.Workbooks.Add("C:\temp\xlTemplate.xlt")
        xlWs = xlWb.Worksheets("Template")

        For Each ctrl As Control In Me.Controls
            If (TypeOf ctrl Is CheckBox AndAlso CType(ctrl, CheckBox).Checked) Then
                xlWb.Worksheets("Template").Copy(After:=xlWs)
                xlWs = xlWb.ActiveSheet
                xlWs.Name = CType(ctrl, CheckBox).Text
            End If
        Next

        If (xlWb.Worksheets.Count > 1) Then
            xlApp.DisplayAlerts = False
            xlWb.Worksheets("Template").Delete()
            xlApp.DisplayAlerts = True
        End If

        xlWb.SaveAs("C:\temp\xlWorkbook.xlsx")

        xlApp.Visible = True
        xlApp.UserControl = True
        xlApp.Quit()

        xlWs = Nothing
        xlWb = Nothing
        xlApp = Nothing

    End Sub

 

[ImSimple]

Thanks Dave.

Jacque