使用MS Access中的VBA刷新Excel数据透视表数据源

I prefer producing an Excel report that has no links in it, so that it is lightweight and can easily be emailed.

我更喜欢生成一个没有链接的Excel报表,因此它很轻,可以通过电子邮件轻松发送。

So I have this report that I generate from MS Access - it writes the table to the TAB of a workbook that I have previously built a Pivot Table that uses that tab as it's source.

所以我有这个报告,我从MS Access生成 - 它将表写入工作簿的TAB,我之前已经构建了一个使用该选项卡作为源的数据透视表。

What I can't seem to get working properly is

我似乎无法正常工作的是

xlPivot.PivotTables("pvAFP").RefreshTable

My code is as follows:

我的代码如下:

Public Sub ViewAFP(strPathToTemplate As String, Optional dbFullPath As String)

'10 Excel objects created
Dim xlApp As Object
Dim WB As Object
Dim xlSheet As Object
Dim xlPivot As Object
Dim rngWorkingRange As Object
Dim intCOL As Integer

Dim db As Database

Dim rsAFP_Pivot As DAO.Recordset
Dim rsAFP_Data As DAO.Recordset



Dim fld As Variant

Dim strLocation As String


'uses the 'My Documents\COB folder of the user
'C:\Users\[username]\Documents\COB
strLocation = "C:\users\" & Environ$("Username") & "\Documents\COB\AFP_Summary_" & DateString & ".xlsx"



Set xlApp = CreateObject("Excel.Application")

Set WB = xlApp.Workbooks.Open(strPathToTemplate)

Set xlSheet = WB.Sheets("AFP DATA")
Set xlPivot = WB.Sheets("AFP PIVOT")



'ALLOW FOR EXTERNAL ENGINES
If Len(dbFullPath) < 1 Then
    Set db = CurrentDb
Else
    Set db = OpenDatabase(dbFullPath, True)
End If


Set rsAFP_Data = db.OpenRecordset("Select * FROM AFP_DATA")

intCOL = 1

For Each fld In rsAFP_Data.Fields
    xlSheet.Cells(1, intCOL).Value = fld.Name
    Debug.Print fld.Name
    intCOL = intCOL + 1
Next


xlSheet.Range("A2").CopyFromRecordset rsAFP_Data


Set rngWorkingRange = xlSheet.Range("A1").CurrentRegion


    xlSheet.ListObjects.Add(xlSrcRange, rngWorkingRange, , xlYes).Name _
        = "AFP_Data"
    xlSheet.ListObjects("AFP_Data").TableStyle = "TableStyleLight9"

'set pvAFP to point to this table
    xlPivot.PivotTables("pvAFP").ChangePivotCache WB.PivotCaches. _
        Create(SourceType:=xlDatabase, SourceData:="AFP DATA!AFP_Data", _
        Version:=xlPivotTableVersion12)



xlPivot.Select

xlPivot.PivotTables("pvAFP").RefreshTable

WB.RefreshAll
WB.SaveAs FileName:=strLocation

xlApp.Quit

Set xlSheet = Nothing
Set fld = Nothing

Set rsAFP_Data = Nothing

Set db = Nothing
Set WB = Nothing
Set xlApp = Nothing

End Sub

Would be nice to be ready to use, because the user needs to "Refresh ALL", before anything can be done to the Pivot Table

准备好使用会很好,因为用户需要“刷新所有”,然后才能对数据透视表做任何事情

1 个解决方案

#1

See if this works for you:

看看这是否适合你:

xlPivot.PivotTables("pvAFP").PivotCache.Refresh