Tag Archive: Excel


Further to a post over 3 years ago https://tickett.wordpress.com/2011/09/27/updateable-excel-frontend-for-sql-view/ – I have finally given up the search for a ready made solution and built something myself.

I work with a number of systems which have functionality gaps and/or need a simpler (quicker) interface for data entry. The solution is essentially a SQL view (or stored procedure) with a simple grid (excel like) front-end. If any data needs to be captured not currently handled by the system a custom table is created to hold the fields.

My previous Excel based solution works rather well, but is starting to show it’s age. I am now beta testing a web based solution i have built;

Screen Shot 2014-12-11 at 20.34.43

The application is extremely simple to configure- enter the SQL to retrieve & update the data;

Screen Shot 2014-12-11 at 20.43.43

And list the columns with some attributes (ReadOnly, Hidden, Title, Width etc);

Screen Shot 2014-12-11 at 20.42.10

And you’re all set!

Functionality currently includes;

  • Easy configuration
  • Simple/fast data entry (with validation)
  • Column resizing (non-persistent)
  • Sort on any column

Next steps;

  • Test (find and fix bugs)
  • Optimise code
  • Allow parameters in select SQL

More problems

Until a few months back I was a member of Experts-Exchange. I’ve found the site a great tool for those little problems you know countless others have encountered and could solve for me in a matter of seconds…. Anyway- this year I was thinking twice about renewing when I re-discovered Stack Exchange a very similar tool but this time at no cost. Stack Exchange has been invaluable in assisting me with a number of recent problems…

SUMIF with multiple criteria in Excel 2003

What I wanted to do seemed relatively straightforward and google threw up a lot of results but the majority of recommended solutions were for Excel 2007 and above. The remaining solutions simply didn’t seem to work?

Here’s the question/answer on Stack Overflow

In the below screenshot I was trying to sum all income for org_code (row 18) and month (column A).

It turned out I was making a few fundamental mistakes:

  • org_code is being held as a string in data table (the left-hand alignment helped Jesse spot this and point me in the right direction!
  • none of the possible solutions seem to like named ranges or evaluating entire columns (A1:A1000 works fine but A:A does not)

I’d like to find some more time to look at this (though unlikely I will). I should also have been able to use SUM and IF in an array formula too.

The working solution looks "something like"

=SUMPRODUCT(D1:D8,--(A1:A8=OrgRef),--(C1:C8=MonthRef))
Thanks Jesse!

ESXi problems

1) ESXi 5.0 relapsing to an old configuration on reboot

I’m still unsure what caused this but I did find that volumes weren’t mounting as expecting during the hypervisor boot process. Choosing recovery mode (shift-r) option during boot successfully solved the issue however.

2) Unable to power on ESXi 5.0 host from vSphere

Still a bit of a puzzle but the primary reason appears to’ve been that the host had to not only be managed to vCenter, but also had to be part of a "cluster". Once I created a cluster and moved the host into it I was able to place the physical machine into standby mode. Unfortunately, I still don’t appear to be able to power the host from an off state. If I make any further developments I will try and update this post.

Cat5e cable with cat6 connectors

There appears to be a lot of contradicting information online about the back-ward compatibility of cat6 connectors but from this experience I would have to rule that cat6 connectors are NOT backward compatible with cat5e cable. Question on Server Fault.

Let’s hope I continue to gain a lot from these sites!

L

Updateable Excel Frontend for SQL View

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”))

L

%d bloggers like this: