Tutorial: How to automate importing data from Excel into a database using a macro (VBA)

The Setup

Public Function ImportData()End

Concept Overview

INSERT INTO myTableName 
(field1, field2)
VALUES
(row1Value1, row1Value2),
(row2Value1, row2Value2)

Let’s write some code!

Const strDbConn As String = "server=myServerName;Database=myDatabaseName;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}"

ADODB Error?

Inserting data with SQL: the header

Inserting data with SQL: the actual data

How to overcome a data import limitation

Putting it together

The Entire Code

Bonus: remove blank values with VBA Code

stevesohcot.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Test web hosting before buying

Test hosting service before buying

Life is a journey of twists and turns, peaks and valleys, mountains to climb and oceans to explore.

ISP: The Interface Segregation Principle

Udacity Front-End Web Development Nanodegree: How I Came In On and What I Got Out Of It

The Struggles of Using ASP.NET Razor Pages

Php Grid Open Source For Mac

Best Data Scraping Tools

Brief Explanation of Heap Sort and Bubble Sort

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Steve Sohcot

Steve Sohcot

stevesohcot.com

More from Medium

Using Boto3 to get data into S3 through Kinesis

SQL Join Challenges for Improving SQL Skills

Cleaning Up Messy Data in Power Query

Winter Olympic part 2: add data to SQL database