-
Apr 24th, 2006, 05:56 PM
#1
[FAQ's: OD] How do I transfer data from SQL Server to Excel using ADO?
You can do this many ways. One way is to create an ADO recordset of the records you wish to export to Excel using Excel's Object Model.
Here is my example of using Excel's Object Model.
VB 6 Code Example:
VB Code:
Option Explicit
'Add a reference to MS Excel xx.0 Object Library
'Add a reference to MS ActiveX Data Objects 2.x Library
Private Sub Command1_Click()
Dim oRs As ADODB.Recordset
Dim oCnn As ADODB.Connection
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim i As Integer
'Connect to your SQL Server DB
Set oCnn = New ADODB.Connection
oCnn.ConnectionString = "provider=sqloledb;data source=MySQLServer;initial catalog=MyDatabase;integrated security=sspi;"
oCnn.Open
'Create your recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
'Create an instance of Excel and add a new blank workbook
Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add
'Add the field names as column headers (optional)
For i = 0 To oRs.Fields.Count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
Next
oWB.Sheets(1).Range("1:1").Font.Bold = True
'Copy the records to the Excel worksheet
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
oWB.Close SaveChanges:=True, FileName:="D:\Test.xls"
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing
End Sub
Last edited by RobDog888; Apr 27th, 2006 at 02:48 PM.
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
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
|