Excel-SQL Server Import-Export using VBA
Introduction
There are two ways to import SQL Server data into Microsoft Excel using VBA:
- To create a QueryTable connected to a database table using Excel or VBA.
- 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.
To refresh data inserted using ADO, just insert the data again.
There are two ways to export Excel data to SQL Server using VBA:
- To use ADO.
- To use Excel add-ins that allow saving data and support VBA integration.
You can download the example and continue reading when you try the code.
The attached VBA code example works in Microsoft Excel 2003-2016.
The example works with data in Microsoft Azure SQL Database. So, you can test the solution right after download.
Before to continue
This article was written in June 2011. It contains the tested code that you can use. I have a lot of thanks.
I would like to recommend you to take a look at my e-book "Excel Applications. 10 Steps for VBA Developers."
You can also download the workbook examples and the SaveToDB add-in used as a free VBA library.
With the SaveToDB add-in, you can create more functional VBA applications with fewer efforts.
For example, you can save data changes from Excel to a database using a single call like GetAddIn().Save.
E-book E-book Examples SaveToDB Add-In
Table of Contents
- Introduction
- SQL Server Data Import to Excel using QueryTable
- SQL Server Data Import to Excel using ADO
- SQL Server Data Import to Excel using SaveToDB Add-In
- Excel Data Export to SQL Server using ADO
- Excel Data Export to SQL Server using SaveToDB Add-In
- Connection String Functions
- Conclusion
- See Also
- Download
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(conString As String, query As String, 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
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(conString As String, query As String, 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 ' https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/ado-api-reference?view=sql-server-ver16 ' 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
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 can 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 can modify the parameters by setting new values to named cells like Range("Company") = "ABC".
You can 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 (the web and file connections or databases through .NET providers), the add-in refreshes data using internal procedures. Moreover, the macro remains the same.
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:
- Delete all data from a temporary import table.
- Export Excel data to the empty temporary import table.
- Update desired tables from the temporary import table data.
Specially developed stored procedures are used in the first and third steps.
You can adapt them to your task.
Moreover, a universal code is used to transfer Excel data to a destination table.
Function ExportRangeToSQL(sourceRange As Range, conString As String, table As String, _ Optional beforeSQL = "", Optional 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 ' https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/ado-api-reference?view=sql-server-ver16 ' 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 = 0 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(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(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
Excel Data Export to SQL Server using SaveToDB Add-In
The SaveToDB add-in allows saving data changes from Excel to databases.
You can 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 can 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 can 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 can learn hidden sheets using the SaveToDB, Options, Developer Options tab, and generated SQL commands using the SaveToDB, Save, View Save Changes SQL menu item.
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(Server As String, Database As String, _ Username As String, 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(Server As String, Database As String, _ Username As String, 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
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-2016 and Microsoft Azure SQL Database, and in Microsoft Excel 2003-2016.
You can adapt it to another database platforms like MySQL, Oracle, or DB2 as the code uses OLE DB and ODBC connections.
You can 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.
See Also
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.11 | 06/01/2024 | 0.1MB | Article |
Download |
SaveToDB Add-In and Examples | |
SaveToDB Add-In for Microsoft Excel
The add-in allows using Microsoft Excel as a client application platform Version: 10.14 | 04/30/2024 | 18.1MB | Getting Started | Editions | Article 1 | Article 2 |
Download |
E-books | |
E-book. Excel Applications. 10 Steps for VBA Developers
The e-book shows how to create database client applications with VBA Version: 1.0 | 03/20/2017 | 2.4MB |
Download |