Speed Up Write To Excel From VB.NET

The easiest thing u can do during a write for a significant change are the following:

oExcel.ScreenUpdating=false
oExcel.EnableEvents=false

By the way, the fastest for a data dump still is CopyFromRecordset. Use a ADODB.Recordset to do so to dump data into a range.


http://support.microsoft.com/kb/306022

'Create a Recordset from all the records in the Orders table.
        Dim sNWind As String
        Dim conn As New ADODB.Connection()
        Dim rs As ADODB.Recordset
        conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";")
        conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

        'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)

        'Transfer the field names to Row 1 of the worksheet:
        'Note: CopyFromRecordset copies only the data and not the field
        '      names, so you can transfer the fieldnames by traversing the
        '      fields collection.
        Dim n As Int32
        For n = 1 To rs.Fields.Count
            oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
        Next

        'Transfer the data to Excel.
        oSheet.Range("A2").CopyFromRecordset(rs)

        'Save the workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book3.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

        'Close the connection
        rs.Close()
        conn.Close()

Select allOpen in new window

Do you know which namespace holds the ADODB definitions? As in what I should Import?

x77,

What is this block doing?

Using x As New ExpExcel
      With x.Wks.Range("A1.L1")
         .Font.Bold = True : .Font.Color = ColorTranslator.ToWin32(Color.Blue)
         .Interior.Color = ColorTranslator.ToWin32(Color.LightCyan)
      End With
      x.Column("A", 13)                               'Trabajo
      x.Column("B", 5)                                'Sup
      x.Column("C:E", 8, , Excel.XlHAlign.xlHAlignRight) 'Fimte,Ffmte,Dur
      x.Column("F", 4)                                'Av
      x.Column("G", 100)                              'Destra

      x.Value("A1", Datos) : Datos = Nothing
  End Using

Especially the x.Column and x.Value calls?

I send the data using
    x.Value("A1", Datos)

But I format columns also  :
   Column A - Width 13 chr
               b              5

Also can set format and alignement.

If you have a DataTAble with 1000 rows and 10 Columns

   Dim Datos(NumRows, NumCols)

   Then Fill Datos with each data on your DataTable
   Then use the ExpExcel class.

The ExpExce class, knows if Wookbook is closed.
If you maintain the Excel WookBook opene, you can add more wookseets to same WookBook.

I have write some samples on EE from ExpExcel class.

https://www.experts-exchange.com/questions/26533988/public-report-class-to-print-to-excel.html?sfQueryTermInfo=1+1+10+30+datos.getlength+valu

In this sample, I Export the DataGridView content to Excel.