Exporting large set of data from db to excel

See more:

MS-Excel

export

Hye..

Usually when I want to export data from database to excel using vb, I used this method but when it comes to large amount of data (which will generate approx 16k rows and 100 columns header), this method would not be suitable.
How can I modify my code to suit my requirement?

Thanks for your time. :)

If Not IO.Directory.Exists("M:\COMM\EPJ_HDTS\MS_EXCEL\INT_TNA") Then
           IO.Directory.CreateDirectory("M:\COMM\EPJ_HDTS\MS_EXCEL\INT_TNA")
       Else
           Dim s As String
           For Each s In System.IO.Directory.GetFiles("M:\COMM\EPJ_HDTS\MS_EXCEL\INT_TNA")
               System.IO.File.Delete(s)
           Next s
           IO.Directory.CreateDirectory("M:\COMM\EPJ_HDTS\MS_EXCEL\INT_TNA")

       End If

       Dim Excel As Object
       Excel = CreateObject("Excel.Application")

       ssql = "SELECT DISTINCT DEPTID_T FROM  dbo.NEW_DEPT_INTERNAL_TNI_DATA "
       dtDiv = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(ssql, connHDTS)
       If dtDiv.Rows.Count > 0 Then
           For Each drDiv As DataRow In dtDiv.Rows
               Dim intRow, intColumnValue, scolumn As Integer
               With Excel
                   .Workbooks.Open("M:\COMM\EPJ_HDTS\MS_EXCEL\DEPTTNA.xlsx")
                   .Worksheets(2).Select()
                   ssql = "SELECT TOP (100) PERCENT CRS_GRPID_T, CRS_GRPDESC_T FROM dbo.Q_NEW_DEPT_COURSE_MASTER_INTERNAL_RANK_CRS_GROUP WHERE DEPTID_T = '" & drDiv("DEPTID_T") & "' ORDER BY NUM"
                   intRow = 1
     dt = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(ssql, connHDTS)
                   If dt.Rows.Count > 0 Then
                       For Each row As DataRow In dt.Rows()
                           For intColumnValue = 0 To 1
                               .Cells.Font.Size = 8
                               .Cells(intRow, intColumnValue + 1).Value = row(intColumnValue).ToString
                           Next
                           intRow += 1
                       Next
                   End If

                   ssql = "SELECT TOP (100) PERCENT CRS_GRPID_T, CRS_GRPDESC_T, CRS_TITLEID_N, CRS_TITLEDESC_T FROM dbo.Q_NEW_DEPT_COURSE_MASTER_INTERNAL_RANK_CRS_TITLE WHERE DEPTID_T = '" & drDiv("DEPTID_T") & "' ORDER BY DEPTID_T, CRS_GRPID_T, CRS_TITLEID_N"
                   intRow = 19
                   scolumn = 6
                   dt = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(ssql, connHDTS)
                   If dt.Rows.Count > 0 Then
                       For Each row As DataRow In dt.Rows()
                           For intColumnValue = 0 To 3
                               .Cells.Font.Size = 8
                               .Cells(intRow + 1, scolumn).Value = row(intColumnValue).ToString
                               intRow += 1
                           Next
                           intRow = 19
                           scolumn += 1
                       Next
                   End If

                   .Worksheets(3).Select()
                   intRow = 1
                   ssql = "Select * FROM dbo.Q_NEW_DEPT_INTERNAL_TNA_ALERT_DETAILS WHERE DEPTID_T = '" & drDiv("DEPTID_T") & "' AND CRS_GRPDESC_T = 'MACHINE MAINTENANCE & TROUBLE SHOOTING - REJECT TROUBLE SHOOTING'"
                   dt = DBLayer.Util.Data.MsSql.DBFunction.BuildDataTable(ssql, connHDTS)
                   For Each row As DataRow In dt.Rows()
                       For intColumnValue = 0 To 8
                           .Cells.Font.Size = 8
                           If (dt.Columns(intColumnValue).ColumnName.Contains("FRDATE_D") AndAlso Not row(intColumnValue) Is DBNull.Value) AndAlso row(intColumnValue).ToString <> "" Then
                               .Cells(intRow, intColumnValue + 1).Value = CDate(row(intColumnValue).ToString).ToString("dd/MM/yyyy")
                           Else
                               .Cells(intRow, intColumnValue + 1).Value = row(intColumnValue).ToString
                           End If

                       Next
                   Next
                   .ActiveWorkbook.SaveAs("M:\COMM\EPJ_HDTS\MS_EXCEL\INT_TNA\DEPT_TNA_INT_" + drDiv("DEPTID_T"))
               End With
           Next
       End If