I am currently working on a Web UI: https://tickett.wordpress.com/2014/12/11/excel-like-web-frontend-to-sql-viewtablestored-procedure/
We often have a need to view/manipulate/analyse data from systems in Excel (because, let’s face it- it’s quick & easy). However, it is sometimes the case that we need to add data (it may be commentary, budget figures or any manner of data that for one reason or another cannot be keyed into the underlying system(s)). I hoped Excel’s built in functionality could cater for this… Unfortunately, while it’s very easy to set a datasource to pull data from SQL it doesn’t appear to have any functionality for pushing data back into the underlying database.
So this is what I came up with…
Step 1: The data is retrieved using VBA (I have a master sheet which is pre-formatted). The “template sheet” is then copied and used as a starting point each time the data is retrieved:
Sub RetreiveSheet() On Error GoTo Error Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _ "Initial Catalog=EBMS1920;Data Source=sql-01" stSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE CRITERIA = 1" stSQL = stSQL & vbCrLf & "ORDER BY [Date] ASC" Set cnt = New ADODB.Connection With cnt .CursorLocation = adUseClient .Open stADO .CommandTimeout = 0 Set rst = .Execute(stSQL) End With Application.EnableEvents = False For i = 0 To rst.Fields.Count - 1 ActiveSheet.Cells(1, i + 1) = rst.Fields(i).Name Next i Application.ScreenUpdating = False For r = 1 To rst.RecordCount For i = 0 To rst.Fields.Count - 1 ActiveSheet.Cells(r + 1, i + 1) = rst.Fields(i).Value Next i rst.MoveNext Next r Application.ScreenUpdating = True rst.Close cnt.Close Set rst = Nothing Set cnt = Nothing ActiveSheet.PivotTables(1).RefreshTable ActiveSheet.Range("B1000").Value = "Last Retreived: " & DateTime.Now() Application.EnableEvents = True ActiveSheet.Protect UserInterfaceOnly:=True Exit Sub Error: MsgBox ("An error occurred") End Sub
Here is an example with some made up data:
The gray cells indicate cells that are pulling from the system and CANNOT be updated. The pink cells are pulling from my custom table and CAN be updated, and the yellow cells are summary fields (a pivottable). All cells are locked except those in pink and the sheet is protected.
The SQL query is pulling from FORECAST_SPREADSHEET which is a view joining tables from the underlying system with my custom table FORECAST_DATA.
Now the clever bit… allowing updates to write back to the database!
Step 2: We create a trigger on our view:
ALTER TRIGGER [dbo].[tr_UPDATE_FORECAST] ON [dbo].[FORECAST_SPREADSHEET] INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON; DELETE A FROM FORECAST_DATA A INNER JOIN INSERTED B ON B.ORG_CODE = A.ORG_CODE AND B.ID = A.EVENT_ID INSERT INTO FORECAST_DATA SELECT ORG_CODE ,ID ,ADD_SERVICE_INCOME ,TOTAL_CATERING_EXPENSES FROM INSERTED END
Step 3: We add a routine to the “worksheet_change” event:
Private Sub Worksheet_Change(ByVal Target As Range) If Application.ScreenUpdating = False Then Exit Sub If Target.Value <> "" And IsNumeric(Target.Value) = False Then Target.Value = "" MsgBox ("Value must be numeric!") Exit Sub End If Dim updateSQL As String Dim orgCode As String Dim eventID As String Dim columnName As String Dim newValue As String orgCode = Range("A" & Target.Row).Value If orgCode = "" Then Exit Sub eventID = Range("D" & Target.Row).Value columnName = Cells("1", Target.Column).Value newValue = IIf(Target.Value = "", "NULL", Target.Value) updateSQL = "UPDATE FORECAST_SPREADSHEET SET " & columnName & " = " & newValue & " WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _ "Initial Catalog=EBMS1920;Data Source=sql-01" Set cnt = New ADODB.Connection With cnt .CursorLocation = adUseClient .Open stADO .CommandTimeout = 0 Set rst = .Execute(updateSQL) End With selectSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID Set rst = cnt.Execute(selectSQL) Application.EnableEvents = False For i = 0 To rst.Fields.Count - 1 ActiveSheet.Cells(Target.Row, i + 1) = rst.Fields(i).Value Next i Application.EnableEvents = True cnt.Close Set rst = Nothing Set cnt = Nothing ActiveSheet.PivotTables(1).RefreshTable End Sub
You will notice in the code used to retrieve the data earlier that I set Application.EnableEvents = False to prevent this event being fired when my code is executing (I only want it to fire when the user is keying in data).
We’re done! RetreiveSheet() can be executed to retrieve the data and keying into the pink cells will update the custom SQL table in real-time! I chose to retrieve the latest copy of an entire row everytime an update is committed to the database.
Sidenote: One trick I learnt whilst building this solution was how to avoid cell formula references being automatically updated (broken) when the sheet to which they refer is no longer valid (deleted). We do this using the INDIRECT() function. So =SUM(’26_201109′!covers) becomes =SUM(INDIRECT(“’26_201109′!covers”))