VBA 中的 SQL 命令

Question

Sub querystringforbiz_pro()

' Defining the object type
Dim data As ADODB.Connection
Dim datarecordset As ADODB.Recordset
Dim header As ADODB.Field


Workbooks("account").Activate
' initiating the new instance of the cinnection
Set data = New ADODB.Connection
Set datarecordset = New ADODB.Recordset

' definig the connection string
 data.ConnectionString = "xxxxxxx"

 biz = Sheets("MID").Range("A2", Range("A2").End(xlDown)).value

' Activating the connection
data.Open

' Sepcification for the recordset
bizquery = "Select m.id, m.company_name, m.url From payu.merchant as m where m.id in (" & biz & ")"

With datarecordset
    .ActiveConnection = data
    .Source = bizquery
    .LockType = adLockReadOnly
    .CursorType = adOpenForwardOnly
    .Open
End With

Workbooks("UpdatingthenewMIDs").Activate
Worksheets.Add

Range("A1").Activate

For Each header In datarecordset.Fields
    ActiveCell.Value = header.Name
    ActiveCell.Offset(0, 1).Select
Next header


Range("A2").CopyFromRecordset datarecordset

datarecordset.Close
data.Close

所以我想一次性传递整个 ID 列表，并根据这些 ID 查找名称。

当我复制变量“BIZ”中的数据并在查询中使用它时，它会抛出错误“类型不匹配”

请建议我如何在查询中一次性传递整个 ID 列表(不想使用 for 循环)

Answer 1

做这样的事情:

...
biz = Sheets("MID").Range("A2", Range("A2").End(xlDown)).Value
Dim ValueList As String
ValueList = ""
For Each cell In biz
    If ValueList <> "" Then
        ValueList = ValueList & ", "
    End If
    ValueList = ValueList & cell
Next cell
...
bizquery = "Select m.id, m.company_name, m.url From payu.merchant as m where m.id in (" & ValueList & ")"
...