Returning an Excel expression from a SQL query used in Excel

Question

I have an Excel spreadsheet whose data is loaded from a SQL Server database using a query. The query is complicated but this is a simplicifation:

SELECT
    Collections.id AS collectionId,
    '=SOME_EXCEL_FUNCTION(A' + CAST(ROW_NUMBER() OVER(ORDER BY pagingId DESC) AS VARCHAR) + ')' AS computedResult
FROM Collections

The query would then return rows like this:

guid_1, =SOME_EXCEL_FUNCTION(A1)
guid_2, =SOME_EXCEL_FUNCTION(A2)
guid_3, =SOME_EXCEL_FUNCTION(A3)
guid_4, =SOME_EXCEL_FUNCTION(A4)
...

Which it does.

Then I want the values displayed by excel to be the result of running the function SOME_EXCEL_FUNCTION on the specified column, e.g. for that forth row the second column in the Excel spreadsheet should show the value resulting from SOME_EXCEL_FUNCTION(guid_4)

But it doesn't. Instead it shows the text =SOME_EXCEL_FUNCTION(A4).

How do I nudge Excel into treating the strings that are returned by SQL that start with = as expressions and not values and thus calculating them?

(N.B. I have deliberately chosen a fictitious Excel function SOME_EXCEL_FUNCTION to highlight that this is a different question from this one, i.e. I cannot replicate the Excel function within the SQL query as suggested here.)

Answer 1

I used to deal with data pasted/imported into excel like this often. Years ago I wrote the following VBA and put it in my personal macro workbook and then bound it to a key. You can select a range of cells and run this and it will pop up with different conversion options. In this scenario I would just use the trim option (5). There are some other useful options here. I figured I'd share the code for anyone else that might find it handy.

Sub trimmer() '
' trimmer Macro
'
' Keyboard Shortcut: Ctrl+t
'
    Dim rSel As Range
    Set rSel = Selection
    Dim c As Range
    Dim vCalc As Variant
    vCalc = Application.Calculation
    Dim strV
    Dim intConv As Integer
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    intConv = Application.InputBox("1. Date - 2. Currency - 3. Decimal - 4. long" & Chr(13) & _
                                   "5. Don't convert just trim values" & Chr(13) & _
                                   "6. Convert international (yyyymmdd) dates to normal dates" & Chr(13) & _
                                   "7. Double - 8. SQL Time" & Chr(13) _
                                   , , , , , , , 1)


    Set rSel = NonEmptyCells(rSel)
    If rSel.Cells.Count > 5000 Then
        If MsgBox("You have selected a large number of cells, this may take some time, do you want to continue?", vbOKCancel) = vbCancel Then
            Exit Sub
        End If
    End If


    Select Case intConv
    Case 1
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CDate(Trim(c.Value))
                c.NumberFormat = "dd-mmm-yyyy"
            End If
        Next c
    Case 2
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CCur(Trim(c.Value))
            End If
        Next c
    Case 3
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CDec(Trim(c.Value))
            End If
        Next c
    Case 4
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CLng(Trim(c.Value))
            End If
        Next c
    Case 5
        For Each c In rSel


            If Trim(c.Value) = "" Then c.Value = ""
            If c.Value <> "" Then
                strV = Trim(c.Value)
                While Asc(Left(strV, 1)) = 127 Or Asc(Left(strV, 1)) = 129 Or Asc(Left(strV, 1)) = 141 Or Asc(Left(strV, 1)) = 143 Or Asc(Left(strV, 1)) = 144 Or Asc(Left(strV, 1)) = 157 Or Asc(Left(strV, 1)) = 160 Or Asc(Left(strV, 1)) = 10 Or Asc(Left(strV, 1)) = 13
                    strV = Right(strV, Len(strV) - 1)
                    If Not strV <> "" Then GoTo skip
                Wend
                While Asc(Right(strV, 1)) = 127 Or Asc(Right(strV, 1)) = 129 Or Asc(Right(strV, 1)) = 141 Or Asc(Right(strV, 1)) = 143 Or Asc(Right(strV, 1)) = 144 Or Asc(Right(strV, 1)) = 157 Or Asc(Right(strV, 1)) = 160 Or Asc(Right(strV, 1)) = 10 Or Asc(Right(strV, 1)) = 13
                    strV = Left(strV, Len(strV) - 1)
                    If Not strV <> "" Then GoTo skip
                Wend
skip:
                c.Value = strV
            End If
        Next c
    Case 6
        '20110131'
        For Each c In rSel
            c.NumberFormat = "General"
            If c.Value <> "" Then
                c.Value = DateValue(Right(c.Value, 2) & "/" & Mid(c.Value, 5, 2) & "/" & Left(c.Value, 4))
            End If
            c.NumberFormat = "dd-mmm-yyyy"
        Next c
    Case 7
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CDbl(Trim(c.Value))
            End If
        Next c
    Case 8
        For Each c In rSel
            If c.Value <> "" Then
                c.Value = CDate(Trim(c.Value))
                c.NumberFormat = "HH:MM:SS"
            End If
        Next c
    Case False
        MsgBox ("you did not select a conversion type")
    End Select
    Application.Calculation = vCalc


    Application.ScreenUpdating = True
    
End Sub


Function NonEmptyCells(TestRange As Range) As Range
Dim r1 As Range
Dim r2 As Range
If Not TestRange.Cells.Count > 1 Then
   Set NonEmptyCells = TestRange
   Exit Function
End If
On Error Resume Next
Set r1 = TestRange.SpecialCells(xlCellTypeFormulas)
Set r2 = TestRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If r1 Is Nothing And Not r2 Is Nothing Then
Set NonEmptyCells = r2
ElseIf r2 Is Nothing And Not r1 Is Nothing Then
Set NonEmptyCells = r1
ElseIf r2 Is Nothing And r1 Is Nothing Then
Set NonEmptyCells = TestRange.Cells(1, 1)
Else
Set NonEmptyCells = Union(r1, r2)


End If


End Function

Answer 2

If your data is in a table add this one-liner to your vba.

[TableName].Value = [TableName].Value 

Answer 3

You could use a simple code to execute on sheet, for example on selection change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     On Error Resume Next
     Columns(3).TextToColumns
     On Error GoTo 0
End Sub

This will allow you to format the data from text to excel formula. Just simply click somewhere on the sheet after the data is loaded from SQL. Remember to change the column number to the column where you data is imported.

Answer 4

Once you have the data loaded, you will have to get excel to re-read the text as a formula.

There are 2 options:

1. (Absurdly long) edit each cell, and press enter to get it to recalculate

2. (Much quicker) search and replace = for =
   Yes, it's the same character, but by changing the cell, it forces excel to reevaluate the cell

Depending on how the data is presented, it may be a one off, or it may need to be repeated every time the data is refreshed.