Introduction

This article describes solutions for Microsoft Excel-SQL Server import-export using VBA.

There are two ways to import SQL Server data into Microsoft Excel using VBA:

  1. To create a QueryTable connected to a database table using Excel or VBA.
  2. To insert database data to a range using ADO Recordset or Excel add-ins.

The QueryTable object has a native Excel feature to refresh data.
So users can refresh the data when needed without additional coding.
However, a user-friendly way to change query parameters requires coding.

To refresh data inserted into a range using ADO, just insert the data again.
This way requires a control which runs the refresh macro.

There are two ways to export Excel data to SQL Server using VBA:

  1. To use ADO.
  2. To use Excel add-ins that allow saving data and support VBA integration.

The article describes the attached VBA code example that works in Microsoft Excel 2003-2016.
The example data are stored in Microsoft Azure SQL Database, and you can test the solution right after download.

The article also describes the code applicable with the SaveToDB Excel add-in.
You may use the SaveToDB add-in to solve your tasks with fewer efforts.

Table of Contents

SQL Server Data Import to Excel using QueryTable

Function ImportSQLtoQueryTable

The function creates a native Excel QueryTable connected to the OLE DB data source specified by the conString parameter.

The result is nearly the same as using the standard Excel connection dialog box.

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

    On Error Resume Next

    Dim ws As Worksheet
    Set ws = target.Worksheet

    Dim address As String
    address = target.Cells(1, 1).address

    ' Procedure recreates ListObject or QueryTable

    If Not target.ListObject Is Nothing Then     ' Created in Excel 2007 or higher
        target.ListObject.Delete
    ElseIf Not target.QueryTable Is Nothing Then ' Created in Excel 2003
        target.QueryTable.ResultRange.Clear
        target.QueryTable.Delete
    End If

    If Application.Version >= "12.0" Then        ' Excel 2007 or higher
        With ws.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;" & conString), _
            Destination:=Range(address))

            With .QueryTable
                .CommandType = xlCmdSql
                .CommandText = StringToArray(query)
                .BackgroundQuery = True
                .SavePassword = True
                .Refresh BackgroundQuery:=False
            End With
        End With
    Else                                          ' Excel 2003
        With ws.QueryTables.Add(Connection:=Array("OLEDB;" & conString), _
            Destination:=Range(address))

            .CommandType = xlCmdSql
            .CommandText = StringToArray(query)
            .BackgroundQuery = True
            .SavePassword = True
            .Refresh BackgroundQuery:=False
        End With
    End If

    ImportSQLtoQueryTable = 0

End Function

' Source: http://support.microsoft.com/kb/816562

Function StringToArray(Str As String) As Variant

    Const StrLen = 127
    Dim NumElems As Integer
    Dim Temp() As String
    Dim i As Integer

    NumElems = (Len(Str) / StrLen) + 1
    ReDim Temp(1 To NumElems) As String

    For i = 1 To NumElems
       Temp(i) = Mid(Str, ((i - 1) * StrLen) + 1, StrLen)
    Next i

    StringToArray = Temp
End Function

Code comments:

  • The query parameter can contain a SELECT or EXECUTE query.
  • The resulting data will be inserted starting from the top left cell of the target range.
  • If the target range contains a ListObject or QueryTable object, it will be deleted, and a new object will be created instead.
    If you need to change the query only, just change the QueryTable.CommandText property.
  • Pay attention to the .SavePassword = True line.
    Microsoft Excel stores passwords without encryption.
    If possible, use the trusted connection. However, it is not supported by Microsoft Azure SQL Database yet.

Test Code of SQL Server Data Import to Excel using QueryTable

Sub TestImportUsingQueryTable()

    Dim conString As String
    conString = GetTestConnectionString()

    Dim query As String
    query = GetTestQuery()

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

    Select Case ImportSQLtoQueryTable(conString, query, target)
        Case Else
    End Select

End Sub

To top

SQL Server Data Import to Excel using ADO

Function ImportSQLtoRange

The function inserts SQL Server data to the target Excel range using ADO.

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

Code comments:

  • The query parameter can contain a SELECT or EXECUTE query.
  • The resulting data will be inserted starting from the top left cell of the target range.
  • Using Object types and the CreateObject function instead of direct use of ADO types
    lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
    This code works in Microsoft Excel 2003-2016.
  • Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.

Test Code of SQL Server Data Import to Excel using ADO

Sub TestImportUsingADO()

    Dim conString As String
    conString = GetTestConnectionString()

    Dim query As String
    query = GetTestQuery()

    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

To top

SQL Server Data Import to Excel using SaveToDB Add-In

The SaveToDB add-in allows connecting to databases, to text files, and the web using Data Connection Wizard, and supports OLE DB, ODBC, .NET and internal providers.

You may reload data using the Reload button at the ribbon or in the Context menu, or from VBA macros.

However, the add-in does not support connecting to new data sources from macros.

The add-in can save a lot of developer time when you need to implement changing query parameters.
You may modify the parameters by setting new values to named cells like Range("Company") = "ABC".
You may learn about this feature in the attached SaveToDB examples for VBA developers.

Procedure TestImportUsingSaveToDB

The procedure reloads active table data.
The table is a native Excel ListObject connected using the Data Connection Wizard.

Sub TestImportUsingSaveToDB()

    Dim addIn As COMAddIn
    Dim addInObj As Object

    Set addIn = Application.COMAddIns("SaveToDB")
    Set addInObj = addIn.Object

    addInObj.Load

End Sub

Code comments:

If the table is an Excel ListObject connected to a database using OLE DB or ODBC, then the action is the same as ListObject.QueryTable.Refresh BackgroundQuery:=False.

In other cases (web and file connections or databases through .NET providers), the add-in refreshes data using internal procedures. Moreover, the macro remains the same.

To top

Excel Data Export to SQL Server using ADO

Function ExportRangeToSQL

The function exports the sourceRange data to a specified database table.

The optional beforeSQL code is executed before exporting, and the optional afterSQL code is executed after exporting.

The following logic of the export process is used in the example:

  1. Delete all data from a temporary import table.
  2. Export Excel data to the empty temporary import table.
  3. Update desired tables from the temporary import table data.

Specially developed stored procedures are used in the first and third steps.
You may adapt them to your task.
Moreover, a universal code is used to transfer Excel data to a destination table.

Function ExportRangeToSQL(ByVal sourceRange As Range, _
    ByVal conString As String, ByVal table As String, _
    Optional ByVal beforeSQL = "", Optional ByVal afterSQL As String) 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
    con.Open

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

    ' BeginTrans, CommitTrans, and RollbackTrans Methods (ADO)
    ' http://msdn.microsoft.com/en-us/library/ms680895(v=vs.85).aspx

    Dim level As Long
    level = con.BeginTrans

    cmd.CommandType = 1             ' adCmdText
    If beforeSQL > "" Then
        cmd.CommandText = beforeSQL
        cmd.ActiveConnection = con
        cmd.Execute
    End If

    ' Dim rst As ADODB.Recordset
    Dim rst As Object
    Set rst = CreateObject("ADODB.Recordset")

    With rst
        Set .ActiveConnection = con
        .Source = "SELECT * FROM " & table
        .CursorLocation = 3         ' adUseClient
        .LockType = 4               ' adLockBatchOptimistic
        .CursorType = 0             ' adOpenForwardOnly
        .Open

        ' Column mappings

        Dim tableFields(100) As Integer
        Dim rangeFields(100) As Integer

        Dim exportFieldsCount As Integer
        exportFieldsCount = 0

        Dim col As Integer
        Dim index As Integer

        For col = 0 To .Fields.Count - 1
            index = Application.Match(.Fields(col).Name, sourceRange.Rows(1), 0)
            If index > 0 Then
                exportFieldsCount = exportFieldsCount + 1
                tableFields(exportFieldsCount) = col
                rangeFields(exportFieldsCount) = index
            End If
        Next

        If exportFieldsCount = 0 Then
            ExportRangeToSQL = 1
            Goto ConnectionEnd
        End If

        ' Fast read of Excel range values to an array
        ' for further fast work with the array

        Dim arr As Variant
        arr = sourceRange.Value

        ' The range data transfer to the Recordset

        Dim row As Long
        Dim rowCount As Long
        rowCount = UBound(arr, 1)

        Dim val As Variant

        For row = 2 To rowCount
            .AddNew
            For col = 1 To exportFieldsCount
                val = arr(row, rangeFields(col))
                If IsEmpty(val) Then
                Else
                    .Fields(tableFields(col)) = val
                End If
            Next
        Next

        .UpdateBatch
    End With

    rst.Close
    Set rst = Nothing

    If afterSQL > "" Then
        cmd.CommandText = afterSQL
        cmd.ActiveConnection = con
        cmd.Execute
    End If

    ExportRangeToSQL = 0

