Excel-SQL Server Import-Export using VBA

Introduction

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.

To refresh data inserted using ADO, just insert the data again.

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.

You may download the example and continue reading when you try the code.

Download Example

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 may 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 may also download the workbook examples and the SaveToDB add-in used as a free VBA library.

With the SaveToDB add-in, you may create more functional VBA applications with fewer efforts.

For example, you may 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
  • Function ImportSQLtoQueryTable
  • Test Code
• SQL Server Data Import to Excel using ADO
  • Function ImportSQLtoRange
  • Test Code
• SQL Server Data Import to Excel using SaveToDB Add-In
  • Procedure TestImportUsingSaveToDB
• Excel Data Export to SQL Server using ADO
  • Function ExportRangeToSQL
  • Test Code
• Excel Data Export to SQL Server using SaveToDB Add-In
  • Procedure TestExportUsingSaveToDB
• Connection String Functions
  • Function OleDbConnectionString
  • Function GetTestConnectionString
  • Function GetTestQuery
• Conclusion
• See Also
• Download