Dim oExcel As New Excel.Application 'Declare New Excel Object Dim obook As Excel.Workbook = oExcel.Workbooks.Add 'Add Workbook to excel Dim oSheet As Excel.Worksheet = obook.Worksheets(1) 'Declare Excel Worksheet Dim dataArray(ds_main.Tables(Export_Table).rows.count - 1, ds_main.Tables(Export_Table).columns.Count - 1) As Object ' Declare Array object with size (row count by column count) Dim r As Integer Dim c As Integer Dim column_count As Integer Dim ROW_COUNT As Integer Dim excel_count As Integer Dim sheet_count As Integer

excel_count = 1 ' set excel count to 1 as idicator to increment for each row to know when 65K records have been processed sheet_count = 1 ' set sheet count to 1 (excel sheet integer starts at 1 not 0 ROW_COUNT = 0 ' set array row count to 0 oSheet.Name = "Page " & sheet_count ' name the first sheet Page 1 'Add columns to Sheet For column_count = 0 To ds_main.Tables(Export_Table).Columns.Count - 1 oSheet.Cells(1, column_count + 1) = ds_main.Tables(Export_Table).Columns(column_count).ColumnName Next column_count ProgressBar.Value = 0 ProgressBar.Maximum = ds_main.Tables(Export_Table).Rows.Count - 1 For r = 0 To ds_main.Tables(Export_Table).Rows.Count - 1 'For each row in dataset For c = 0 To ds_main.Tables(Export_Table).columns.Count - 1 'for each column dataArray(ROW_COUNT, c) = ds_main.Tables(Export_Table).Rows(r).Item(c) 'add column data into array Next c If excel_count = 65000 Then 'record count is 65000 oSheet.Range("A2").Resize(excel_count, ds_main.Tables(Export_Table).columns.Count - 1).Value = dataArray 'Copy Array into excel sheet oSheet = obook.Worksheets.Add(After:=obook.Worksheets("Page " & sheet_count)) 'add new sheet to workbook after previous sheet sheet_count += 1 'increment sheet count by one oSheet.Name = "Page " & sheet_count 'name new sheet excel_count = 1 'reset to excel count to 1 ROW_COUNT = 0 'reset the row count for array to 0 'Array.Clear(dataArray, 0, 65000) 'reset the array ReDim dataArray(ds_main.Tables(Export_Table).rows.count - 1, ds_main.Tables(Export_Table).columns.Count - 1) 'Add column headers to new sheet For column_count = 0 To ds_main.Tables(Export_Table).Columns.Count - 1 oSheet.Cells(1, column_count + 1) = ds_main.Tables(Export_Table).Columns(column_count).ColumnName Next column_count Else 'if records not equal to 65K excel_count += 1 'Add 1 to excel count ROW_COUNT += 1 'Add 1 to array row count End If If r = ds_main.Tables(Export_Table).rows.count - 1 Then 'If this is the final record oSheet.Range("A2").Resize(excel_count, ds_main.Tables(Export_Table).columns.Count - 1).Value = dataArray 'copy array to excel sheet End If ProgressBar.PerformStep() Next r obook.SaveAs(FileName) 'Save the excel sheet oSheet = Nothing 'set objects to nothing oExcel = Nothing GC.Collect() 'Garabarage Collection to reclaim space ProgressBar.Value = 0 Exporting_Label.Visible = False MessageBox.Show("Export Complete", "Finish", MessageBoxButtons.OK, MessageBoxIcon.Information)