请教在excel中如何引用access数据库中某一列任意一行的记录,麻烦了，谢谢！

flysum2008

请教在excel中如何引用access数据库中某一列任意一行的记录？能实现这一功能吗？万分感谢！详见附件：

[ 本帖最后由 flysum2008 于 2011-4-12 07:40 编辑 ]

flysum2008

麻烦大家帮帮我，谢谢啦！

flysum2008

原帖由 zhaogang1960 于 2011-4-12 08:53 发表
Sub Macro1()
    Dim myData As String, myTable As String, SQL As String, s$
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    myData = ThisWorkbook.Path & "\职 ...

非常感谢您，占用您时间了，我好好学习一下，再次感谢！

zhaogang1960

如果要求的记录较少时，可以用记录集的AbsolutePosition属性定位：
Sub Macro1()
    Dim myData As String, myTable As String, SQL As String, a
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    myData = ThisWorkbook.Path & "\职工管理22.mdb"
    myTable = "职工基本信息22"
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "microsoft.jet.oledb.4.0"
        .Open myData
    End With
    Set rs = New ADODB.Recordset
    rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
    a = Array(1, 2, 4, 5, 7, 8, 10, 11, 12)
    [a1].CurrentRegion.ClearContents
    For i = 0 To UBound(a)
        rs.AbsolutePosition = a(i)
        Cells(i + 1, 1) = rs.Fields(0)
    Next
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

flysum2008

再次谢谢您，我的记录就是比较多，有20万条呢，我好好研究一下，不懂再向您请教！

zhaogang1960

Sub Macro1()
    Dim myData As String, myTable As String, SQL As String, s$
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    myData = ThisWorkbook.Path & "\职工管理22.mdb"
    myTable = "职工基本信息22"
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "microsoft.jet.oledb.4.0"
        .Open myData
    End With
    Set rs = New ADODB.Recordset
    rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
    s = ",1,2,4,5,7,8,10,11,12,"
    [a1].CurrentRegion.ClearContents
    For i = 1 To rs.RecordCount
        If InStr(s, "," & i & ",") Then
            m = m + 1
            Cells(m, 1) = rs.Fields(0)
        End If
        rs.MoveNext
    Next
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

zhaogang1960

数据库论坛求助.rar (31.53 KB, 下载次数: 89)

2011-4-12 08:56 上传

点击文件名下载附件

LMY123

AbsolutePosition学习中