-
Apr 24th, 2007, 04:45 PM
#1
Thread Starter
New Member
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.
-
Apr 24th, 2007, 04:56 PM
#2
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Apr 24th, 2007, 06:16 PM
#3
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
-
Apr 25th, 2007, 01:55 PM
#4
Thread Starter
New Member
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.
-
Apr 25th, 2007, 06:43 PM
#5
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Apr 27th, 2007, 01:48 PM
#6
Thread Starter
New Member
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.
-
Mar 28th, 2008, 05:53 AM
#7
New Member
Re: Export DataGrid to Excel
what is frmFind?
is it a textbox or what?
reply urgent!!!
because im a noob brain
-
Mar 28th, 2008, 07:04 AM
#8
Re: Export DataGrid to Excel
The prefix frm usually indicates the name of a Form.
txt would indicate the name of a textbox.
-
Mar 30th, 2008, 10:16 PM
#9
New Member
Re: Export DataGrid to Excel
so if i dont have any textbox just the datafields of my datagrid to will be inserted to excel
-
Mar 31st, 2008, 01:54 AM
#10
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Mar 31st, 2008, 04:17 AM
#11
New Member
Re: Export DataGrid to Excel
can u give me a exsample so that i can understand better..
pls. help me!!!
-
Apr 20th, 2008, 05:35 AM
#12
New Member
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
-
Apr 2nd, 2012, 08:26 AM
#13
New Member
Re: Export DataGrid to Excel
Originally Posted by nirmaludupa
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
-
Dec 19th, 2012, 09:35 PM
#14
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|