Code to export data from grid to excel in vb.net2005 or vb.net

[mxo]

Hi all

A while ago I wrote a code for an application that manipulate data using SQL query and displays the results on a grid now this time around i would like to have a button to export the results from the grid to excel

see the code below:
Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class frmMain
Inherits System.Windows.Forms.Form

Private Const MAX_RECORDS As Integer = 10000
Private strConnect As String

Private Function GetFileName() As String
Dim NewFileName As String
Dim Newfile As OpenFileDialog = New OpenFileDialog()
Dim MyChoice As DialogResult

With Newfile
.Filter = "Access Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
.FilterIndex = 1
.DefaultExt = "mdb"
.InitialDirectory = "C:\Documents and Settings\User\My Documents\Line.mdb"
.CheckFileExists = False
.ReadOnlyChecked = True
.Title = "Open Access Data File"
End With

MyChoice = Newfile.ShowDialog
If MyChoice = DialogResult.Cancel Then
NewFileName = ""
Else
NewFileName = Newfile.FileName
End If
If NewFileName.Length > 0 Then
Return NewFileName
End If
End Function

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim FileName As String
Dim AdoConn As New ADODB.Connection()
Dim MyCat As New ADOX.Catalog()
Dim tbl As ADOX.Table

FileName = GetFileName()
If FileName.Length = 0 Then
Exit Sub
End If
Me.Text = "SQL LineSystem: " & FileName
strConnect = "Provider=Microsoft.JetOLEDB.4.0;Password="""";UserID=Admin;Data Source="
strConnect &= FileName
AdoConn.Open(strConnect)
MyCat.ActiveConnection = AdoConn

For Each tbl In MyCat.Tables
lstTables.Items.Add(tbl.Name.ToString)
Next
AdoConn.Close()

End Sub

Please assist
Mxo
South Africa

 

[lestatdelioncourt]

look into the excel interop classes, these allow u to create an excel spreadsheet, and populate the data.

 

[lestatdelioncourt]

the excel interop relies on excel being installed where the program is run.

 

[mxo]

Hi Lestatdelioncourt

I am not following your suggestion pls bear with me.

 

[Sorwen]

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
        Dim cBreak As Boolean = False

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)


        Dim n As Int32

        rTable.Open()
        oSheet.Range("A2").CopyFromRecordset(rTable)

        'Format cell width
        oSheet.Cells.Select()
        oSheet.Cells.EntireColumn.AutoFit()
        oSheet.Cells(1, 1).Select()

        If cBreak = True Then
            oSheet.Range("A:B").Delete()
        End If
        rTable.Close()

        oBook.SaveAs(WorkbookLocation & WorkbookName)
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
End Sub

This is what I use. What lestatdelioncourt is saying is for this to work the computer it is running on must have Excel Installed. When you use it like this you can give it most (if not all I'm not sure on that) VBA code you would normally be able to do in Excel.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

You will want to take out this part as you will not need it:

If cBreak = True Then
            oSheet.Range("A:B").Delete()
        End If

I forgot that is part of other code I took out. A common source we pull from sometimes takes special coding and that was part of it I for got to remove

-I hate Microsoft!
-Forever and always forward.