|
SQL调用程序出不了数据。
把 'On Error Resume Next 注释掉,发现:
'strSQL = "select 姓名,身份证号 from [" & s & "]" '把这句去掉就出错了,但是不去掉,就等于说上面“载入数据”这个程序是多余的。没有实现调用的过程,不知问题出在哪里?????????
- Dim strSQL, S, myPath$, OutputSheet$, OutputRange$
- Sub 载入数据()
- strSQL = "select 姓名,身份证号 from "
- '条件 = "WHERE 姓名='王丽力'"
- OutputSheet = "结果"
- OutputRange = "A2"
- Call subProgram(strSQL, Pathstr, OutputSheet, OutputRange, "WHERE 姓名='王丽力'") '调用子程序
- MsgBox "OK"
- End Sub
- Sub subProgram(ByVal strSQL$, ByVal myPath$, ByVal OutputSheet$, ByVal OutputRange$, strCondition$) '子程序
- Dim cnn As Object, Rst As Object, rs As Object
- Dim strConn As String
- Dim i As Integer, j%, Pathstr, S$, t$, sProvider$
- Pathstr = Application.GetOpenFilename(fileFilter:="Excel文件(*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="打开Excel文件", MultiSelect:=True) '选择多个EXCCEL 文件
- If TypeName(Pathstr) = "Boolean" Then Exit Sub
- Application.ScreenUpdating = False
- Select Case Application.Version * 1
- Case Is <= 11
- sProvider = "Provider = Microsoft.Jet.Oledb.4.0;Extended Properties ='Excel 8.0';Data Source ="
- Case Is >= 12
- sProvider = "Provider = Microsoft.Ace.Oledb.12.0;Extended Properties ='Excel 12.0';Data Source ="
- End Select
- Cells.ClearContents
- On Error Resume Next '
- myPath = S
- For i = 1 To UBound(Pathstr)
- If Pathstr(i) <> ThisWorkbook.FullName Then
- Set cnn = CreateObject("ADODB.Connection")
- cnn.Open sProvider & Pathstr(i)
- Set rs = cnn.OpenSchema(20)
- Do Until rs.EOF
- If rs.Fields("TABLE_TYPE") = "TABLE" Then
- S = Replace(rs("TABLE_NAME").Value, "'", "")
- If Right(S, 1) = "$" Then
- Set Rst = cnn.Execute(strSQL & " [" & S & "] " & strCondition)
- If Err.Number = 0 Then
- m = m + 1
- If m = 1 Then
- For j = 0 To Rst.Fields.Count - 1
- Cells(1, j + 1) = Rst.Fields(j).Name
- Next
- Range("A2").CopyFromRecordset Rst
- Else
- Range("A65536").End(xlUp).Offset(1).CopyFromRecordset Rst
- End If
- Exit Do
- Else
- Err.Clear
- End If
- End If
- End If
- rs.MoveNext
- Loop
- End If
- Next
- Cells.EntireColumn.AutoFit
- Rst.Close
- rs.Close
- cnn.Close
- Set cnn = Nothing
- Set rs = Nothing
- Set Rst = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|