Export DataGrid to Excel-VBForums
Results 1 to 14 of 14

Thread: Export DataGrid to Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    3

    Export DataGrid to Excel

    Hi All,

    Hope you are all doing Great!!

    I got a code from this site which will export the datagrid values in VB to excel.

    This code will open a new Excel Sheet, However it does not copy the values of the DataGrid to excel

    Code:
    Public Sub subGridToExcel()
    On Error GoTo ErrorLine
    Screen.MousePointer = vbHourglass
    Dim xl As Object
    Dim wb As Object
    Dim ws As Object
    Dim i  As Long
    Dim x  As Long
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Add()
    Set ws = xl.ActiveWindow.ActiveSheet
    ws.Columns().ColumnWidth = 10
    For x = 0 To frmFind.DataGrid1.Row - 1
    frmFind.DataGrid1.Row = x
    frmFind.DataGrid1.Col = 1
    For i = 1 To frmFind.DataGrid1.Col
    frmFind.DataGrid1.Col = i
    If frmFind.DataGrid1.Text <> "" Then
    ws.Cells(x, i) = frmFind.DataGrid1.Text
    ws.Cells(x, i).HorizontalAlignment = -4131
    'xlLeft
    End If
    Next
    Next
    xl.Visible = True
    ErrorLine:
    Screen.MousePointer = vbDefault
    End Sub

    Please let me know if this can be worked out.

    Thank you for your Help!!

    Please have a Great day!!

    Best Regards,
    Nirmal Udupa.

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

    Re: Export DataGrid to Excel

    Welcome to th Forums.
    I hardly ever use datagrids but isnt there a columns collection "Cols". The loop is just using the Col property for the upper bounds of the inner loop where it loops throught the rows columns

    Code:
    Public Sub subGridToExcel()
        On Error GoTo ErrorLine
        Screen.MousePointer = vbHourglass
        Dim xl As Object
        Dim wb As Object
        Dim ws As Object
        Dim i  As Long
        Dim x  As Long
    
        Set xl = CreateObject("Excel.Application")
        Set wb = xl.Workbooks.Add()
        Set ws = xl.ActiveWindow.ActiveSheet
        ws.Columns().ColumnWidth = 10
    
        For x = 0 To frmFind.DataGrid1.Row - 1
            frmFind.DataGrid1.Row = x
            frmFind.DataGrid1.Col = 1
                For i = 1 To frmFind.DataGrid1.Cols '<===Here changed?
                    frmFind.DataGrid1.Col = i
                    If frmFind.DataGrid1.Text <> "" Then
                        ws.Cells(x, i) = frmFind.DataGrid1.Text
                        ws.Cells(x, i).HorizontalAlignment = -4131
                        'xlLeft
                    End If
                Next
        Next
        xl.Visible = True
    ErrorLine:
        Screen.MousePointer = vbDefault
    End Sub
    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
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Export DataGrid to Excel

    The DataGrid is a strange beast. It does not know exactly how many Rows it actually has. There is an ApproxCount property that seems to work but I have no idea if it is 100% reliable. You should use the RecordCount property of the underlying recordset object instead.

    Also, accessing a Row (without changing the current cell) is always relative to the current row. Typically you need to ensure the datagrid is on the first row before looping through the entire grid.

    Anyways, here is an example of looping through the entire grid.

    Code:
        Dim lngRow As Long
        Dim lngCol As Long
        
        For lngRow = 0 To DataGrid1.ApproxCount - 1
            For lngCol = 0 To DataGrid1.Columns.Count - 1
                'can also use the CellValue method instead of the CellText
                Debug.Print DataGrid1.Columns(lngCol).CellText(DataGrid1.GetBookmark(lngRow)),
            Next
        Next

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    3

    Re: Export DataGrid to Excel

    Hi RobDog888/brucevde,

    Thanks for the code. However, these code does not seems to Work. it just opens the Excel Application with a new Workbook.

    Anyways Thank you for your time for giving a try and I really appreciate the unconditional help you provide to the beginners through this forum.

    Please have a Wonderful day!!!

    Warm Regards,
    Nirmal Udupa.
    India.

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

    Re: Export DataGrid to Excel

    Its just the inner loop of your original code. Try merging Brucevde's code into yours.
    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

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    3

    Re: Export DataGrid to Excel

    Hi RobDog888/brucevde,

    Hope you are all doing Great!!

    Thanks for the reply RobDog888.

    Please find the tested code which will export datagrid data to Excel.

    Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
    Code:
    Public Sub export2excel()
    On Error GoTo er
    Dim oExcel As Object
    Set oExcel = CreateObject("Excel.Application")
    Dim oWorkBook As Object
    Dim oWorkSheet As Object
    Dim i As Integer, k As Integer
    Dim lRow As Long
    Dim LastRow As Long
    Dim LastCol As Long
    oExcel.Visible = False
    oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
    Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")
    
    i = 2 'Row in Excel
    
    LastRow = frmFind.DataGrid1.Row 'Save Current row
    LastCol = frmFind.DataGrid1.Col 'and column
    frmFind.DataGrid1.Row = 0 'Fixed Row is -1
    Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
         For k = 1 To frmFind.DataGrid1.Columns.Count - 1
              frmFind.DataGrid1.Col = k 'Fixed Column is -1
              oWorkSheet.Cells(i, k).Font.Bold = False
              oWorkSheet.Cells(i, k).Font.Color = vbBlack
              oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
         Next
         i = i + 1
         If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
            frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
         Else
             Exit Do
         End If
    Loop
    frmFind.DataGrid1.Row = LastRow 'Restore original Row
    frmFind.DataGrid1.Col = LastCol 'and Column
    
    oExcel.Workbooks("Nirmal.xls").Save
    oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
    oExcel.Quit
    
    er:
    If Err.Number = 1004 Then
    Exit Sub
    End If
    End Sub
    Thank you and Please have a Wonderful Weekend.

    Warm Regards,
    Nirmal Udupa.
    India.

  7. #7
    New Member
    Join Date
    Mar 2008
    Posts
    4

    Re: Export DataGrid to Excel

    what is frmFind?
    is it a textbox or what?

    reply urgent!!!

    because im a noob brain

  8. #8
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Export DataGrid to Excel

    The prefix frm usually indicates the name of a Form.

    txt would indicate the name of a textbox.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  9. #9
    New Member
    Join Date
    Mar 2008
    Posts
    4

    Re: Export DataGrid to Excel

    so if i dont have any textbox just the datafields of my datagrid to will be inserted to excel

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

    Re: Export DataGrid to Excel

    Use your datagrids object name and reference down to the cell needed.
    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
    Mar 2008
    Posts
    4

    Re: Export DataGrid to Excel

    can u give me a exsample so that i can understand better..
    pls. help me!!!

  12. #12
    New Member
    Join Date
    Apr 2008
    Posts
    1

    Re: Export DataGrid to Excel

    you can export from dataset

    For i = 0 To ds.Tables(0).Rows.Count - 1
    For j = 0 To ds.Tables(0).Columns.Count - 1
    xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)
    Next
    Next

    http://vb.net-informations.com/excel...e_to_excel.htm


    you can export from datagridview

    For i = 0 To DataGridView1.RowCount - 2
    For j = 0 To DataGridView1.ColumnCount - 1
    xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value.ToString()
    Next
    Next



    http://vb.net-informations.com/excel...w_to_excel.htm

  13. #13
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Thumbs up Re: Export DataGrid to Excel

    Quote Originally Posted by nirmaludupa View Post
    Hi RobDog888/brucevde,

    Hope you are all doing Great!!

    Thanks for the reply RobDog888.

    Please find the tested code which will export datagrid data to Excel.

    Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
    Code:
    Public Sub export2excel()
    On Error GoTo er
    Dim oExcel As Object
    Set oExcel = CreateObject("Excel.Application")
    Dim oWorkBook As Object
    Dim oWorkSheet As Object
    Dim i As Integer, k As Integer
    Dim lRow As Long
    Dim LastRow As Long
    Dim LastCol As Long
    oExcel.Visible = False
    oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
    Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")
    
    i = 2 'Row in Excel
    
    LastRow = frmFind.DataGrid1.Row 'Save Current row
    LastCol = frmFind.DataGrid1.Col 'and column
    frmFind.DataGrid1.Row = 0 'Fixed Row is -1
    Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
         For k = 1 To frmFind.DataGrid1.Columns.Count - 1
              frmFind.DataGrid1.Col = k 'Fixed Column is -1
              oWorkSheet.Cells(i, k).Font.Bold = False
              oWorkSheet.Cells(i, k).Font.Color = vbBlack
              oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
         Next
         i = i + 1
         If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
            frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
         Else
             Exit Do
         End If
    Loop
    frmFind.DataGrid1.Row = LastRow 'Restore original Row
    frmFind.DataGrid1.Col = LastCol 'and Column
    
    oExcel.Workbooks("Nirmal.xls").Save
    oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
    oExcel.Quit
    
    er:
    If Err.Number = 1004 Then
    Exit Sub
    End If
    End Sub
    Thank you and Please have a Wonderful Weekend.

    Warm Regards,
    Nirmal Udupa.
    India.
    Thanks Nirmal It works

  14. #14
    New Member
    Join Date
    Dec 2012
    Posts
    2

    Re: Export DataGrid to Excel

    Originally Posted by nirmaludupa View Post
    Hi RobDog888/brucevde,

    Hope you are all doing Great!!

    Thanks for the reply RobDog888.

    Please find the tested code which will export datagrid data to Excel.

    Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
    Code:

    Public Sub export2excel()
    On Error GoTo er
    Dim oExcel As Object
    Set oExcel = CreateObject("Excel.Application")
    Dim oWorkBook As Object
    Dim oWorkSheet As Object
    Dim i As Integer, k As Integer
    Dim lRow As Long
    Dim LastRow As Long
    Dim LastCol As Long
    oExcel.Visible = False
    oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
    Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")

    i = 2 'Row in Excel

    LastRow = frmFind.DataGrid1.Row 'Save Current row
    LastCol = frmFind.DataGrid1.Col 'and column
    frmFind.DataGrid1.Row = 0 'Fixed Row is -1
    Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
    For k = 1 To frmFind.DataGrid1.Columns.Count - 1
    frmFind.DataGrid1.Col = k 'Fixed Column is -1
    oWorkSheet.Cells(i, k).Font.Bold = False
    oWorkSheet.Cells(i, k).Font.Color = vbBlack
    oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
    Next
    i = i + 1
    If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
    frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
    Else
    Exit Do
    End If
    Loop
    frmFind.DataGrid1.Row = LastRow 'Restore original Row
    frmFind.DataGrid1.Col = LastCol 'and Column

    oExcel.Workbooks("Nirmal.xls").Save
    oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
    oExcel.Quit

    er:
    If Err.Number = 1004 Then
    Exit Sub
    End If
    End Sub

    Thank you and Please have a Wonderful Weekend.

    Warm Regards,
    Nirmal Udupa.
    India.




    yeah baby... it really works... thanks guys... )

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.