无法获取工作表类的数据透视表属性 [英] Unable to get the PivotTables Property of the Worksheet Class

问题描述

我正在尝试使用另一个人制作的某些VBA，这给了我一个我似乎无法修复的错误.这是代码:

I am trying to use some VBA that another person made and it is giving me an error that I can't seem to fix. Here's the code:

Sub Format_Homeward_V3()
' Format_Homeward_V3 Macro
    ChDir "J:\Templates"
    Workbooks.Open Filename:= _
        "J:\Templates\Homeward_Exception_Report_Template_Final.xlsx"
    Workbooks.Open Filename:= _
        "J:\Templates\Homeward_Inventory_Report_Template_Final.xlsx"
    Range("A4").Select
    Sheets("Homeward_Summary").PivotTables("MyPivot").PivotCache.Refresh
    Sheets("Detail").Select
    Range("A1").Select

它在以下位置出错:

Sheets("Homeward_Summary").PivotTables("MyPivot").PivotCache.Refresh  

并显示错误:

无法获取工作表类的数据透视表属性"

"unable to get the pivottables property of the worksheet class"

但据我所知，它是在查看数据透视表的正确工作表和正确名称.我也尝试做 .RefreshTable 而不是刷新缓存，但是遇到了同样的错误.我真的不知道为什么会给我这个错误，我也不知道还有什么尝试.一段时间以来，一直没有出现此错误.

But as far as I can tell it is looking at the right sheet and the right name of the PivotTable. I also tried doing .RefreshTable instead of cache refresh but got the same error. I don't really know why it would be giving me this error and and I don't know what else to try. It's been working without this error for a while.

推荐答案

请确保已引用所有内容，然后就不必猜测要尝试处理的工作簿或工作表了(请速记，请根据需要更改)

Make sure everything is referenced, then there is no guesswork about which workbook or sheet you are trying to deal with (pardon the shorthand, alter as required)

Sub Format_Homeward_V3()
Dim wbe As Workbook
Dim wbi As Workbook
Dim ws As Worksheet
Set wbe = Workbooks.Open("J:\Templates\Homeward_Exception_Report_Template_Final.xlsx")
Set wbi = Workbooks.Open("J:\Templates\Homeward_Inventory_Report_Template_Final.xlsx")
Set ws = wbi.Sheets("PIVOT")
ws.Range("A4").Select
ws.PivotTables("MyPivot").PivotCache.Refresh

这篇关于无法获取工作表类的数据透视表属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！