By: Sebastiao Pereira | Updated: 2024-08-12 | Comments (6) | Related: > Microsoft Excel Integration
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.
Create a Table for the Automobile Data
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
Create a Table for the Automobile Data Log
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
Create 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.
Rename the sheet tab to "Automobile" and fill the cells with the following:
Working with VBA
In Excel, press ALT-F11 to open the VBA panel, right-click on Microsoft Excel Objects, and choose Insert > Module.
Click Tools > References, look for Microsoft ActiveX Data Objects X.X Library, and click OK.
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.
Now, it is ready to enter some data, which I did for some items.
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.
Fill out the server name and the database name and use the following SQL statement: "SELECT * FROM [dbo].[Automobile]".
Provide your credentials.
Update the data visualization based on your needs and click Load.
This will insert a new sheet called QUERY.
Move this table generated from the QUERY sheet to the AUTOMOBILE sheet at cell E1.
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.
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]".
Many improvements can be implemented, but this is enough to show how to interact with SQL Server using Excel.
Next Steps
- If you need more information about ADO objects, please visit ADO Fundamentals.
- I wrote an article that explains the use of log for change tracking in a SQL store procedure: Single SQL Stored Procedure for all CRUD Operations and Change Tracking
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-08-12