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

Try using TextToColumns on the cells with the formula from SQL — Dawid SA Tokyo
– Dawid SA Tokyo, Commented Jul 11, 2016 at 11:51
I cannot see how to apply that - would you explain further please — dumbledad
– dumbledad, Commented Jul 11, 2016 at 12:04
After the data is loaded to the excel you could run a macro to change the cells cells with the formula from SQL. I assume that you are using VBA to get the data from SQL correct? — Dawid SA Tokyo
– Dawid SA Tokyo, Commented Jul 11, 2016 at 12:13
Nope, using the connection functionality in the data tab, like the start of this — dumbledad
– dumbledad, Commented Jul 11, 2016 at 14:17

SeanC · Accepted Answer · 2016-07-11 15:53:52Z

1

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

There are 2 options:

(Absurdly long) edit each cell, and press enter to get it to recalculate
(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.

answered Jul 11, 2016 at 15:53

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dawid SA Tokyo · Accepted Answer · 2016-07-12 05:59:57Z

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.

GollyJer · Accepted Answer · 2020-02-28 03:29:47Z

1

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

[TableName].Value = [TableName].Value

answered Feb 28, 2020 at 3:29

GollyJer

27.2k18 gold badges126 silver badges190 bronze badges

1 Comment

dumbledad Over a year ago

There's no VBA, as pointed out in the last comment on the question. using the connection functionality in the data tab, like the start of this

Bee_Riii · Accepted Answer · 2021-04-09 07:49:53Z

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

Collectives™ on Stack Overflow

Returning an Excel expression from a SQL query used in Excel

4 Answers 4

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Linked

Related