Question

用ADO 连接excel时，提示应用程序定义错误，大侠请帮忙看下问题在哪里？？？

Private Sub AdoCnnFile()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

Dim strCnn As String
Dim SQL As String
Dim pathStr As String
Dim fileStr As String

pathStr = "F:\ExcelVBA练习\PDR文件夹"
fileStr = "pdr-6月.xlsx"

If Right(pathStr, 1) <> "\" Then
pathStr = pathStr & "\"
Else
End If

'ado 连接字符串
strCnn = "provider=Microsoft ACE OLEDB.12.0;data source=" & pathStr & fileStr & _
";Extended Properties=""excel 12.0 xml;hdr=no"";" '注意引号的数目与位置。
'ado 查询字符串
SQL = "select*from database=[Excel 12.0;database=" & pathStr & fileStr & "].[sheet1$a1:j19]"

cnn.Open strCnn
Set rs = cnn.Execute(SQL)

ThisWorkbook.Sheets(1).[a1].CopyFromRecordset rs

Set cnn = Nothing
Set rs = Nothing
Set cmd = Nothing

End Sub

Answer 1

Sub AdoCnnFile()
Dim cnn As New ADODB.Connection
cnn.Open "provider=Microsoft ACE OLEDB.12.0;data source=F:\ExcelVBA练习\PDR文件夹\pdr-6月.xlsx"
ThisWorkbook.Sheets(1).[a1].CopyFromRecordset cnn.Execute("select * from [sheet1$a:j]")
Set cnn = Nothing
End Sub
''你怎么能写那么长?连接字符串多了xml,select语句错误
''有效请采纳