|
发表于 2013-5-24 12:23
|
显示全部楼层
本楼为最佳答案
- Sub tt()
- Application.ScreenUpdating = False
- 'Dim cnn As New ADODB.Connection
- Set cnn = CreateObject("adodb.connection")
- 'Dim rst As New ADODB.Recordset
- Set rs = CreateObject("adodb.recordset")
- Dim sql As String, strfiled As String, i As Integer, j As Integer
- If [f2] <> "" Then
- With cnn
- .Provider = "microsoft.jet.oledb.4.0;"
- .Open ThisWorkbook.Path & "\data.mdb"
- End With
- sql = "select * from data where 序号='" & Cells(2, 6).Value & "'"
- Set rst = cnn.Execute(sql)
- If rst.BOF Then
- MsgBox "数据库找不到此序号"
- ActiveSheet.Unprotect "695360052"
- Union(Range("b:b"), Range("d:d"), Range("f:f")).ClearContents
- ActiveSheet.Protect "695360052"
- GoTo line1 '当序号在数据库中不存在时,清除B列、D列、F列数据,并跳到line1执行
- End If
- Set d = CreateObject("scripting.dictionary") '当序号在数据库中存在时,创建一个字典
- For i = 0 To rst.Fields.Count - 1 '循环数据库找到的记录
- d(rst.Fields(i).Name) = rst.Fields(i).Value '将数据库找到的记录保存到字典中
- Next
- For i = 2 To Range("a65536").End(3).Row
- For j = 1 To 6 Step 2
- Cells(i, j + 1) = d(Cells(i, j).Text) '循环将字典的值给到对应的单元格中
- Next
- Next
- MsgBox "获取数据成功"
- cnn.Close
- Set cnn = Nothing
- End If
- line1:
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|