Exporting DataGrid to Excel

I am displaying certain records in a DataGrid whose number of field display changes depending upon the fields the user want to select.

I want to EXPORT this whole DataGrid to MS Excel as it is. How to do so?

Since the number of field display changes, I cannot reserve the number of cells in Excel. So I want to transfer the Grid itself.

Isnt the DataGrid VB.NET?

Vb 6.

Is your grid bound to an ADO rs?

I am using:

Dim rs as ADODB.Recordset

object to populate DataGrid

Cool, easier. Use the .CopyFromRecordset method in Excel to import your object's recordset
directly to an Excel spreadsheet.

VB Code:

'From Excel help:
'This example copies the field names from a DAO Recordset object into the 
'first row of a worksheet and formats the names as bold. The example then 
'copies the recordset onto the worksheet, beginning at cell A2.
 
For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1),  _
    ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs

Thanks a lot Rob.

No prob. I like it when there is a method or function that can solve the problem like this. ;)

Rob, I changed your last line of the answer to something like this, and that also worked well:

oWorkSheet.Range("A2").CopyFromRecordset DataGrid1.DataSource

This is beneficial when you are using the same recordset to execute different queries. In that case if the results the grid results can be transferred as above.

Cool tip! Thanks :thumb:

Rob,

Is this possible when the datagrid is not bound.
I built the datadrid up manually, the results are passed back from a server side subroutine, so binding was not possible.

Thanks
andy

If your server side function returns an ADO recordset then you can use that rs to copyinto excel.

I created a front end sales analysis in VB.net, the user fills in the details and then the request is sent to the server. The server does all the processing, which is quite a lot and returns the results in a system delimited format.
I then split the data up and fille the datgrid.

An export to Excel would then be great when the user needs that liitle more funtionality.

My skills are more server side, I'm making the VB up as a go a long, only started last month, but having great fun.

Cheers
andy

good day! i'm using microsoft visual basic 2008 express edition and i wanted to export the datagrid to excel too but i'm having errors. This code i got from microsoft but when i tried it, i got these errors, type 'ADODB Connection' is not defined but that was already defined using dim, am i right? i'm newbie to visual basic so bare with me. any help would really be appreciated. thanks!

Code:

---

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        '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()
    End Sub

---

I do have the same problem. I cannot save data from datagrid to excel. I use inputbox so user can input data to datagrid. Then the user can click the button to save it to excel. I'm using VB.NET. Can someone please show me some code on how to create that?