|
D组学委:windimi007前来叫作业!
这次正好借用兰版的这个题练习一下在VBA中使用SQL,有什么不当之处还望兰版指导,谢谢。
- Option Explicit
- Private Sub Worksheet_Change(ByVal Target As Range)
- On Error Resume Next
- If Target.Address(0, 0) = "E5" Then
- Dim AdoConn As Object
- Dim AdoRst As Object
- Dim StrConn As String
- Dim StrSql As String
- Dim arr
- Set AdoConn = CreateObject("Adodb.connection")
- Set AdoRst = CreateObject("Adodb.RecordSet")
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
- "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
- StrSql = "SELECT 单价 FROM [Database=" & ThisWorkbook.Path & "\价格表.xls;Excel 12.0].[Sheet1$] WHERE STRCOMP(产品名称,""" & Target.Value & """,0)=0"
- AdoConn.Open StrConn
- Set AdoRst = AdoConn.Execute(StrSql)
- arr = AdoRst.GetRows
- If Err.Number = 3021 Then
- Cells(7, "E") = "查找不到"
- Else
- If UBound(arr, 2) = 0 Then
- Cells(7, "E") = arr(0, 0)
- Else
- Cells(7, "E") = Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(arr)), ",")
- End If
- End If
- AdoRst.Close
- AdoConn.Close
- Set AdoRst = Nothing
- Set AdoConn = Nothing
- End If
- End Sub
复制代码
|
评分
-
查看全部评分
|