请老师们给看看这个例子,无法查询出数据,谢谢

YANWEI82123300

请老师们给看看这个例子,无法查询出数据,谢谢
我的例子是跨工作簿查询所需数据
其中A1单元格(有两个参数:PO Number,ITEM),B1单元格为一个参数,想通过这两个参数进行查询,取出结果出来,下面的代码没有报错但是就是不出结果,请看看问题在哪里?谢谢
代码来自论坛里面的一位老师的例子,改了一下
例子:ITEM= VA133276-002    (文本格式)
     PO Number = 4214021036(文本格式)


Sub limonet_New()

'XXXXXXXXXXXXXXXXXXXXXXX
    Dim rng As Range
    Dim P As String
   Dim Q As String
   
   P = Sheet2.Range("A1")
   Q = Sheet2.Range("B1")
   
    Set rng = ActiveSheet.Range("B1") ' 指定要设置背景颜色的单元格
    rng.Interior.ColorIndex = 6 ' 设置背景颜色的索引值，可以根据需要更改
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Range("A3:M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
Dim strSQL As String, CN As Object, Rst As Object
Set CN = CreateObject("ADODB.connection")
    CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;Data Source=" & ThisWorkbook.FullName
  
  'XXXXXXXXXXXXX
     strSQL = "Select [PO Number],[PO Line Number],[Line Type],[Item],[Description],[BUYER],[Closed Code],[Vendor],[Qty],[Qty Recd],[Qty Accept],[Need By],[Promise] From [excel 12.0;Database=" & ThisWorkbook.Path & "\RICE0010-Open PO.xlsx].['RICE0010-Open PO$'] where '" & P & "' = '" & Q & "'   ORDER BY [Need By]"
  
Set Rst = CN.Execute(strSQL)
For i = 1 To Rst.Fields.Count
        Cells(2, i) = Rst.Fields(i - 1).Name   '由第二行开始添加表头字段名
    Next i

Range("A3").CopyFromRecordset Rst



'Range("a3").CopyFromRecordset CN.Execute(strSQL)

  Rst.Close
  CN.Close
  Set CN = Nothing
End Sub

YANWEI82123300

各位老师受累给看看这个问题,谢谢

魂断蓝桥

     strSQL = "Select [PO Number],[PO Line Number],[Line Type],[Item],[Description],[BUYER],[Closed Code],[Vendor],[Qty],[Qty Recd],[Qty Accept],[Need By],[Promise] From [excel 12.0;Database=" & ThisWorkbook.Path & "\RICE0010-Open PO.xlsx].['RICE0010-Open PO$'] where " & P & " = '" & Q & "'   ORDER BY [Need By]"

YANWEI82123300

魂断蓝桥 发表于 2024-5-21 10:48
strSQL = "Select ,,[Line Type],,[Description],,[Closed Code],[Vendor],[Qty],[Qty Recd],[Qty Acc ...

蓝桥老师我使用A1单元格值为'ITEM'时候数据可以查询出结果,但是A1单元格改为'PO Number',B1值为 4214021036,就报错

魂断蓝桥

     strSQL = "Select [PO Number],[PO Line Number],[Line Type],[Item],[Description],[BUYER],[Closed Code],[Vendor],[Qty],[Qty Recd],[Qty Accept],[Need By],[Promise] From [excel 12.0;Database=" & ThisWorkbook.Path & "\RICE0010-Open PO.xlsx].['RICE0010-Open PO$'] where [" & P & "]='" & Q & "'   ORDER BY [Need By]"


字段名称有空格需要用把字段名称放在[字 段] 中

YANWEI82123300

魂断蓝桥 发表于 2024-5-21 11:18
strSQL = "Select ,,[Line Type],,[Description],,[Closed Code],[Vendor],[Qty],[Qty Recd],[Qty Acc ...

谢谢 魂断蓝桥老师指导!!