This is a long video but I really wanted to try and make my steps and explanations as clear as possible to follow. I hope that you will this video informative and helpful in your endeavor to develop an Excel tool to export, insert and update SQL database data using Excel. Please leave comments below and let me know what you thought of the video and if you have any questions.
PART 1: Export SQL Data to Excel
This will be the first part of my video training series where I will teach you how to Export and Update SQL Server table data using Excel in a step-by-step approach. In this first video I will show you how to add a button in Excel with VBA code behind the button that will export SQL data from an SQL table out of the AdventureWorks2014 sample SQL Server database.
Download Part 1 Sample File: AdventureWorks Update Utility 01a.xlsm
PART 2: Update SQL Server Data from Excel
In this second video in my training series on How to Export and Update SQL Server table data using Excel Step by Step, I show you in detail how to update SQL Server table data from Excel.
Upcoming videos will continue to refine this solution to include inserts, more update fields with foreign keys consideration, data validation, login prompt popup form, and other cool stuff.
Download Part 2 Sample File: AdventureWorks Update Utility 02a.xlsm
Prerequisites
-
Microsoft Excel - Some knowledge of Microsoft Excel would be desirable otherwise it might be too steep of a learning curve because in this video I will introduce you to programming concepts in the Visual Basic for Applications (VBA) programming environment within Excel. Secondly, I will introduce you to SQL Server and Structured Query Language (SQL).
-
SQL Server Access - You will need access to SQL Server (you can download and install the free SQL Server Express Edition for this training video series)
-
AdventureWorks2014 Database - You will need the AdventureWorks2014 sample SQL Server database which you can also download free and restore in SQL Server.
Links
Direct download link for SQL Server AdventureWorks2014 sample database: https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2014.bak
SQL Server 2014 Express: https://www.microsoft.com/en-us/download/details.aspx?id=42299
Related Reading
- Jon Muller: Excel SQL - Overview of my Excel and SQL training approach combining real-time connectivity with practical workflows
- 7 Reasons Excel is Great for SQL Database Data Bulk Editing - Why Excel provides data situational awareness for database editing
- Excel SQL Bulk Update - Example workbook demonstrating bulk update capabilities built with these techniques
- XLNavigator Benefits and Features - Excel add-in with built-in SQL Server import for users who need a ready-made solution
- Data Intelligence and Visualization - Using Excel as a business intelligence platform with SQL Server connectivity
Official Resources
- Microsoft SQL Server Sample Databases - Official repository for AdventureWorks and other SQL Server sample databases
- SQL Server Express Edition - Free SQL Server edition for learning and development
- Excel VBA ADO Reference - Technical documentation for ActiveX Data Objects in Excel VBA
Looking for more Excel and SQL integration tips? Check out Excel SQL Bulk Update or explore XLNavigator for powerful Excel enhancements.