Results 1 to 1 of 1

Threaded View

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™

    [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:
    1. Option Explicit
    2. 'Add a reference to MS Excel xx.0 Object Library
    3. 'Add a reference to MS ActiveX Data Objects 2.x Library
    4. Private Sub Command1_Click()
    5.     Dim oRs As ADODB.Recordset
    6.     Dim oCnn As ADODB.Connection
    7.     Dim oApp As Excel.Application
    8.     Dim oWB As Excel.Workbook
    9.     Dim i As Integer
    10.     'Connect to your SQL Server DB
    11.     Set oCnn = New ADODB.Connection
    12.     oCnn.ConnectionString = "provider=sqloledb;data source=MySQLServer;initial catalog=MyDatabase;integrated security=sspi;"
    13.     oCnn.Open
    14.     'Create your recordset
    15.     Set oRs = New ADODB.Recordset
    16.     oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
    17.     'Create an instance of Excel and add a new blank workbook
    18.     Set oApp = New Excel.Application
    19.     oApp.Visible = False
    20.     Set oWB = oApp.Workbooks.Add
    21.     'Add the field names as column headers (optional)
    22.     For i = 0 To oRs.Fields.Count - 1
    23.         oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    24.     Next
    25.     oWB.Sheets(1).Range("1:1").Font.Bold = True
    26.     'Copy the records to the Excel worksheet
    27.     oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    28.     oRs.Close
    29.     Set oRs = Nothing
    30.     oCnn.Close
    31.     Set oCnn = Nothing
    32.     oWB.Close SaveChanges:=True, FileName:="D:\Test.xls"
    33.     Set oWB = Nothing
    34.     oApp.Quit
    35.     Set oApp = Nothing
    36. 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!
    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 i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.