[已解决]SQL查询时出现系统错误，求助

www1988

代码如下：

1. Sub Ado0()
   
2. Dim cnn As New ADODB.Connection
   
3. 
   
4. 
   
5. 
   
6. Dim rst As New ADODB.Recordset
   
7. 
   
8. 
   
9. 
   
10. Dim Sql As String
    
11. 
    
12. Dim lujing As String
    
13. 
    
14. Dim i As Integer
    
15. 
    
16. Dim j As Integer
    
17. 
    
18. lujing = ThisWorkbook.Path & Application.PathSeparator & "通讯录.xls"
    
19. 
    
20. cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0';Data Source=" & lujing
    
21. 
    
22. 
    
23. Sql = "Select 姓名 From [Sheet1$] "
    
24. 
    
25. rst.Open Sql, cnn, adOpenKeyset
    
26. 
    
27. For i = 1 To rst.Fields.Count
    
28. 
    
29. 
    
30. 
    
31. Sheet1.Cells(1, i) = rst.Fields(i - 1).Name
    
32. 
    
33. 
    
34. 
    
35. Next i
    
36. 
    
37. 
    
38. rst.Close
    
39. 
    
40. cnn.Close
    
41. 
    
42. Set rst = Nothing
    
43. 
    
44. Set cnn = Nothing
    
45. 
    
46. End Sub
    

复制代码

运行后如此这般：

恳请高手帮助！！

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-1-28 23:56

1. Sub Ado0()
   
2.     Dim cnn As New ADODB.Connection
   
3.     Dim rst As New ADODB.Recordset
   
4.     Dim Sql As String
   
5.     Dim lujing As String
   
6.     Dim i As Integer
   
7.     Dim j As Integer
   
8.     ActiveSheet.UsedRange.Clear
   
9.    
   
10.     lujing = ThisWorkbook.Path & Application.PathSeparator & "通讯录.xls"
    
11.     cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";Data Source=" & lujing
    
12.     Sql = "Select * From [Sheet1$] "
    
13.     rst.Open Sql, cnn, adOpenKeyset
    
14.     For i = 1 To rst.Fields.Count
    
15.         Sheet1.Cells(1, i) = rst.Fields(i - 1).Name
    
16.     Next i
    
17.     Range("a2").CopyFromRecordset rst
    
18.     rst.Close
    
19.     cnn.Close
    
20.     Set rst = Nothing
    
21.     Set cnn = Nothing
    
22.     MsgBox "查询完毕"
    
23. End Sub

复制代码

跳转到最佳答案楼层

hwc2ycy

控件引用了没有？

www1988

hwc2ycy 发表于 2013-1-28 22:51
控件引用了没有？

控件已经引用了
xuexi.zip (10.29 KB, 下载次数: 17)

2013-1-28 22:56 上传

点击文件名下载附件
这个是罪魁祸首

hwc2ycy

1. Sub Ado0()
   
2.     Dim cnn As New ADODB.Connection
   
3.     Dim rst As New ADODB.Recordset
   
4.     Dim Sql As String
   
5.     Dim lujing As String
   
6.     Dim i As Integer
   
7.     Dim j As Integer
   
8. 
   
9.     lujing = ThisWorkbook.Path & Application.PathSeparator & "通讯录.xls"
   
10.     cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";Data Source=" & lujing ' & ""Excel 8.0;HDR=YES;IMEX=0"";"
    
11.     Sql = "Select * From [Sheet1$] "
    
12.     rst.Open Sql, cnn, adOpenKeyset
    
13.     For i = 1 To rst.Fields.Count
    
14.         Sheet1.Cells(1, i) = rst.Fields(i - 1).Name
    
15.     Next i
    
16.     Range("a2").CopyFromRecordset rst
    
17.    
    
18.     rst.Close
    
19.     cnn.Close
    
20.     Set rst = Nothing
    
21.     Set cnn = Nothing
    
22. End Sub

