Access VBA中的Excel中的数据透视表 [英] Pivot tables in Excel from Access VBA

问题描述

我正在使用Access VBA格式化通过Access创建的Excel电子表格。

我在函数顶部声明了以下内容：

Dim oApp As Excel.Application
Dim oPt As Excel.PivotTable
Dim oPt2 As Excel.PivotTable
Dim oPc As Excel.PivotCache

Dim oApp As Excel.Application
Dim oPt As Excel.PivotTable
Dim oPt2 As Excel.PivotTable
Dim oPc As Excel.PivotCache

我打开电子表格，没有将oApp设置到打开工作表的问题。格式化没有问题。

I open up the spreadsheet with no problems with set oApp to the open worksheet. Formatting there are no problems.

然后我使用第一个工作表中的数据创建一个数据透视表第二个工作表：

Then I use the data in the first worksheet to create a pivot table on a second worksheet:

设置oPc = oApp.ActiveWorkbook.PivotCaches.Add（SourceType：= xlDatabase，SourceData：= _
FileName（x）&"！R1C1：R20001C74"）
设置oPt = oApp.ActiveSheet.PivotTables.Add（PivotCache：= oPc，TableDestination：=""，TableName _
：=" PivotTable1"）

    Set oPc = oApp.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        FileName(x) & "!R1C1:R20001C74")
    Set oPt = oApp.ActiveSheet.PivotTables.Add(PivotCache:=oPc, TableDestination:="", TableName _
        :="PivotTable1")

再没问题。它就像一个魅力，因为代码然后将各种字段添加到这个新的数据透视表。问题似乎是我只能使用那个pivotcache ONCE。当我尝试使用

No problem again. It works like a charm as the code then adds various fields to this new pivot table. The problem seems to be that I can only use that pivotcache ONCE. The code dies when I try to add a second pivot table to this Excel file with

设置oPt2 = oApp时，我会尝试将第二个数据透视表添加到此Excel文件中。 ActiveSheet.PivotTables.Add（PivotCache：= oPc，TableDestination：=""，TableName _
：=" PivotTable2"）

    Set oPt2 = oApp.ActiveSheet.PivotTables.Add(PivotCache:=oPc, TableDestination:="", TableName _
        :="PivotTable2")

通过创建另一个pivotcache并基于这个新缓存构建第二个pivottable，我能够解决这个问题的唯一方法。不幸的是，这使得保存的电子表格的大小增加了三倍。

The only way I have been able to get around this by creating yet another pivotcache and building the second pivottable based on this new cache.  Unfortunately, this triples the size of the saved spreadsheet. 

代码本身是从记录的Excel VBA宏直接转换，我做了一些调整它通过Access VBA（创建Excel.application对象等）运行。

The code itself is a straight conversion from a recorded Excel VBA macro, with some tweaking on my part to make it run via Access VBA (creating the Excel.application object, etc.)

任何人都知道重用的方法从其他Office产品自动化Excel时，各种数据透视表上的相同pivotcache？当代码在Excel内部运行时，重复使用相同的pivotcache以获得额外的pivottables没有问题。

Anyone know a way of reusing the same pivotcache on various pivot tables when automating Excel from other Office products?  When the code is run inside Excel itself, there are no problems reusing the same pivotcache repeatedly for additional pivottables.

提前致谢！

推荐答案

以下代码可帮助您获得所需的结果。 在数据透视表开发代码之前执行此代码。

The code below should help you achieve the results you are looking for.  Execute this code prior to your pivot table development codes.

Sub Pivot_Table_Cache（）

'

'Pivot_Table Macro

'使所有数据透视表使用相同的数据透视表缓存

'

'步骤1：声明您的可变物品
     Dim ws As Worksheet

    Dim pt As PivotTable

    Dim SourceRange作为范围

   设置SourceRange = ActiveWorkbook.Worksheets（" NAME_OF_WORKSHEET_FOR_DATASOURCE）.Range（&＃A：AG ######"）

   

'第2步：遍历工作簿中的每张工作表

   对于每个ws在ThisWorkbook.Worksheets

   

'第3步：遍历每个数据透视表

        For Each pt In ws.PivotTables

            pt.RefreshTable

       下一页pt

   下一个ws

End Sub

Sub Pivot_Table_Cache()
'
' Pivot_Table Macro
' Make All Pivot Tables Use the Same Pivot Cache
'
'Step 1: Declare Your Varibles
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim SourceRange As Range
    Set SourceRange = ActiveWorkbook.Worksheets("NAME_OF_WORKSHEET_FOR_DATASOURCE).Range("A#:AG######")
   
'Step 2: Loop through each sheet in workbook
    For Each ws In ThisWorkbook.Worksheets
   
'Step 3: Loop through each PivotTable
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

这篇关于Access VBA中的Excel中的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！