ConnectionEnd:

    con.CommitTrans

    con.Close
    Set cmd = Nothing
    Set con = Nothing

End Function

Code comments:

  • The preliminary column mappings are used for fast transferring Excel range column data to a Recordset column.
  • Excel data types are not verified.
  • Using Object types and the CreateObject function instead of direct use of ADO types
    lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
    This code works in Microsoft Excel 2003-2016.
  • Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.

Test Code of Excel Data Export to SQL Server

The temporary dbo04.ExcelTestImport table is used for inserting Excel data.

This table is cleared before exporting by the dbo04.uspImportExcel_Before stored procedure.

The dbo04.uspImportExcel_After stored procedure updates the source dbo04.ExcelTest table with values from dbo04.ExcelTestImport.

This technique simplifies the Excel part of an application but requires additional database objects and server side coding.

Sub TestExportUsingADO()

    Dim conString As String
    conString = GetTestConnectionString()

    Dim table As String
    table = "dbo04.ExcelTestImport"

    Dim beforeSQL As String
    Dim afterSQL As String

    beforeSQL = "EXEC dbo04.uspImportExcel_Before"
    afterSQL = "EXEC dbo04.uspImportExcel_After"

    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet

    Dim qt As QueryTable
    Set qt = GetTopQueryTable(ws)

    Dim sourceRange As Range

    If Not qt Is Nothing Then
        Set sourceRange = qt.ResultRange
    Else
        Set sourceRange = ws.Cells(3, 2).CurrentRegion
    End If

    Select Case ExportRangeToSQL(sourceRange, conString, table, beforeSQL, afterSQL)
        Case 1
            MsgBox "The source range does not contain required headers", vbCritical
        Case Else
    End Select

    ' Refresh the data
    If Not qt Is Nothing Then
        Call RefreshWorksheetQueryTables(ws)
    ElseIf ws.Name = ws.Parent.Worksheets(1).Name Then
    Else
        Call TestImportUsingADO
    End If

End Sub

The called RefreshWorksheetQueryTables procedure updates all worksheet QueryTables and ListObjects.

Sub RefreshWorksheetQueryTables(ByVal ws As Worksheet)

    On Error Resume Next

    Dim qt As QueryTable

    For Each qt In ws.QueryTables
        qt.Refresh BackgroundQuery:=True
    Next

    Dim lo As ListObject

    For Each lo In ws.ListObjects
        lo.QueryTable.Refresh BackgroundQuery:=True
    Next

End Sub

The called GetTopQueryTable function returns the most top QueryTable object connected to a database.

Function GetTopQueryTable(ByVal ws As Worksheet) As QueryTable

    On Error Resume Next

    Set GetTopQueryTable = Nothing

    Dim lastRow As Long
    lastRow = 0

    Dim qt As QueryTable
    For Each qt In ws.QueryTables
        If qt.ResultRange.row > lastRow Then
            lastRow = qt.ResultRange.row
            Set GetTopQueryTable = qt
        End If
    Next

    Dim lo As ListObject

    For Each lo In ws.ListObjects
        If lo.SourceType = xlSrcQuery Then
            If lo.QueryTable.ResultRange.row > lastRow Then
                lastRow = lo.QueryTable.ResultRange.row
                Set GetTopQueryTable = lo.QueryTable
            End If
        End If
    Next

End Function

To top

Excel Data Export to SQL Server using SaveToDB Add-In

The SaveToDB add-in allows saving data changes from Excel to databases.

