使用VBA的数据透视表布局 [英] pivot table layout using VBA

查看:312
本文介绍了使用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(xlInsideHorizo​​ntal).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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