excel - 循环通过 Power Pivot、数据透视表过滤器并打印到 PDF
问题描述
目标是循环遍历 Excel Power Pivot、数据透视表并将每个过滤结果打印到特定文件位置的 PDF 中。
当代码到达输出的 for 循环时,它给了我一个错误
“运行时错误 - 438 对象不支持该属性或方法”
在线上For Each pi In pt
Sub Button1_Click()
Dim strPath As String
Dim wksSource As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim cf As CubeField
Set wksSource = Worksheets("Summary for Each Analyst")
Set pt = wksSource.PivotTables("PivotTable1")
Set cf = pt.CubeFields("[Std_MainData].[CredentialingAnalyst]")
If cf.Orientation <> xlPageField Then
MsgBox "There's no 'Credentialing Analyst' field in the Report Filter. Try again!", vbExclamation
End If
strPath = "H:\"
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
ActiveWorkbook.ShowPivotTableFieldList = False
pt.PivotCache.Refresh
For Each pi In pt
wksSource.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & pi.Name & ".pdf"
Next pi
解决方案
整个周末我都在为同样的问题苦苦挣扎,最后我终于让它通过切片器而不是报告过滤器循环工作。
'This VBA will loop through your Power Pivot slicer and print the results to PDF.
'To get it working change slicer name and storage location in below VBA.
Private Sub PowerPivotLoopSlicerPrintPDF()
Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Set SC = ActiveWorkbook.SlicerCaches("Slicer_Kolonne1") 'Add slicer name between " "
Set SL = SC.SlicerCacheLevels(1)
'c(ounter) is set to 1, ready to begin
c = 1
'Repeat the a loop until number of prints exceeds number of items in slicer
Do While c <= SC.SlicerCacheLevels.Item.Count + 1
'This makes sure that SI is the correct slicer. Needed for corrent file name.
For Each SI In SL.SlicerItems
If SI.Selected = True Then
SlicerverdiIndex = c
Exit For
End If
Next SI
'PRINT CODE
Dim FName As String
Dim FPath As String
'Define file path for printed file storage
FPath = "C:\Users\remia\Desktop\VBA\" 'Choose your filepath
FName = SI.SourceName
'Define WHAT to print and how to build file name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FPath & "\" & FName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
'PRINT CODE FINISHED
'Sets the slicer to the last item in the list
If SlicerverdiIndex = 1 Then
SlicerverdiIndex = SC.SlicerCacheLevels.Item.Count + 1
End If
SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex - 1).Name
'Adds 1 to the counter, will loop until end of slicer has been reached.
c = c + 1
Loop
End Sub
推荐阅读
- python - 小部件跨越 Pyqt5 上的选项卡
- mysql - mysql导入模型转发工程师错误
- python - Matplotlib,散布着三个图例
- swift - Swift - 以编程方式刷新约束
- java - 如何配置 EC2 以在我的 Spring Boot 应用程序中处理表情符号
- python - 如何在 tkinter 窗口中绘制我的 networkx 图?
- r - like 函数因调用而异 R
- c++ - gcc 7.4.0 的奇怪 std::atof 错误
- php - Laravel - 如何在我的 Laravel 代码中从 json 中提取值
- python - 期望用双引号括起来的属性名称