You may save data using the Save button at the ribbon or from VBA macros.

The simplest scenario is saving changes to a single target table. It works without coding.
Moreover, you may load data from tables, views, or stored procedures.

If you need to save the data to multiple normalized tables, you have to use stored procedures for INSERT, UPDATE, and DELETE operations. It is not so hard.

Procedure TestExportUsingSaveToDB

The macro saves data changes of the active table to a database and reloads the data.

Sub TestExportUsingSaveToDB()

    Dim addIn As COMAddIn
    Dim addInObj As Object

    Set addIn = Application.COMAddIns("SaveToDB")
    Set addInObj = addIn.Object

    addInObj.Save

End Sub

Code comments:

The SaveToDB add-in makes a lot of work behind the scene.

It saves table metadata, a copy of loaded data, and data changes on hidden sheets.
You may even close the workbook. When the Save action is called, it builds and sends
INSERT, UPDATE and DELETE statements (or specified stored procedures) to a database.

You may learn hidden sheets using the SaveToDB, Options, Developer Options tab, and generated SQL commands using the SaveToDB, Save, View Save Changes SQL menu item.

To top

Connection String Functions

The example contains several useful functions for working with connection strings.

Function OleDbConnectionString

If the Username parameter is empty, the function returns an OLE DB connection string for trusted connection.

Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
    ByVal Username As String, ByVal Password As String) As String

    If Username = "" Then
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";Integrated Security=SSPI;Persist Security Info=False;"
    Else
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";User ID=" & Username & ";Password=" & Password & ";"
    End If

End Function

Function OdbcConnectionString

If the Username parameter is empty, the function returns an ODBC connection string for trusted connection.

Function OdbcConnectionString(ByVal Server As String, ByVal Database As String, _
    ByVal Username As String, ByVal Password As String) As String

    If Username = "" Then
        OdbcConnectionString = "Driver={SQL Server};Server=" & Server _
            & ";Trusted_Connection=Yes;Database=" & Database
    Else
        OdbcConnectionString = "Driver={SQL Server};Server=" & Server _
            & ";UID=" & Username & ";PWD=" & Password & ";Database=" & Database
    End If

End Function

Function GetTestConnectionString

You may use this code to test the example deployed in Microsoft Azure SQL Database.

Function GetTestConnectionString() As String

    GetTestConnectionString = OleDbConnectionString( _
        "ko7h266q17.database.windows.net", "AzureDemo50", _
        "excel_user@ko7h266q17", "ExSQL_#02")
    ' GetTestConnectionString = OleDbConnectionString(".", "AzureDemo50", "", "")

End Function

Function GetTestQuery

You may try any of SELECT or EXECUTE queries.

Function GetTestQuery() As String

    GetTestQuery = "SELECT * FROM dbo04.ExcelTest"
    ' GetTestQuery = "EXEC dbo04.uspExcelTest"

End Function

To top

Conclusion

You can use the attached example code to import-export data between Microsoft Excel and SQL Server.

The code works fine with Microsoft SQL Server 2005-2014 and Microsoft Azure SQL Database, and in Microsoft Excel 2003-2016.
You may adapt it to another database platforms like MySQL, Oracle, or DB2 as the code uses OLE DB and ODBC connections.

You may also use the SaveToDB add-in as a database layer starting Excel 2007.
SaveToDB allows implementing projects with fewer efforts as it solves database layer tasks from the box.

Download

Example Source Code
Importing and Exporting Excel and SQL Server Data Using VBA
Includes source codes and examples for working with SQL Server using VBA
Version: 1.7 | 10/23/2015 | 0.1MB | Article

SaveToDB Add-In and Examples
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.10 | 11/02/2016 | 20.9MB | Homepage | Article 1 | Article 2
SaveToDB Examples for VBA Developers
Includes examples with source codes for VBA developers
Version: 6.10 | 11/02/2016 | 10.1MB | Homepage | Article
Download

Comments  

# ddk 2016-06-21 20:35
Hi Sergey,

I Use free plan hosting at somee.com
the suggest connection string from them is
"workstation id=MyWeb.mssql.somee.com;packe t size=4096;user id=myuser_SQLLogin_1;pwd=MyPas sword;data source=MyWeb.mssql.somee.com;p ersist security info=False;initial catalog=MyDBase"

