-
Mar 6th, 2005, 03:26 AM
#1
Thread Starter
Addicted Member
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.
-
Mar 6th, 2005, 12:03 PM
#2
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!
• 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 6th, 2005, 12:34 PM
#3
Thread Starter
Addicted Member
Re: Exporting DataGrid to Excel
-
Mar 6th, 2005, 01:00 PM
#4
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!
• 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 7th, 2005, 01:54 AM
#5
Thread Starter
Addicted Member
Re: Exporting DataGrid to Excel
I am using:
Dim rs as ADODB.Recordset
object to populate DataGrid
-
Mar 7th, 2005, 01:53 PM
#6
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:
'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
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 8th, 2005, 12:02 AM
#7
Thread Starter
Addicted Member
Re: Exporting DataGrid to Excel
-
Mar 8th, 2005, 01:47 AM
#8
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!
• 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 13th, 2005, 12:27 PM
#9
Thread Starter
Addicted Member
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.
-
Mar 13th, 2005, 12:36 PM
#10
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!
• 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 18th, 2005, 12:33 PM
#11
New Member
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
-
Mar 18th, 2005, 12:48 PM
#12
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!
• 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 18th, 2005, 02:07 PM
#13
New Member
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
-
Apr 29th, 2009, 11:27 PM
#14
New Member
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
-
May 3rd, 2009, 08:05 AM
#15
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|