从Excel中使用的SQL查询返回Excel表达式 [英] Returning an Excel expression from a SQL query used in Excel

问题描述

我有一个Excel电子表格，其数据是使用查询从SQL Server数据库中加载的.查询很复杂，但这是一个简化:

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

查询将返回如下行:

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

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

这样做.

然后我希望excel显示的值是在指定列上运行函数 SOME_EXCEL_FUNCTION 的结果，例如对于第四行，Excel电子表格中的第二列应显示 SOME_EXCEL_FUNCTION(guid_4)

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)

但事实并非如此.而是显示文本 = SOME_EXCEL_FUNCTION(A4).

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

我该如何使Excel轻推以 = 开头的SQL返回的字符串作为表达式而不是值，然后计算它们?

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?

(注意，我故意选择了一个虚拟的Excel函数 SOME_EXCEL_FUNCTION 以突出显示这是与这个，即我无法按照建议的此处.在SQL查询中复制Excel函数.

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

推荐答案

如果您的数据在表中，请将此单行添加到您的vba中.

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

[TableName].Value = [TableName].Value 

这篇关于从Excel中使用的SQL查询返回Excel表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！