I replaced OLEDB conString with:
conString = "Provider=SQLOLEDB.1;Data Source=MyWeb.mssql.somee.com;I nitial Catalog=MyDBase;User ID=myuser_SQLLogin_1;Password= MyPassword;"

it always return "database import error" when using "TestImportUsingADO" (rst always returnn "Nothing")
I had google it but can't find out, Please help. thanks
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-06-22 00:04
Hi,
Try to connect to your server using SQL Server Management Studio.
The hosting firewall may block your connection.
Reply | Reply with quote | Quote
# ddk 2016-06-23 15:37
Hi Sergey,

Thanks for your reply, instead using SQL Server Management Studio, I'm using HeidiSQL and it can connect with Network Type MS SQL Server (TCP/IP) with port 1433.
now I change the connection string with :
conString = "Provider=SQLOLEDB.1;Integrate d Security=SSPI;Persist Security Info=False;Initial Catalog=automate;Data Source=automate.mssql.somee.co m;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;user id=mattmate_SQLLogin_1;pwd=Pas sword;Workstation ID=automate.mssql.somee.com;Us e Encryption for Data=False;Tag with column collation when possible=False;"

But still fail.
Above is the real account details, the dbo is "dbo.lisensi" Hopefully I get suggest from you or anyone here.
thanks before
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-06-23 16:27
Download, install, and use ODBC Driver 11 SQL Server
https://www.microsoft.com/en-US/download/details.aspx?id=36434

The native OLEDB provider cannot connect to your instance.
Reply | Reply with quote | Quote
# Ram 2016-06-06 14:29
Hi,

While using the ADO to export, time(hh:mm [ff]) is exported as a null value. Is there a workaround for this?

Ram
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-06-22 00:15
Hi Ram,
Excel works fine with DateTime values only. You may try to cast the time type to the datetime type in the SELECT query used as a column desctription.
Reply | Reply with quote | Quote
# Nick 2016-05-22 12:55
Hi Sergey

Thank you for your example code, this is exactly what I am looking to do!

I've encountered a problem however - when I use the ExportRangeToSQL function, everything runs ok up until the .UpdateBatch, whereupon I receive the error message
"Object '[MyTableName]' does not exist."

This is odd, because it reads from the table earlier in the function.

Do you have any ideas what I might be doing wrong?

Nick
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-05-27 18:26
Hi Nick,

Did you solve your issue?

It seems that you may add a schema to your table name.
Reply | Reply with quote | Quote
# Watkins-Lewis 2016-04-27 10:52
This is a really useful tutorial and got it working first time, so thank you!

I want it to run automatically however, and I always get a 'SQL Server Login' pop up where I have to hit 'OK' - is there something I can add to stop this happening?

Thanks in advance
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-04-27 20:10
Thank you for kindly words!

Try right click, Table, External Data Properties..., Connection Properties, Definition, and check Save Password.
Reply | Reply with quote | Quote
# Neeraj 2016-03-02 23:40
Hi

I am trying to import data to excel from MS SQL Server 2012. Not sure about how to get the connection string information? I am unable to find arguments for OleDbConnectionString() method. Can you please help?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2016-03-03 07:00
Hi Neeraj,
See Microsoft OLE DB Provider for SQL Server at http://www.connectionstrings.com/sql-server/
Reply | Reply with quote | Quote
# pszemoc 2015-11-17 13:49
Hi Sergey,
Although I tested your code for a specific task, it will produce error always when setting the target to another sheet:

ImportSQLtoQueryTable ConnString, "select * from [Arkusz1$] where Column1 is not null", Sheets(2).Cells(1, 1)

You might consider improving it with specifying worksheet in target. Check the mpalanco's comment in this link http://stackoverflow.com/questions/13624967/use-sql-style-query-in-excel-using-vba

Good job!
Reply | Reply with quote | Quote
# Shorton 2015-09-10 12:48
Sergey: Great article. I got my push almost working the way I want. If I put my xls on the Windows Server running SQL server, I can get it to work. but I need to have the xls on the desktop running the VBA. The server errors if I point it at the PC. I've checked permissions on the desktop folder, they are open. I can write to it while remoted into the Windows server as a user that has no permissions on my desktop. Maybe it's a syntax thing.

