VBA SQL WHERE IN子句无数据返回及数组赋值异常求助

核心问题分析 #

1. 数组范围计算错误 #

你当前计算P列最后一行的代码Lastrow = Cells(Rows.Count, "P").End(xlUp).Row存在隐患：Cells未指定所属工作表，会默认使用当前激活的工作表，而非目标的Sheet9。

比如当你激活的是A列有500行的工作表时，该表的P列可能仅存在表头（第1行），此时Lastrow会被计算为1，导致Range("P2:P" & Lastrow)自动反转成P1:P2，最终数组里只包含P1和P2的值，而非你预期的Sheet9中P2到P5的数据。

2. SQL IN条件拼接错误 #

通过Range.Value赋值得到的是二维数组（即使是单列，格式为myRange(行索引, 1)），而Join函数仅支持一维数组。直接使用Join(myRange, """, """)会生成不符合SQL语法的字符串，导致WHERE条件无法匹配任何数据。

修正后的完整代码 #

Sub grabdata()
    Dim qt As QueryTable
    Dim qName As String
    Dim connString As String, sSql As String, Filepath As String, sourceshname As String
    Dim targetsh As Worksheet
    
    qName = "PowerQueryData"
    Filepath = Sheets("Sheet9").Range("C18").Value
    sourceshname = "Sheet1"
    
    Set targetsh = ThisWorkbook.Worksheets("TEST")
    
    Dim myRange As Variant
    Dim Lastrow As Long
    Dim arrOneDim() As String
    Dim i As Long
    
    ' 修正：指定Sheet9计算P列最后一行
    With Sheets("Sheet9")
        Lastrow = .Cells(.Rows.Count, "P").End(xlUp).Row
        ' 仅当P2及以下有数据时才继续
        If Lastrow < 2 Then
            MsgBox "P列无有效数据"
            Exit Sub
        End If
        myRange = .Range("P2:P" & Lastrow).Value
    End With
    
    ' 把二维数组转成一维字符串数组
    ReDim arrOneDim(1 To UBound(myRange))
    For i = 1 To UBound(myRange)
        arrOneDim(i) = CStr(myRange(i, 1))
    Next i
    
    If Filepath <> "" Then
        ' 用一维数组拼接IN条件
        sSql = "SELECT * FROM [" & sourceshname & "$] WHERE [something] IN ('" & Join(arrOneDim, "', '") & "')"
        Debug.Print sSql ' 可查看生成的SQL语句是否正确
        
        connString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filepath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"""
        Set qt = targetsh.QueryTables.Add(Connection:=connString, Destination:=targetsh.Range("A1"), Sql:=sSql)
        With qt
            .Name = qName
            .RefreshStyle = xlOverwriteCells
            .Refresh BackgroundQuery:=False
            .MaintainConnection = False
            .AdjustColumnWidth = False
            .Delete
        End With
    End If
End Sub

额外验证步骤 #

1. 运行代码前先查看Debug.Print sSql输出的SQL语句，确认IN括号内的数值是否和P列数据一致
2. 若仍无数据返回，检查目标工作簿中[something]列的数据类型是否与P列一致（比如文本/数字不匹配）

内容的提问来源于stack exchange，提问作者JeffCh