This article will show you how to read data from an Excel spreadsheet and insert it into a SQL Server database.

First, we'll define a DSN-less connections string:

Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=your_db_name;" & _
"Data Source=YOUR_SERVER_NAME


Define and set the worksheet

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")


Open a connection to the database. It's important that the connection CursorLocation property be adUseServer.


Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseServer
.Open stADO
.CommandTimeout = 0
End With


For this example, we assume that the data range starts at A1 and has 3 columns and 5 rows.
The database table name is dbo.book. It has the following fields: isbn, booknr, authorid.
The first 2 are of string type, therefore we surround the values with single quotes.
The third one is an int type, so we use a conversion function before appending this value to the sql string.


Dim row As Integer
Dim strSQL As String
For row = 1 To 5
strSQL = "insert into dbo.book (isbnnbr, booknm, authorid) values ('" & _
wsSheet.Cells(row, 1).Value & "','" & wsSheet.Cells(row, 2).Value & "'," & wsSheet.Cells(row, 3).Value & ")"
'execute the insert statement
con.Execute strSQL
Next row


Clean up the connection:

con.Close
Set con = Nothing