复制代码

hwc2ycy

hwc2ycy

你工作簿下所在的文件夹下有通讯录工作簿没？

hwc2ycy

1. Sub Ado0()
   
2.     Dim cnn As New ADODB.Connection
   
3.     Dim rst As New ADODB.Recordset
   
4.     Dim Sql As String
   
5.     Dim lujing As String
   
6.     Dim i As Integer
   
7.     Dim j As Integer
   
8.     ActiveSheet.UsedRange.Clear
   
9.    
   
10.     lujing = ThisWorkbook.Path & Application.PathSeparator & "通讯录.xls"
    
11.     cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";Data Source=" & lujing
    
12.     Sql = "Select * From [Sheet1$] "
    
13.     rst.Open Sql, cnn, adOpenKeyset
    
14.     For i = 1 To rst.Fields.Count
    
15.         Sheet1.Cells(1, i) = rst.Fields(i - 1).Name
    
16.     Next i
    
17.     Range("a2").CopyFromRecordset rst
    
18.     rst.Close
    
19.     cnn.Close
    
20.     Set rst = Nothing
    
21.     Set cnn = Nothing
    
22.     MsgBox "查询完毕"
    
23. End Sub

复制代码

www1988

hwc2ycy 发表于 2013-1-28 23:56

感谢您的大力帮助，但是问题依然没有解决，也不知道是不是我太菜的原因，我想让代码指定查询，比如通讯录有姓名职务和电话，我只想查询到姓名和电话，对了，通讯录还有一个合并单元格做的表头
，如下：（抱歉隐藏下资料）

我想改的代码如下：

1. Sub Ado0()
   
2. Dim cnn As New ADODB.Connection
   
3. 
   
4. 
   
5. 
   
6. Dim rst As New ADODB.Recordset
   
7. 
   
8. 
   
9. 
   
10. Dim Sql As String
    
11. 
    
12. Dim lujing As String
    
13. 
    
14. Dim i As Integer
    
15. 
    
16. Dim j As Integer
    
17. 
    
18. Dim r As Integer
    
19. 
    
20. lujing = ThisWorkbook.Path & Application.PathSeparator & "通讯录.xls"
    
21. 
    
22. cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";Data Source=" & lujing
    
23. 
    
24. 
    
25. 
    
26. 
    
27. 
    
28. Sql = "Select 姓名 From [Sheet1$] "
    
29. 
    
30. 
    
31. rst.Open Sql, cnn, adOpenKeyset
    
32. 
    
33. 
    
34. For i = 1 To rst.RecordCount
    
35. 
    
36. 
    
37. 
    
38. For j = 0 To rst.Fields.Count - 1
    
39. 
    
40. 
    
41. 
    
42. Sheet1.Cells(i + 1, j + 1) = rst.Fields(j)
    
43. 
    
44. 
    
45. 
    
46. Next j
    
47. 
    
48. 
    
49. 
    
50. rst.MoveNext
    
51. 
    
52. 
    
53. 
    
54. Next i
    
55. 
    
56. 
    
57. 
    
58. rst.Close
    
59. 
    
60. cnn.Close
    
61. 
    
62. Set rst = Nothing
    
63. 
    
64. Set cnn = Nothing
    
65. 
    
66. End Sub
    

复制代码

刚刚通过您的帮助，代码可以正常运行了，我看出来主要的改动有HDR=YES;IMEX=0，我会查查什么意思的，上面我想把SQL语句改成Select 姓名 From [Sheet1$]，这时候又出现了错误，请问怎么解决呢？

hwc2ycy

合并表头去掉，你的数据格式有问题。

www1988

hwc2ycy 发表于 2013-1-29 09:39
合并表头去掉，你的数据格式有问题。

合并表头有方法绕过吗？这个格式是固定的，不能随意更改，另外去掉后依然不能执行Select 姓名,电话 From [Sheet1$]

如果需要我再开一贴提最佳