Exporting DataGrid to Excel-VBForums
Results 1 to 15 of 15

Thread: Exporting DataGrid to Excel

  1. #1

    Thread Starter
    Addicted Member rpk_20061975's Avatar
    Join Date
    Jun 2001
    Location
    India
    Posts
    234

    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.

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

    Isnt the DataGrid VB.NET?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  3. #3

    Thread Starter
    Addicted Member rpk_20061975's Avatar
    Join Date
    Jun 2001
    Location
    India
    Posts
    234

    Re: Exporting DataGrid to Excel

    Vb 6.

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

    Is your grid bound to an ADO rs?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  5. #5

    Thread Starter
    Addicted Member rpk_20061975's Avatar
    Join Date
    Jun 2001
    Location
    India
    Posts
    234

    Re: Exporting DataGrid to Excel

    I am using:

    Dim rs as ADODB.Recordset

    object to populate DataGrid

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

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

    VB Code:
    1. 'From Excel help:
    2. 'This example copies the field names from a DAO Recordset object into the
    3. 'first row of a worksheet and formats the names as bold. The example then
    4. 'copies the recordset onto the worksheet, beginning at cell A2.
    5.  
    6. For iCols = 0 to rs.Fields.Count - 1
    7.     ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    8. Next
    9. ws.Range(ws.Cells(1, 1),  _
    10.     ws.Cells(1, rs.Fields.Count)).Font.Bold = True
    11. ws.Range("A2").CopyFromRecordset rs
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  7. #7

    Thread Starter
    Addicted Member rpk_20061975's Avatar
    Join Date
    Jun 2001
    Location
    India
    Posts
    234

    Resolved Re: Exporting DataGrid to Excel

    Thanks a lot Rob.

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

    No prob. I like it when there is a method or function that can solve the problem like this.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  9. #9

    Thread Starter
    Addicted Member rpk_20061975's Avatar
    Join Date
    Jun 2001
    Location
    India
    Posts
    234

    Re: Exporting DataGrid to Excel

    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.

  10. #10
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

    Cool tip! Thanks
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  11. #11
    New Member
    Join Date
    Jan 2005
    Posts
    8

    Re: Exporting DataGrid to Excel

    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

  12. #12
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,104

    Re: Exporting DataGrid to Excel

    If your server side function returns an ADO recordset then you can use that rs to copyinto excel.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  13. #13
    New Member
    Join Date
    Jan 2005
    Posts
    8

    Re: Exporting DataGrid to 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

  14. #14
    New Member
    Join Date
    Feb 2009
    Posts
    6

    Re: Exporting DataGrid to Excel

    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

  15. #15
    New Member
    Join Date
    May 2009
    Posts
    3

    Re: Exporting DataGrid to Excel

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.