Access VBA把数据库中数据转化为Excel 数据透视表

如果Access VBA 的项目中有满足要求的数据表，在EXCEL中可直接读入数据进行数据透视！

Private Sub 读取ACCESS数据()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String
Dim strcnn As String

Sheet16.Cells.ClearContents
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    sql = " select * from ACCESS数据表名称"
    strcnn = "provider=MicroSoft.ACE.OLEDB.15.0;Data Source=" & ThisWorkbook.Path & "\ACCESS数据库名称.accdb"


    cnn.Open strcnn


    rst.Open sql, cnn, adOpenKeyset, adLockOptimistic
    If rst.RecordCount < 1 Then
        MsgBox "数据表 没有数据记录!", vbCritical, "提示"
        Exit Sub
    End If

’写入字段名称
    For I = 0 To rst.Fields.Count - 1
        ActiveSheet.Cells(1, I + 1).Value = rst.Fields(I).Name
    Next
’复制数据    
    Range("A2").CopyFromRecordset rst


    MsgBox "数据读入完成!", vbInformation, "提示"


End Sub