Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to export to Excel from vb.net code.

How-to

How to export to Excel from vb.net code.

by  Sorwen  Posted    (Edited  )
I see this question a lot and was rather surprised there wasn't a how to. This is the most effective manner I've found to export to excel without excessive code or getting someone's dll/plug-in/etc. Some people don't like it because it uses adodb rather than ado.net.

Code: 
Public Shared Sub DataToExcel(ByVal rTable As ADODB.Recordset, ByVal WorkbookLocation As String, ByVal WorkbookName As String)
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)
      
        rTable.Open()
        oSheet.Range("A1").CopyFromRecordset(rTable)
        rTable.Close()

        oBook.SaveAs(WorkbookLocation & WorkbookName)

        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
    End Sub

This is the total basics. There are all kinds of modifications you could do. In this format you can send it any commands that you would as if you were working in a Excel workbook's VBA.

Auto Format all cells width:
Code: 
        oSheet.Cells.Select()
        oSheet.Cells.EntireColumn.AutoFit()

Force save without overwrite confirmation:
Code: 
            oExcel.DisplayAlerts = False
            oBook.SaveAs(WorkbookLocation & WorkbookName)
            oExcel.DisplayAlerts = True
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top