Get Free Tips
MSSQLTips.com
Home » Excel as Front End Application for SQL Server
Microsoft Excel Integration

Excel as Front End Application for SQL Server

written by Sebastiao Pereira 6 comments

Problem

Due to limited budgets, developing custom front-end applications is difficult for small- to medium-sized projects. In some cases, the additional expense associated with custom applications is not justified, even though the benefits of having this real-time data access are ideal.

For those cases, can Excel be used as a front-end application to interact with SQL Server as its data source?

Solution

As a front end for an SQL Server database, Excel can be useful for simple applications due to its familiarity with users, cost-effectiveness, ease of data manipulation, possibility of data integration with Power Query or VBA, and data visualization capabilities (graphs). In this article, we walk through how to setup a simple Excel interface to work with SQL Server data.

SQL Server Setup

Let’s create an example application to control a car’s fuel efficiency to show how Excel can function as a front end to an SQL Server database.

Automobile Data Table

CREATE TABLE [dbo].[Automobile](
   [EventId] [int] IDENTITY(1,1) NOT NULL,
   [AutomobileId] [nvarchar](50) NULL,
   [Day] [date] NOT NULL,
   [Place] [nvarchar](50) NULL,
   [Amount] [money] NULL,
   [Price] [money] NULL,
   [Odometer] [int] NULL,
   [Comments] [nvarchar](50) NULL,
   [Mileage] [int] NULL,
   [Consumption] [money] NULL,
   [FuelEfficiency]  AS ([Mileage]/[Consumption]),
   [ModifiedDate] [datetime] DEFAULT GETDATE(),
   [ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Automobile] PRIMARY KEY CLUSTERED 
(
   [EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Automobile Data Log Table

CREATE TABLE [dbo].[AutomobileLog](
   [ControlItem] [int] IDENTITY(1,1) NOT NULL,
   [EventId] [int] NULL,
   [AutomobileId] [nvarchar](50) NULL,
   [Day] [date] NOT NULL,
   [Place] [nvarchar](50) NULL,
   [Amount] [money] NULL,
   [Price] [money] NULL,
   [Odometer] [int] NULL,
   [Comments] [nvarchar](50) NULL,
   [Mileage] [int] NULL,
   [Consumption] [money] NULL,
   [FuelEfficiency] [money] NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_AutomobileLog] PRIMARY KEY CLUSTERED 
(
   [ControlItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The Stored Procedure

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20240719
-- Description: Automobile Follow-up
-- =============================================
CREATE PROCEDURE [dbo].[uspAutomobile] 
         (@EventId int
         ,@AutomobileId nvarchar(50)
         ,@Day date
         ,@Place nvarchar(50)
         ,@Amount money
         ,@Price money
         ,@Odometer int
         ,@Comments nvarchar(50)
         ,@ModifiedBy nvarchar(50))
WITH EXECUTE AS CALLER
AS
BEGIN
   SET NOCOUNT ON;
   BEGIN TRY
      BEGIN TRANSACTION;
 
         DECLARE @ModifiedDate datetime = GETDATE();
 
         IF LEN(@Place) = 0
            SET @Place = NULL;
 
         IF LEN(@Comments) = 0
            SET @Comments = NULL;
 
         -- UPDATE
         IF @EventId > 0
            BEGIN
               UPDATE [dbo].[Automobile]
                  SET [AutomobileId] = @AutomobileId
                     ,[Day] = @Day
                     ,[Place] = @Place
                     ,[Amount] = @Amount
                     ,[Price] = @Price
                     ,[Odometer] = @Odometer
                     ,[Comments] = @Comments
                     ,[ModifiedDate] = @ModifiedDate
                     ,[ModifiedBy] = @ModifiedBy
                  WHERE [EventId] = @EventId; 
            END
 
         -- INSERT
         IF @EventId = 0
            BEGIN
               INSERT INTO [dbo].[Automobile]
                     ([AutomobileId]
                     ,[Day]
                     ,[Place]
                     ,[Amount]
                     ,[Price]
                     ,[Odometer]
                     ,[Comments]
                     ,[ModifiedDate]
                     ,[ModifiedBy])
                  VALUES (@AutomobileId
                     ,@Day
                     ,@Place
                     ,@Amount
                     ,@Price
                     ,@Odometer
                     ,@Comments
                     ,@ModifiedDate
                     ,@ModifiedBy);
 
               SET @EventId = SCOPE_IDENTITY();
            END
 
         IF @EventId >= 0
            BEGIN;
               WITH CTEAUT (ev,diff) AS
                  (SELECT [EventId]
                        ,CASE 
                           WHEN LAG([Automobile].[Odometer]) OVER (ORDER BY [Automobile].[AutomobileId], [Automobile].[EventId]) IS NULL 
                           THEN 0
                           ELSE [Automobile].[Odometer] - LAG([Automobile].[Odometer]) OVER (ORDER BY [Automobile].[AutomobileId], [Automobile].[EventId])
                         END
                     FROM [dbo].[Automobile]
                     WHERE [AutomobileId] = @AutomobileId)
                  UPDATE  [dbo].[Automobile] 
                     SET  [Mileage] = x.diff
                        ,[Consumption] = [Amount] / [Price]
                     FROM CTEAUT x
                     WHERE [EventId] = x.ev;
            END
 
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
 
         INSERT INTO [dbo].[AutomobileLog]
               ([EventId]
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy])
         SELECT @EventId
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy]
            FROM [dbo].[Automobile]
            WHERE [EventId] = ABS(@EventId);
 
         -- DELETE
         IF @EventId < 0
            BEGIN
               DELETE FROM [dbo].[Automobile]
                  WHERE [EventId] = ABS(@EventId); 
            END;
 
      COMMIT TRANSACTION;
 
      SELECT [EventId]
               ,[AutomobileId]
               ,[Day]
               ,[Place]
               ,[Amount]
               ,[Price]
               ,[Odometer]
               ,[Comments]
               ,[Mileage]
               ,[Consumption]
               ,[FuelEfficiency]
               ,[ModifiedDate]
               ,[ModifiedBy]
         FROM [dbo].[Automobile]
         ORDER BY [AutomobileId]
               ,[EventId] DESC;
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         BEGIN
            ROLLBACK TRANSACTION;
         END
         
      -- Print error information. 
      PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State: ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line: ' + CONVERT(varchar(5), ERROR_LINE()) + 
          ', User: ' + CONVERT(varchar(5), @ModifiedBy);
 
      PRINT ERROR_MESSAGE();
   END CATCH;
END
GO

Excel Setup

Create a new Excel spreadsheet named Automobile.xlsm on your computer. Click File > Options > Customize Ribbon > Developer to allow VBA buttons on the sheet.

Developer option on Ribbon visible

Rename the sheet tab to “Automobile” and fill the cells with the following:

Rename the sheet and include text cells

Working with VBA

In Excel, press ALT-F11 to open the VBA panel, right-click on Microsoft Excel Objects, and choose Insert > Module.

Inserting a VBA module

Click Tools > References, look for Microsoft ActiveX Data Objects X.X Library, and click OK.

Reference to ADO Library

Click on Module1. Insert the code below and modify the DatabaseName and ComputerName with the respective names of the computer running SQL Server and the database being used. For numeric values, I am forced to only accept dot as a decimal separator.

Public Sub AutoInsEvent()
    If Len(Cells(2, 2)) = 0 Then Exit Sub
    
    Dim conex As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
  
    conex.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=***DatabaseName***;Data Source=***ComputerName***"
        sql = "EXECUTE [dbo].[uspAutomobile] " & _
            Cells(2, 2) & ",'" & _
            Cells(3, 2) & "','" & _
            Format(Cells(4, 2), "yyyy/MM/dd") & "','" & _
            Cells(5, 2) & "'," & _
            Replace(Cells(6, 2), ",", ".") & "," & _
            Replace(Cells(7, 2), ",", ".") & "," & _
            Cells(8, 2) & ",'" & _
            Cells(9, 2) & "','" & _
            Cells(10, 2) & "';"
        rs.Open sql, conex, adOpenKeyset, adLockOptimistic
    conex.Close
    Range("B2:B10").ClearContents
 
    Range("E2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    For i = 2 To 10
        If Cells(i, 3) > 0 Then
            Cells(i, 2) = Cells(i, 3)
        End If
    Next i
    
    Columns("E:Z").Select
    Columns("E:Z").EntireColumn.AutoFit
    Range("B2").Select
End Sub

Go to the Developer tab, choose Insert > Form Control > Button, and click on the desired cell where you want to place the button and associate it to the macro above. After that, you can change the name of the button to “GO” or whatever you want. To change the name of a button, right-click over the button and choose Edit Text.

Inserting a button in Automobile sheet

Now, it is ready to enter some data, which I did for some items.

Entering some data

Including a Table to View the Data Entered

Since it is always good to see what is happening, let’s include a query to show the data in our database. Go to Data > Get Data > From Database > From SQL Server Database.

Data from Database

Fill out the server name and the database name and use the following SQL statement: “SELECT * FROM [dbo].[Automobile]”.

SQL Server details

Provide your credentials.

SQL Server credentials

Update the data visualization based on your needs and click Load.

Preload of data

This will insert a new sheet called QUERY.

Move this table generated from the QUERY sheet to the AUTOMOBILE sheet at cell E1.

Excel after entering some data

Observe that I entered July 8th twice. To delete it, select the data, copy item 9 from columns EVENTID to COMMENTS, paste it using transpose into cell B2, change EVENTID 9 to -9, and press GO. Negative numbers are a command to exclude the item.

Also, if you want to change an Item, copy and transpose it to change the value. I will do so for item 7, and press GO. This repeated task of transposing can also be done using VBA to automate this process.

Removing duplicate values entered

If you want to know who did what and when, repeat the process of Including a table to view the data entered above but change the SQL STATEMENT to “SELECT * FROM [DBO].[AutomobileLog] ORDER BY ABS([EventId]),[ControlItem]”.

Excel table showing the LOG

Many improvements can be implemented, but this is enough to show how to interact with SQL Server using Excel.

Next Steps

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide