Excel VBA教程:Recordset属性

返回或设置一个 Recordset对象,该对象作为指定查询表的数据源。

说明

如果用该属性来覆盖一个已经存在的记录集,当Refresh方法运行之后,改动才会起作用。

Excel VBA教程:Recordset属性·示例

本示例对用于第一张查询表(第一张工作表上)的 Recordset对象进行更改,然后刷新该查询表。


With Worksheets(1).QueryTables(1)
    .Recordset = _
        OpenDatabase("c:\Nwind.mdb") _
        .OpenRecordset("employees")
    .Refresh
End With

本示例在活动工作表的 A3 单元格上,通过连接到 Microsoft Jet 上的 ADO 创建一个新的数据透视表高速缓存,然后再基于该高速缓存新建一个数据透视表。


Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
    .ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0"
    .Open "C:\perfdate\record.mdb"
End With
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
    .CommandText = "Select Speed, Pressure, Time From DynoRun"
    .CommandType = adCmdText
    .Execute
End With
' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
    .CreatePivotTable TableDestination:=Range("A3"), _
        TableName:="Performance"
End With
With ActiveSheet.PivotTables("Performance")
    .SmallGrid = False
    With .PivotFields("Pressure")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Speed")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Time")
        .Orientation = xlDataField
        .Position = 1
    End With
End With
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing

上页:Excel VBA教程:RecordRelative属性 下页:Excel VBA教程:ReferenceStyle属性

Excel VBA教程:Recordset属性

Excel VBA教程:ReferenceStyle属性 Excel VBA教程:RefersTo属性
Excel VBA教程:RefersToLocal属性 Excel VBA教程:RefersToR1C1属性
Excel VBA教程:RefersToR1C1Local属性 Excel VBA教程:RefersToRange属性
Excel VBA教程:RefreshDate属性 Excel VBA教程:Refreshing属性
Excel VBA教程:RefreshName属性 Excel VBA教程:RefreshOnChange属性
Excel VBA教程:RefreshOnFileOpen属性 Excel VBA教程:RefreshPeriod属性
Excel VBA教程:RefreshStyle属性 Excel VBA教程:RegisteredFunctions属性
Excel VBA教程:RelyOnCSS属性 Excel VBA教程:RelyOnVML属性
Excel VBA教程:RemovePersonalInformation属性 Excel VBA教程:RepeatItemsOnEachPrintedPage属性
Excel VBA教程:ReplaceFormat属性 Excel VBA教程:ReplacementList属性
版权所有 © 中山市飞娥软件工作室 证书:粤ICP备09170368号