从 VBA 调用 SQL 时的动态 WHERE 条件

发布于 2024-12-12 02:09:05 字数 724 浏览 0 评论 0原文

在 Excel 中，我有一列 ID。我想根据 ID 列中的值从表中选择值。到目前为止，我已经得到了这个，其中 ws.Cells(i,10) 指的是 K 列中每行的值。

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=xxxx;Description=xxxxx;UID=xxx;APP=2007 Microsoft Office system;WSID=xxx" _
    ), Array("56;DATABASE=xxx;Trusted_Connection=Yes")), Destination:=Range( _
    "$M$3")).QueryTable        

    For i = 1 To 691
    sText = "SELECT Instrument.ID, Instrument.MATURITY FROM xxxx.dbo.Instrument Instrument WHERE Instrument.ID=" & ws.Cells(i, 10).Value & ""
    .CommandText = Array(sText)
    Next

End With

我在 sText 行上收到错误“424”，错误消息“需要对象”。

我也有预感这不是最好的方法。必须有一个更简单的设置来运行具有列中 WHERE 条件的 SQL。

感谢您的帮助！

原文

In Excel I have a column of IDs. I would like to SELECT values from a table conditional on the values in the ID column. So far I've got this, where ws.Cells(i,10) refers to the values of each of the rows in column K.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=xxxx;Description=xxxxx;UID=xxx;APP=2007 Microsoft Office system;WSID=xxx" _
    ), Array("56;DATABASE=xxx;Trusted_Connection=Yes")), Destination:=Range( _
    "$M$3")).QueryTable        

    For i = 1 To 691
    sText = "SELECT Instrument.ID, Instrument.MATURITY FROM xxxx.dbo.Instrument Instrument WHERE Instrument.ID=" & ws.Cells(i, 10).Value & ""
    .CommandText = Array(sText)
    Next

End With

I get an error "424" on the row sText, error message "Object required".

I also have a hunch this is not the best way to do this. There has to be a simpler setup to run a SQL with WHERE conditions from a column.

Thanks for any help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花开半夏魅人心 2024-12-19 02:09:05

我几乎肯定 iDevlop 是正确的 - 您尚未定义变量 ws，这会导致“需要对象”错误消息。

无论如何，您可能更喜欢这个解决方案：

Dim IDs as String
Dim ws As Worksheet
Set ws = ActiveSheet
IDs = Join(WorksheetFunction.Transpose( _
           ws.Range(ws.Cells(1, 10), ws.Cells(691, 10))), ",")
.CommandText = "SELECT ... FROM ... WHERE Instrument.ID in (" + IDs + ")"

不需要循环。

I'm almost positive that iDevlop is correct - you haven't defined your variable ws, which results in the "Object Required" error message.

In any event, you might like this solution better:

Dim IDs as String
Dim ws As Worksheet
Set ws = ActiveSheet
IDs = Join(WorksheetFunction.Transpose( _
           ws.Range(ws.Cells(1, 10), ws.Cells(691, 10))), ",")
.CommandText = "SELECT ... FROM ... WHERE Instrument.ID in (" + IDs + ")"

No loop necessary.

回复收藏 0 原文

~没有更多了~