变量未IN子句

Question

此代码是用Excel2010 VBA内部组件和查询，PostgreSQL表

我在VBA下面的代码创建，我想在我的SQL查询中使用一个变量，但我不能让使用IN子句接受VBA变量的SQL查询

此代码创建我想要使用的变量并且正常工作。这使我可以选择特定的细胞，我需要查询SelectedID = 6,7,8,6452

我现在想要将它添加到使用IN子句我的查询，但它的

Dim StaffID As Range 
Dim ID As Range 
Dim LR As Long 
Dim SelectedID As String 

'Count number of rows to search 
LR = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 
On Error Resume Next 'if only 1 row 

'Store Data from here 
Set StaffID = ThisWorkbook.Sheets("Sheet1").Range("B2:B" & LR) 

'Loop through each cell in Range and look for any character in column A 
'then store offset cell value using comma delimiter 
For Each ID In Worksheets("Sheet1").Range("A2:A" & LR).Cells 
    If ID.Value > 0 Then 
     SelectedID = SelectedID & "," & ID.Offset(, 1).Value 
    End If 
Next ID 

'Remove first delimiter from string (,) 
SelectedID = Right(SelectedID, Len(SelectedID) - 1) 

输出例只是不起作用。有没有人有解决方案或解决方法。

Sheets("Sheet2").Select 
Range("A1").Select 
Dim rs As Recordset 

Dim t As String 
t = "SELECT DISTINCT s.entity_id, u.login_name, s.role " _ 
      & "FROM staff s INNER JOIN user u ON s.entity_id=u.staff_id " _ 
      & "WHERE u.staff_id IN (SelectedID) " _ 

Set rs = conn.Execute(t) 
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1")) 
    .Refresh 
End With 

rs.Close 

Answer 1

此时您将字符串'SelectedID'直接放入查询中。你需要像这样在您的VBA：

t = "SELECT DISTINCT s.entity_id, u.login_name, s.role " _ 
      & "FROM staff s INNER JOIN user u ON s.entity_id=u.staff_id " _ 
      & "WHERE u.staff_id IN (" & SelectedID & ")" 

Answer 2

Rows.Count应该是完全合格的With ThisWorkbook.Worksheets("Sheet1")充分排位赛将让你的代码读取更好。

Function getSelectedIDSQL() As String 
    Dim ID As Range, StaffID As Range 
    Dim SelectedID As String 

    With ThisWorkbook.Worksheets("Sheet1") 
     For Each ID In .Range("A2" & .Range("A" & .Rows.Count).End(xlUp)) 
      If ID.Value > 0 Then SelectedID = SelectedID & "," & ID.Offset(, 1).Value 
     Next 
    End With 

    If Len(SelectedID) Then getSelectedIDSQL = Right(SelectedID, Len(SelectedID) - 1) 

End Function