This is the string that fails. If I change the path to a local (to the SQL server) path, it works:
strSQL = "insert into [WLS3] select [Job_Number] FROM
OPENDATASOURCE('Microsoft.ACE. OLEDB.12.0','Data Source=\\MYPCNAME\MYOPENFOLDER NAME\wls12.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]"
Reply | Reply with quote | Quote
# Nick 2015-08-13 12:22
Great article Sergey, thanks.

I too would like to see what the stored procedures should look like, if that's possible?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-08-26 20:16
Thank you for kindly words, Nick!

You may implement any logic in stored procedures.
Reply | Reply with quote | Quote
# Zac 2015-07-24 02:01
Hello,

Im using SQL Server Data Import to Excel using ADO - Function ImportSQLtoRange

The variables were not passing from TestImportUsingADO() so i just set the variables like conString and query at the begging of ImportSQLtoRange.

However the Object rst is always set to Nothing so I think it is not assigning to cmd.Execute.

Any help would be greatly appreciated

Thanks
Reply | Reply with quote | Quote
# ddk 2016-06-21 18:20
Hi Zac,

Same problem with me
whether the problem has been solved ?
please help to share with me. thanks..
Reply | Reply with quote | Quote
# Alexander 2015-06-20 14:53
Sergey,

I have Excel 2010 32bit.
I found a problem in Function ExportRangeToSQL().
My sourceRange has 200000 rows and 40 columns.
I get the error:
...
Dim arr As Variant
arr = sourceRange.Value ''' error - Out of Memory

What can you do?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-06-20 15:41
Hi Alexander,

MSDN:
32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process.

You may see used memory in Task Manager.

You may install 64-bit Office or reduce amount of rows saved at once.
Reply | Reply with quote | Quote
# Alexander 2015-06-20 16:03
Sergey,

I need to quickly transfer data from Excel to MSSQL, ~ 200K - 300K rows and 10 columns (in the table with 40 columns and 30 of them were informational)
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-06-20 16:12
You may use the SaveToDB add-in.

See the SaveToDB Suite link in the Download section.

It allows saving, merging and publising data.
It is the easiest solution.

And this is free.
Reply | Reply with quote | Quote
# Alexander 2015-06-20 15:57
Sergey,
I can't use 32-bit Office for all Bank.

This 2 variants work slowly after ~ 12000 rows in cycle:

Dim rng As Range, i As Long, n As Long, arr As Variant, rowN As Long, colN As Integer
colN = 40
With ThisWorkbook.Sheets("test")
Set rng = .Range(Cells(2, 1), Cells(200000, colN))

'''''''' 1)
rowN = rng.Rows.Count
For i = 2 To rowN
For n = 1 To colN
Cells(1, 1).Value = Cells(i, n).Value
Next
Next

''''''''' 2)
arr = rng.Value
rowN = UBound(arr, 1)
Cells(1, 1).Value = rowN
For i = 2 To rowN
For n = 1 To colN
Cells(1, 1).Value = arr(i, n)
Next
Next
End With
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-06-20 16:16
You need to use arrays to bulk read and write range values like

arr = rng.Value
arr(i,j) = ...
rng.Value = arr

These methods work fast.
Reply | Reply with quote | Quote
# Alexander 2015-04-27 20:59
Excellent article!

Sergey,
How to encrypt the username and password in the connection string? We can open the Excel file in the Open Office and there will be visible password
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-05-12 16:01
Excel does not allow to encrypt passwords.
You may encrypt the entire workbook or uncheck the Save Password checkbox in the connection definition.
Reply | Reply with quote | Quote
# Alexander 2015-05-12 19:48
I create string constant for connectionString. I use SQL-login with password. I grant permissions for my stored procedures. I drop/create QueryTable in Excel-marcos

