MSSQL – Import data from Excel to SQL Server with VBA

Posted on December 21, 2015 by

Let’s imagine the following situation. You work in a small company, where one excel spreadsheet is used by many users locally. But you still want to see some kind of track of the results in a collected option. E.g. some information, when the users from their desks have run a specific program. And what was their result. Their parameters? And anything else. 🙂

This can be done with the all-mighty MS SQL Server. Let’s imagine that you need information like this:

It is quite ok if you know the username of the user, the date and the time he ran a program plus the current location of the file. A status as a result of the file is also ok to be known. This is the sql code, used to generate this table in a db:

CREATE DATABASE LogData
GO

USE [LogData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LogTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NULL,
	[CurrentDate] [nvarchar](50) NULL,
	[CurrentTime] [nvarchar](50) NULL,
	[CurrentLocation] [nvarchar](50) NULL,
	[Status] [nvarchar](50) NULL,
 CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE DATABASE LogData

USE [LogData]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[LogTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[UserName] [nvarchar](50) NULL,

[CurrentDate] [nvarchar](50) NULL,

[CurrentTime] [nvarchar](50) NULL,

[CurrentLocation] [nvarchar](50) NULL,

[Status] [nvarchar](50) NULL,

CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

So, what should you do? The way is to simply kindly make a connection to the MSSQL database with VBA, thus trying to make everything as smooth as possible 🙂 Something like this:

Option Explicit

Sub GenerateData()
     
    Dim conn            As New ADODB.Connection
    Dim l_row           As Long
    Dim s_username      As String
    Dim s_date          As String
    Dim s_time          As String
    Dim s_location      As String
    Dim s_status        As String
  
    With ActiveSheet
        conn.Open "Provider=SQLOLEDB;Data Source=GRO-PC;Initial Catalog=LogData;Integrated Security=SSPI;"
        
        l_row = last_row_with_data(1, ActiveSheet) + 1
        
        .Cells(l_row, 1) = Environ("username")
        .Cells(l_row, 2) = Date
        .Cells(l_row, 3) = Time
        .Cells(l_row, 4) = Application.ActiveWorkbook.FullName
        .Cells(l_row, 5) = make_random(2, 6)
        
        s_username = .Cells(l_row, 1)
        s_date = .Cells(l_row, 2)
        s_time = .Cells(l_row, 3)
        s_location = .Cells(l_row, 4)
        s_status = .Cells(l_row, 5)
                        
        conn.Execute "insert into dbo.LogTable (UserName, CurrentDate, CurrentTime, CurrentLocation, Status) values ('" & s_username & "', '" & s_date & "', '" & s_time & "', '" & s_location & "','" & s_status & "')"
            
        conn.Close
        Set conn = Nothing

    End With
End Sub

Public Function last_row_with_data(ByVal lng_column_number As Long, shCurrent As Variant) As Long
    last_row_with_data = shCurrent.Cells(Rows.Count, lng_column_number).End(xlUp).row
End Function

Public Function make_random(down As Integer, up As Integer)
    make_random = Int((up - down + 1) * Rnd + down)
End Function

Option Explicit

Sub GenerateData()

Dim conn As New ADODB.Connection

Dim l_row As Long

Dim s_username As String

Dim s_date As String

Dim s_time As String

Dim s_location As String

Dim s_status As String

With ActiveSheet

conn.Open "Provider=SQLOLEDB;Data Source=GRO-PC;Initial Catalog=LogData;Integrated Security=SSPI;"

l_row = last_row_with_data(1, ActiveSheet) + 1

.Cells(l_row, 1) = Environ("username")

.Cells(l_row, 2) = Date

.Cells(l_row, 3) = Time

.Cells(l_row, 4) = Application.ActiveWorkbook.FullName

.Cells(l_row, 5) = make_random(2, 6)

s_username = .Cells(l_row, 1)

s_date = .Cells(l_row, 2)

s_time = .Cells(l_row, 3)

s_location = .Cells(l_row, 4)

s_status = .Cells(l_row, 5)

conn.Execute "insert into dbo.LogTable (UserName, CurrentDate, CurrentTime, CurrentLocation, Status) values ('" & s_username & "', '" & s_date & "', '" & s_time & "', '" & s_location & "','" & s_status & "')"

conn.Close

Set conn = Nothing

End With

End Sub

Public Function last_row_with_data(ByVal lng_column_number As Long, shCurrent As Variant) As Long

last_row_with_data = shCurrent.Cells(Rows.Count, lng_column_number).End(xlUp).row

End Function

Public Function make_random(down As Integer, up As Integer)

make_random = Int((up - down + 1) * Rnd + down)

End Function

As you see, you

Generate the data in the code in the first not empty row in excel;
Use this data to assign it to variables;
Use these variables as parts of the insert query;

Pretty much that is all. The random could be anything else, I just liked it. Last but not least, to avoid errors due to missing library for DBO.Connection, make sure you have added the MS ActiveX Data Objects 2.8 Library like here:

Once it is there, you probably would be able to run the code smoothly. Available also in Github! 🙂

That is all! 🙂