MSSQL – Import data from Excel to SQL Server with VBA

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

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

As you see, you

1. Generate the data in the code in the first not empty row in excel;
2. Use this data to assign it to variables;
3. 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! 🙂