How to export SQL Server data to Excel using VBA?

Question

Currently I'm testing how to export data from SQL Server Management Studio to Excel for an upcoming project.

I looked up online and came upon a code as follows:

Function ImportSQLtoRange(ByVal conString As String, ByVal query As String, ByVal target As Range) As Integer

On Error Resume Next

' Object type and CreateObject function are used instead of ADODB.Connection,
' ADODB.Command for late binding without reference to
' Microsoft ActiveX Data Objects 2.x Library

' ADO API Reference
' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx

' Dim con As ADODB.Connection
Dim con As Object
Set con = CreateObject("ADODB.Connection")

con.ConnectionString = conString

' Dim cmd As ADODB.Command
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")

cmd.CommandText = query
cmd.CommandType = 1         ' adCmdText

' The Open method doesn't actually establish a connection to the server
' until a Recordset is opened on the Connection object
con.Open
cmd.ActiveConnection = con

' Dim rst As ADODB.Recordset
Dim rst As Object
Set rst = cmd.Execute

If rst Is Nothing Then
    con.Close
    Set con = Nothing

    ImportSQLtoRange = 1
    Exit Function
End If

Dim ws As Worksheet
Dim col As Integer

Set ws = target.Worksheet

' Column Names
For col = 0 To rst.Fields.Count - 1
    ws.Cells(target.row, target.Column + col).Value = rst.Fields(col).Name
Next
ws.Range(ws.Cells(target.row, target.Column), ws.Cells(target.row, target.Column + rst.Fields.Count)).Font.Bold = True

' Data from Recordset
ws.Cells(target.row + 1, target.Column).CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set cmd = Nothing
Set con = Nothing

ImportSQLtoRange = 0

End Function

Sub TestImportUsingADO()

Dim conString As String
conString = "data source = server name; initial catalog = database; uid = name; password = password"

Dim query As String
query = "my query goes here"

Dim target As Range
Set target = ThisWorkbook.Sheets(2).Cells(3, 2)

target.CurrentRegion.Clear

Select Case ImportSQLtoRange(conString, query, target)
    Case 1
        MsgBox "Import database data error", vbCritical
    Case Else
End Select

End Sub

When I try to use the macro, I receive a message box as set in Case 1 in ImportSQLtoRange. But I'm not sure if that means that the code works and can access the query or this is an error message. If this shows that it does work how do I modify it so that it gives me the table I want based on my query?

Edit: Upon following Kazimierz Jawor suggestion, I commented on

On Error Resume Next

Now I get an error as follows:

Clicking Debug show the error coming from

con.open

What does that mean?

comment On error resume next line and you get error exactly in the line where it is produced. Next provide more information about error: error number, error name, in which line it happened — Kazimierz Jawor
– Kazimierz Jawor, Commented Dec 4, 2017 at 7:39
return a recordeset rather than integer and set your function return to the recordset, rst. — Nathan_Sav
– Nathan_Sav, Commented Dec 4, 2017 at 7:41

JohnyL · Accepted Answer · 2017-12-04 10:10:48Z

1

Please try to use the below code

Dim conn As New ADODB.Connection
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Below is the example:

 Sub GetDataFromADO()
'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
    objMyConn.Open

'Set and Excecute SQL Command'
    strSQL = "select * from myTable"

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL            

'Copy Data to Excel'
    Do until objMyRecordset.EOF //Test whether the current record is after the last record
          'Your Code
    Loop

End Sub

edited Dec 4, 2017 at 10:10

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

answered Dec 4, 2017 at 8:05

nishit dey

4581 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

nishit dey Over a year ago

You can search for the answer here

hjh93 Over a year ago

Ironically, that was where I got the code from. But I can't figure out which part do I need to change to make it work...

nishit dey Over a year ago

Did you try the one which i posted in the solution

JohnyL Over a year ago

The fastest way to import data from recordset to sheet is Range.CopyFromRecordSet method.

nishit dey Over a year ago

@JohnyL : the changes you suggested, Thank you for that. But i have a question EOF or the originial EOR, what is the difference between them.

|

ASH · Accepted Answer · 2017-12-08 17:05:42Z

How about doing it this way?

Sub GetDataFromADO()

    'Declare variables'
        Set objMyconn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset
        Dim rc As Long

    'Open Connection'
        objMyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_DB_Name; Integrated Security=SSPI;"

        objMyconn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyconn
        objMyCmd.CommandText = "select * from [Person].[BusinessEntity] "
        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyconn
        objMyRecordset.Open objMyCmd

    'Copy Data to Excel'
        'ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
        Application.ActiveCell.CopyFromRecordset (objMyRecordset)
        rc = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(rc + 1, 1).Select
        'Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Value
        objMyconn.Close

End Sub

You can find lots of useful tips about integrating Excel and SQL Server from the link below.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

Collectives™ on Stack Overflow

How to export SQL Server data to Excel using VBA?

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related