如何编写从SQL数据库返回值的Excel函数？ [英] How to write an Excel function which returns a value from an SQL database?

问题描述

  = GetRecField（Foo Record Key ，FooField1）
  

...将通过ODBC内部连接到SQL数据库，执行有一个

  SELECT FooField1 FROM MyTable WHERE KEY_FIELD ='Foo Record Key'; 
  

，并将返回结果值作为GetRecField函数的结果。上述SQL被授予仅返回一个记录（IOW KEY_FIELD具有唯一约束）。

当然，上述功能可以在工作表中多次调用，请尝试避免盲目的 QueryTables.Add

TIA。

解决方案

您可以编写一个自定义函数来执行

1. 打开VBA编辑器ALT-F11）


2. 打开工具 - >引用，并确保选中Microsoft ActiveX数据对象2.8库和Microsoft ActiveX数据对象记录集2.8库


3. 右键单击VBAProject，然后选择插入 - >模块


4. 打开模块。现在您可以创建一个自定义函数，如：

 
公共函数GetItem（字段As String，id As Integer）As String 
 Set oConnection = New ADODB.Connection 
 Dim oRecordset As ADOR.Recordset 
 oConnection.Openprovider = sqloledb; data source = yourserver; &_ 
Trusted_Connection = yes; initial catalog = yourdatabase; 
设置oRecordset = oConnection.Execute（&_ 
从表中选择&field&，其中id =&id）
如果oRecordset.EOF然后
 GetItem =n / a
 Else 
 GetItem = oRecordset（field）
如果
结束函数
 

<您现在可以从单元格中调用该函数：

= GetItem（fieldname; 2 ）

该模块是必需的，因为无法从spreadhseet内部调用非模块功能。 >

I want to write the following function which should be used in an Excel worksheet:

=GetRecField("Foo Record Key", "FooField1")

...which will connect internally through ODBC to an SQL database, execute there an

SELECT FooField1 FROM MyTable WHERE KEY_FIELD='Foo Record Key';

and will return the resulting value as the result of the function GetRecField. The above SQL is granted to return only one record (IOW KEY_FIELD has an unique constraint).

Of course, the above function can be called multiple times in a worksheet so, please try to avoid a blind QueryTables.Add

TIA.

解决方案

You can write a custom function to do that

1. Open the VBA editor (ALT-F11)
2. Open Tools -> References, and make sure the "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ActiveX Data Objects Recordset 2.8 Library" are selected
3. Right click VBAProject, and choose Insert -> Module
4. Open the module. Now you can create a custom function, like:

    Public Function GetItem(field As String, id As Integer) As String
        Set oConnection = New ADODB.Connection
        Dim oRecordset As ADOR.Recordset
        oConnection.Open "provider=sqloledb;data source=yourserver;" & _
            "Trusted_Connection=yes;initial catalog=yourdatabase;"
        Set oRecordset = oConnection.Execute( & _
            "select " & field & " from table where id = " & id)
        If oRecordset.EOF Then
            GetItem = "n/a"
        Else
            GetItem = oRecordset(field)
        End If
    End Function

5. You can now call the function from a cell:

   =GetItem("fieldname";2)

The module is required because non-module functions can't be called from inside the spreadhseet.

这篇关于如何编写从SQL数据库返回值的Excel函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！