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
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    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!
    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 i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  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
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    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!
    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 i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  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
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Export DataGrid to Excel

    The prefix frm usually indicates the name of a Form.

    txt would indicate the name of a textbox.

  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
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    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!
    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 i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  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
  •  



Click Here to Expand Forum to Full Width