注册 登录
编程论坛 VB6论坛

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

yiyaozjk 发布于 2014-03-21 18:51, 657 次点击
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 物料名称 =''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
跟踪没有提示错误,我错了哪里?
只有本站会员才能查看附件,请 登录
8 回复
#2
owenlu19812014-03-21 22:43
有些细节上出错了,特别是记录集定义后要设置或返回游标引擎的位置 rst2.CursorLocation = adUseClient

程序代码:


 Public xlApp As New Excel.Application          '定义EXCEL类
Public xlBook As New Excel.Workbook            '定义工件簿类
Public xlSheet As New Excel.Worksheet          '定义工作表类
Dim rst2 As New ADODB.Recordset, cnn As New ADODB.Connection

 
Private Sub Command1_Click()
    rst2.CursorLocation = adUseClient
    FileName = "仓库.xls"
    Set xlApp = CreateObject("Excel.Application")
    xlApp.WindowState = xlMaximized
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\" & FileName) '打开工件簿文件
    xlApp.Visible = True
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=Excel 8.0;data source=" & App.Path & "\" & FileName
    'StrSQL = "SELECT * FROM [结存$] where 物料名称='JACK IN THE BOX'"
    StrSQL = "SELECT * FROM [结存$]"
    rst2.Open StrSQL, cnn, 1, 1
    If Not rst2.EOF Then
        Set xlSheet = xlBook.Worksheets(2)
        xlSheet.Range("A2").CopyFromRecordset rst2
        For i = 1 To rst2.Fields.Count '获得SQL结果的列标题
            xlSheet.Cells(1, i) = rst2.Fields(i - 1).Name
        Next i
    Else
        MsgBox "没有找到记录!"
    End If
End Sub
#3
w3609894262014-03-21 22:52
2楼正解
#4
yiyaozjk2014-03-22 07:28
拜读,二楼正确。

但我写在上面的代码,是网上搬过来的,为什么就不能用Set rst2 = cnn.Execute(StrSQL)???不解
#5
owenlu19812014-03-22 10:35
应该也可以 试下
主要是要加这句,具体功能百度下,还有其它属性
rst2.CursorLocation = adUseClient
#6
yiyaozjk2014-03-22 11:24
又出现新问题了。。
   一般的查询都没有问题,
   但我发现:
    物料名称中一旦含有'时,就会出现操作符丢失的错误的。。。。
    比如:30行:WILKINSONS'S HARDWARE STORES LIMITED
          49行: SAINSBURY'S SUPERMARKETS LTD   
高手再帮我解决一下这个问题。。谢谢
#7
owenlu19812014-03-22 22:02
如果遇到字串有单引号,在SQL语句中单引号前再加一个单引号
如果遇到字串有双引号,在SQL语句中双引号前再加一个双引号

StrSQL = "SELECT * FROM [结存$] where 物料名称='WILKINSONS''S HARDWARE STORES LIMITED'"
#8
yiyaozjk2014-03-22 22:16
问题是:如果用 StrSQL = "SELECT * FROM [结存$] where 物料名称='"& 变量名 &"'"
   而这个字符串变量中又有单引号。。。。。。那如何再加单引号或双引号   啦??
#9
owenlu19812014-03-23 13:20
先将变量作字符串替换 Replace(变量名,"'","''")
一般数据库中尽量避免引号
1