How To Copy Excel rows to new worksheets...

[HiBoo]

I've got a weird problem to address and after numerous attempts trying to solve this using Visual Studio 2005 I've nearly given up. Please note I am a beginner to VB.NET.

Problem Defined: Given an .xls file containing contact data from MS Outlook (Export the contact list to an excel file). I want to iterate through the source file one row at a time. The first row will be copied into a new xls file #1, the second row into a new xls file #2... etc...etc..etc...
A new file will be created until a variable number of files has been created at which point the process will just repeat itself until all rows of data have been copied.

For example, the contactfile.xls contains 50 rows of data. The file count is set to 10 and the process begins. Row1 is copied to file1, row2 is copied to file2, row3 to file3 etc... until we get to row10 copied to file10. row11 is now copied to file1, row11 into file2 etc until we get to row50 copied to file10 and the process ends.

Sorry for this being so long winded but it's the best way to explain. What this is used for is beyond me but it's been requested. I believe the new files will be e-mailed to sales personnel to use as new contact sheets. In any event, can someone please explain to be how to use an Excel reference to get this data and spit it out again?

This is what I've got so far as a trial and error test. It does not create a new file as required, it currently just creates a new worksheet instead. I can't seem to figure out how to leave all the data in the Source worksheet and just build upon the others.

Any or all help would be appreciated and or any other logical approaches to solve this problem would be appreciated. Thanks.

    Sub test()
        Dim wapp As Excel.Application
        Dim wsheet As Excel.Worksheet
        Dim wbook As Excel.Workbook

        wapp = New Excel.Application
        wapp.Visible = False
        wbook = wapp.Workbooks.Add()

        For i As Integer = wbook.Sheets.Count To 2 Step -1
            wbook.Sheets(i).Delete()
        Next

        wsheet = wbook.ActiveSheet

        Try
            Dim iX As Integer
            Dim iY As Integer
            Dim iC As Integer
            Dim CC As Integer = Me.DataGridView1.Columns.Count

            For iC = 0 To Me.DataGridView1.Columns.Count - 1
                wsheet.Cells(1, iC + 1).Value = Me.DataGridView1.Columns(iC).HeaderText
                wsheet.Cells(1, iC + 1).font.bold = True
                wsheet.Cells(1, iC + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                wsheet.Rows(1).autofit()
            Next
            wsheet.Name = "Source"

            For i As Int32 = 1 To Me.NumericUpDown.Value
                wsheet.Copy(After:=wsheet)
            Next
            '--- Rename each worksheet, with "Source" as the exception
            Dim ws As Excel.Worksheet
            Dim intIndex As Integer = 1
            For Each ws In wbook.Worksheets
                If ws.Name <> "Source" Then
                    ws.Name = "List " & intIndex.ToString("00")
                    intIndex += 1
                End If
            Next

            Dim currentWS As Excel.Worksheet
            Dim intWs As Integer = 1
            Dim intCountWS As Integer = wbook.Worksheets.Count - 1
            For iX = 0 To Me.DataGridView1.Rows.Count - 1
                '--- For each of the new sheets, add one record at time
                currentWS = wbook.Worksheets("List " & intWs.ToString("00"))
                For iY = 0 To Me.DataGridView1.Columns.Count - 1
                    currentWS.Cells(iX + 2, iY + 1).value = Me.DataGridView1(iY, iX).Value.ToString
                    currentWS.Cells(iX + 2, iY + 1).horizontalalignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
                Next
                intWs += 1
                If intWs > intCountWS Then intWs = 1
            Next
            wapp.Visible = True
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        wapp.UserControl = True
    End Sub