You know the Recordset property for the number of records transferred at once to the server (using .UpdateBatch) ?
Reply | Reply with quote | Quote
# EmEe 2015-03-31 17:34
Will this run on a 64 bit excel that is going to a 32 bit server?
Reply | Reply with quote | Quote
# Mike 2015-03-05 20:38
thanks. This is absolutely amazing. I had no idea that this could be done.
Reply | Reply with quote | Quote
# Voshee 2014-12-01 05:20
Thank you.
The code is working perfectly.
Reply | Reply with quote | Quote
# Liliya Sadikova 2014-10-15 07:29
Thank you very much. Worked like a charm!
Reply | Reply with quote | Quote
# AlexSal1010 2014-10-09 17:12
Great article, thank you very much! This has helped me in sooo many situations where SSIS was not able to be leveraged
Reply | Reply with quote | Quote
# sandy 2014-09-24 14:31
Hi.. I need an excel macro/query that fetches the data what is there in sql tables to excel sheet..could you please provide a direct code and what needs to be modified in that please !!
Reply | Reply with quote | Quote
# Sergey Vaselenko 2014-09-27 23:15
Hi Sandy,
You may call ImportSQLtoQueryTable or ImportSQLtoRange from your macro.
Reply | Reply with quote | Quote
# Kris 2014-08-08 15:50
Hi, Thanks for your code to import SQL data into Excel. It was very helpful to me. However I am having a small issue. I am trying to run a query ( select top 1000 * from tablename order by column1 asc;) to get the results.HOwever I am getting an empty set of data stating import has failed. That query was running 46 sec on the server. However when I remove the order clause, I am getting results into the excel.In this case, the query is running 1 sec on the server. I guess the query taking 46 sec is causing ImportSQLtoRange function to timeout/fail in between. Is there anyway to get around this. Let me know if my question is not clear.
Reply | Reply with quote | Quote
# Sergey Vaselenko 2014-08-08 16:44
Try to add index to column1.
You may also increase CommandTimeout. See
http://msdn.microsoft.com/en-us/library/ms678265(v=vs.85).aspx
Reply | Reply with quote | Quote
# Kris 2014-08-11 22:56
Thank you. CommandTimeout helped.
Reply | Reply with quote | Quote
# carsto 2014-07-17 14:46
I can NOT clear the existing SQL table, is that possible?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2014-07-21 07:06
Yes, it is possible.
You may clear the table in a stored procedure like dbo02.uspImportExcel_Before or execute the DELETE FROM statement using the beforeSQL variable.
Reply | Reply with quote | Quote
# Richi 2014-06-03 03:27
Hello,
Really nice code.

I run this code, and it basically export anything on the worksheet to sql.
My question is, what if the data already in the table, can we have an update function, instead of insert like right now?

Can you help me to figure out the code?

thank you so much
richi
Reply | Reply with quote | Quote
# Moon45 2014-05-22 09:17
It looks like a very nice and handy code. Thanks a lot.

Do you know if the recordset.addnew will replace entries with existing primary keys, or an error will occur due to existing primary key? If there is an error, do you know how could I cope efficiently with it?

For the moment I use the REPLACE INTO query for each line of my table, but it is not so efficient and doesn't allow the mapping.

Thanks
Reply | Reply with quote | Quote
# Dan 2013-04-23 12:46
Great info and examples! Thanks for publishing this. Do you happen to have an example of handling return values in ADO from calling a stored procedure?
Reply | Reply with quote | Quote
# Dan 2013-04-23 13:13
Thanks! But how do I actually capture the return value? Cmd1.parameters(0).value?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2013-04-23 13:41
Here is a good example of working with stored procedure parameters:

msdn.microsoft.com/en-us/library/ms675869(v=vs.85).aspx
Reply | Reply with quote | Quote
# Sergey Vaselenko 2013-04-23 12:59
Use "exec sp" format for the query instead of "select * from ...".
It works.
Reply | Reply with quote | Quote
# Ryan 2013-04-18 13:30
First off, thank you! I do have two questions though I hope you can assist with. 1) I have Criteria Fields set up in my Excel Query because the table is so large and I only need to edit a portion. Can I do this and still export changes I made in excel back to the SQL server? (keeping all other original table data in tact). 2) I'm just a user of the SQL server not the developer, so I don't think stored procedures is an option for me. Therefore, I don't believe I can ue the 'SQL Server Data Import to Excel using ADO' outlined above. Is there another way? I tried the SaveToDB excel add-in and it worked, but again you can't set Criteria Fields to the query (that I could figure out) and some tables are too large.
Reply | Reply with quote | Quote
# Sergey Vaselenko 2013-04-22 20:12
Thank you, Ryan, for the compliments.

