使用VBA的数据透视表布局 [英] pivot table layout using VBA
问题描述
创建数据透视表时,手动创建时布局会有所不同。请帮助我。
When I created pivot table the layout differs when created manually. please help me.
Private Sub Report_scheduled_not_generated_JP_Click()
Dim FName As String
Dim Wb As Workbook
Dim Ws As Worksheet
昏暗的范围在哪里
Dim objTable As PivotTable,objField As PivotField
FName =" C:\ Users \ PN00444496 \Desktop \ Report报告未生成" &安培;格式(日期,"DD-MMM-YYYY")& " .xlsx"
'在ReadOnly-Mode中打开文件
设置Wb = Workbooks.Open(" C:\ Users \PN00444496 \Desktop\exportrs.csv",False,True)
'获取第一张表并将其命名为
设置Ws = Wb.Worksheets(1)
Ws.Name ="报告预定未生成"
'获取数据单元格
设置Where = Ws.UsedRange
'应用一些格式化
使用Where.Rows(1).Interior
.Pattern = xlSolid
.ThemeColor = xlThemeColorAccent1
结束时与
Where.BorderAround xlContinuous
Where.Borders(xlInsideVertical).LineStyle = xlContinuous
Where.Borders(xlInsideHorizontal).LineStyle = xlContinuous
Where.EntireColumn.AutoFit
'创建数据透视表
设置objTable = Ws.PivotTableWizard
'指定行和列字段。
设置objField = objTable.PivotFields(" REPORT_TYPE")
objField.Orientation = xlRowField
设置objField = objTable.PivotFields(" CASE_LOCK_STATUS")
objField.Orientation = xlRowField
设置objField = objTable.PivotFields(" DUE_WHEN")
objField.Orientation = xlColumnField
设置objField = objTable.PivotFields(&TRACKING_NUM")
objField.Orientation = xlDataField
objField.Function = xlCount
'objField.NumberFormat =" $#,## 0"
'保存文件
Wb.SaveAs FName,xlWorkbookDefault
'Wb.Close
End Sub
Private Sub Report_scheduled_not_generated_JP_Click()
Dim FName As String
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Where As Range
Dim objTable As PivotTable, objField As PivotField
FName = "C:\Users\PN00444496\Desktop\Report scheduled Not Generated " & Format(Date, "DD-MMM-YYYY") & ".xlsx"
'Open the file in ReadOnly-Mode
Set Wb = Workbooks.Open("C:\Users\PN00444496\Desktop\exportrs.csv", False, True)
'Get the 1st sheet and name it
Set Ws = Wb.Worksheets(1)
Ws.Name = "Report scheduled Not Generated"
'Get the data cells
Set Where = Ws.UsedRange
'Apply some formattings
With Where.Rows(1).Interior
.Pattern = xlSolid
.ThemeColor = xlThemeColorAccent1
End With
Where.BorderAround xlContinuous
Where.Borders(xlInsideVertical).LineStyle = xlContinuous
Where.Borders(xlInsideHorizontal).LineStyle = xlContinuous
Where.EntireColumn.AutoFit
'Create Pivot Table
Set objTable = Ws.PivotTableWizard
' Specify row and column fields.
Set objField = objTable.PivotFields("REPORT_TYPE")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("CASE_LOCK_STATUS")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("DUE_WHEN")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("TRACKING_NUM")
objField.Orientation = xlDataField
objField.Function = xlCount
' objField.NumberFormat = "$ #,##0"
'Save the file
Wb.SaveAs FName, xlWorkbookDefault
'Wb.Close
End Sub
推荐答案
这是讨论Microsoft Excel问题和反馈的论坛,我将把你的问题转到MSDN论坛上的Excel
This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel
https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev
我们建议适当发布的原因是您将获得最合格的受访者群体,而定期阅读论坛的其他合作伙伴可以分享他们的知识或从您与我们的互动中学习。感谢您的理解。
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.
这篇关于使用VBA的数据透视表布局的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!