我想用ADO+SQL语句查询EXCEL，但得不到结果，大侠帮忙查找一下原因

[yiyaozjk]

我想用ADO+SQL语句查询EXCEL，但得不到结果，大侠帮忙查找一下原因，谢谢

Public xlApp As Excel.Application          '定义EXCEL类
Public xlBook As Excel.Workbook            '定义工件簿类
Public xlsheet As Excel.Worksheet          '定义工作表类
Private Sub Command1_Click()
   FileName = "仓库.xls"
Set xlApp = GetObject(, "Excel.Application")     '判断Excel是否打开
xlApp.WindowState = xlMaximized
           Set xlBook = xlApp.Workbooks.Open(App.Path & "\" & FileName) '打开工件簿文件
           xlApp.Visible = True
Set cnn = CreateObject("ADODB.Connection")
    Set rst2 = CreateObject("ADODB.Recordset")
'    Set xlsheet = xlBook.Worksheets(4)
    ' xlsheet.Unprotect "123abc"
   ' xlsheet.Range("J:J").ClearContents

    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=Excel 8.0;data source=" & App.Path & "\" & FileName
   ' StrSQL = "SELECT * FROM [" & Sh.Name & "$]"
     StrSQL = "SELECT * FROM [结存$] where 物料名称='JACK IN THE BOX'"
     StrSQL = "SELECT * FROM [结存$] "
    ' Set rst2 = cnn.Execute("SELECT 物料名称,规格型号,出库地点,出库人 FROM [出库$] where 物料名称 ='TARGET.COM'and 出库方式='采购入库'")
      Set rst2 = cnn.Execute(StrSQL)
       Set xlsheet = xlBook.Worksheets(2)
      xlsheet.Range("a5").CopyFromRecordset rst2
      For i = 1 To 2  'rst2.Fields.Count '获得SQL结果的列标题
      ' MsgBox rst2.Fields(i - 1).Name '字段名，
     '  MsgBox rst2.Fields(i - 1) '字段名，

    Next
End Sub

跟踪没有提示错误，我错了哪里？ 谢谢

[泊客天涯]

好像是你的

引用

Set xlBook = xlApp.Workbooks.Open(App.Path & "\" & FileName) '打开工件簿文件

与

引用

cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=Excel 8.0;data source=" & App.Path & "\" & FileName

冲突，你打开excel文件想用它作为数据源可能不行，我忘记了，可能是这样的你试试吧

[泊客天涯]

再有 你判断是否打开excel后 如果没有打开是不是要打开一个呢

   On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")     '判断Excel是否打开
If Err.Number = 429 Then Set xlApp = CreateObject("Excel.Application")

[dong_y888]

Public xlApp As Excel.Application          '定义EXCEL类
Public xlBook As Excel.Workbook            '定义工件簿类
Public xlsheet As Excel.Worksheet          '定义工作表类
Private Sub Command1_Click()
   FileName = "仓库.xls"

Set cnn = CreateObject("ADODB.Connection")
    Set rst2 = CreateObject("ADODB.Recordset")

    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=Excel 8.0;data source=" & App.Path & "\" & FileName
   ' StrSQL = "SELECT * FROM [" & Sh.Name & "$]"
     StrSQL = "SELECT * FROM [结存$] where 物料名称='JACK IN THE BOX'"
     StrSQL = "SELECT * FROM [结存$] "
    ' Set rst2 = cnn.Execute("SELECT 物料名称,规格型号,出库地点,出库人 FROM [出库$] where 物料名称 ='TARGET.COM'and 出库方式='采购入库'")
      Set rst2 = cnn.Execute(StrSQL)
       

      
     
      For i = 1 To 2  'rst2.Fields.Count '获得SQL结果的列标题
          MsgBox rst2.Fields(i - 1).Name '字段名，
          MsgBox rst2.Fields(i - 1) '字段名，

    Next
    rst2.Close
    Set rst2 = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

要将结果放到工作表中，可以再打开另一个工作薄放进去，不能放到查询的工作薄中。