1) The synchronization of the Excel and database data is the most complex part of the scenario.
The algorithm above is simple. It pushes all the data into a table. And do with these data what you want...
So, I think it's a hard way.

2) If SaveToDB is suitable for you, wait for a week.
SaveToDB 3.0 will be launched soon. It has a functionality to filter tables and views using the ribbon fields and it works perfectly.
Also it allows to save the changes of a view to its underlying table without any coding.
Hope, it is the best solution for you.
Reply | Reply with quote | Quote
# Marcio 2013-02-27 19:17
Hi, for me best code about Excel to SQL - SQL to Excel.

I have 2 questions.

1st: I want to specify my excel range to send data to SQL. Ex: I want to record A10:Z300, from worksheet Plan1 inside SQL BD. How can I make it possible?

2nd: how can i use IP machine into GetTestConnectionString = OleDbConnectionString(".", ".", "", "")?

Thx for any help and sry about bad english text ever :D
Reply | Reply with quote | Quote
# Sergey Vaselenko 2013-02-27 20:38
Hi,

Thank you for your compliment.

2nd: You may use IP-address as a server name. The full format is

<Server name or IP-address>[,<Port>][ \<Instance name>]

1st: You have 3 choices:

1. Worksheets("Plan1").Range("A10").CurrentRegion
2. Worksheets("Plan1").Range("A10").ListObject.DataBodyRange
3. Worksheets("Plan1").Range("A10").ListObject.QueryTable.ResultRange

You may also get a ListObject object from a collection.

For example, Worksheets("Plan1").ListObject s(1)
Reply | Reply with quote | Quote
# laksh 2013-01-15 17:20
good one.
Reply | Reply with quote | Quote
# Jeremy 2012-11-22 13:38
Hi, thanks for your tutorial, amazine and fast to deploy :)

However i got a mistake while creating the xxx_after procedure on SQL server.
the code seems to be alright but when i want to push the procedure the following error appears:
(originally in french)

Msg 8102, level 16, state 1, Procedure ExcelTestImport, Ligne 53
Impossible to update identity column: 'ID'.

the error is right here in code:

-- Update source data from import data
UPDATE dbo.ExcelTest
SET
Error HERE==> [ID] = i.[ID]
, [Float] = i.[Float]
--
I tried to comment this row :
[ID] = i.[ID]
and the procedure got executed without showing any error.
Then i tried from excel which is all configured as your model and i get this mesgerror:

"The source range does not contain required headers" (cf your code in vba sub:TestExportUsingADO)

Any idea about that someone?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2013-04-22 20:50
The right code is

-- Update source data from import data

UPDATE dbo02.ExcelTest
SET
[Float] = i.[Float]
, [Datetime] = i.[Datetime]
, [Nvarchar] = i.[Nvarchar]
FROM
dbo02.ExcelTest et
INNER JOIN dbo02.ExcelTestImport i ON et.ID = i.ID
Reply | Reply with quote | Quote
# dedalus2014 2015-04-17 20:20
What does the procedure uspImportExcel_Before look like?
Reply | Reply with quote | Quote
# dedalus2014 2015-04-17 22:48
Tried replacing the uspImportExcel_After procedure with

'afterSQL = "UPDATE dbo02.ExcelTest SET [Float] = i.[Float], [Datetime] = i.[Datetime], [Nvarchar] = i.[Nvarchar] FROM dbo02.ExcelTest et INNER JOIN dbo02.ExcelTestImport i ON et.ID = i.ID".

Interestingly, the database will not be updated with new rows in the excel table. Any idea anyone?
Reply | Reply with quote | Quote
# Sergey Vaselenko 2015-05-12 16:11
Sorry for the silence. Do you solve your task?
Reply | Reply with quote | Quote