• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Creating, modifying and formatting a pivot table with AS in Excel 2008
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Creating, modifying and formatting a pivot table with AS in Excel 2008

  • Subject: Re: Creating, modifying and formatting a pivot table with AS in Excel 2008
  • From: Jim Skibbie <email@hidden>
  • Date: Mon, 19 Jan 2009 16:30:23 -0600
  • Thread-topic: Creating, modifying and formatting a pivot table with AS in Excel 2008
Title: Re: Creating, modifying and formatting a pivot table with AS in Excel 2008
Here’s what I’ve come up with so far. This seems really slow compared to the Excel 2004 VB macro. Any thoughts on a faster way to do this?

Jim


set PTRows to {"code", "projectNumber", "brand", "projectDescription", "size", "product", "upc", "currentTask", "taskOwner", "ActualStartDate", "comments", "Project Status"}

set PTFields to {"Team", "projectType"}

set PTTableName to "PivotTable3"

tell application "Microsoft Excel"
    activate
    set CWB to active workbook
    set the name of the active sheet to "Data"
    set newsheet to make new worksheet at the end of CWB
    tell newsheet
        set name to "Pivot Table"
    end tell
    set PTSheet to the sheet named "Pivot Table"
    
    --creates Pivot Table
    set PTable to make new pivot table at PTSheet with properties ¬
        {source data:range "Data!A1:BA29999", table range1:range "", name:PTTableName}
    
    set small grid of PTable to false
    
    add fields to pivot table PTable row fields PTRows page fields PTFields with add to table
    
    --sets PivotTable Row Fields to Subtotal of None
    repeat with i in PTRows
        set pf to pivot field i of pivot table PTTableName of active sheet
        set subtotals pf subtotal index 1 without value
    end repeat
    
    set the pivot field orientation of the pivot field named "currentTask" of PTable to orient as data field
    
end tell



From: Jim Skibbie
Date: Mon, 19 Jan 2009 11:25:53 -0600
To: "email@hidden" <email@hidden>
Conversation: Creating, modifying and formatting a pivot table with AS in Excel 2008
Subject: Creating, modifying and formatting a pivot table with AS in Excel 2008

Can anyone point me in the right direction on how to create a Pivot Table in Excel 2008 with Applescript. I have a user who has a VBA Macro that needs to get converted to Applescript because VBA Macros are no longer supported in Excel 2008 for Mac. I don’t use Excel much, so the concept of the Pivot Table is somewhat foreign to me.

I’ve found some references on how to manipulate the Pivot Table, but nothing showing how to create the Pivot Table from scratch.

Thanks.
Jim

Here’s are the two VBA Macro I’m trying to convert:

Sub Create_Pivot()

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'Pivot Template'!R1C1:R29999C53", TableDestination:="", TableName:= _
        "PivotTable3"
    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable3").PivotFields("code").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("projectNumber").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotTable3").PivotFields("projectType").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("projectDescription"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("size").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("brand").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("product").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("upc").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("currentTask").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("taskOwner").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("ActualStartDate"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("comments").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Team").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Project Status").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("code", _
        "projectNumber", "brand", "projectDescription", "size", "product", "upc", _
        "currentTask", "taskOwner", "ActualStartDate", "comments", "Project Status"), _
        PageFields:=Array("Team", "projectType")
    ActiveSheet.PivotTables("PivotTable3").PivotFields("currentTask").Orientation _
        = xlDataField
End Sub
Sub Format_Pivot()
'
' Format_Pivot Macro
 
    With ActiveWindow
    End With
    ActiveCell.Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ActiveCell.Offset(0, 12).Columns("A:A").EntireColumn.Select
    Selection.EntireColumn.Hidden = True
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 313
    End With
    ActiveWindow.SmallScroll ToRight:=1
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    ActiveWindow.SmallScroll ToRight:=-1
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    ActiveCell.Offset(2, -12).Range("A1").Select
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 12.29
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.ColumnWidth = 18.86
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.ColumnWidth = 20.29
    ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.ColumnWidth = 12.14
    ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.ColumnWidth = 14.71
    ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.ColumnWidth = 16
    ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.ColumnWidth = 17.71
    ActiveCell.Offset(0, 6).Columns("A:A").EntireColumn.ColumnWidth = 15.86
    ActiveCell.Offset(0, 7).Columns("A:A").EntireColumn.ColumnWidth = 16.14
    ActiveWindow.SmallScroll ToRight:=1
    ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.ColumnWidth = 13
    ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.ColumnWidth = 15.29
    ActiveCell.Offset(0, 10).Columns("A:A").EntireColumn.ColumnWidth = 12.86
    ActiveCell.Offset(0, 10).Columns("A:A").EntireColumn.ColumnWidth = 16.57
    ActiveCell.Offset(0, 9).Columns("A:A").EntireColumn.ColumnWidth = 8.86
    ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.ColumnWidth = 11.71
    ActiveWindow.SmallScroll ToRight:=-9
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$3:$3"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.EntireRow.Hidden = True
    ActiveWindow.SmallScroll ToRight:=-3
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$5:$5"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "TEAM NOTES"
        .CenterHeader = "&D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "PAGE &P OF &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(0.7)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
    End With
    
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
End Sub



 
 _______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users

This email sent to email@hidden
References: 
 >Creating, modifying and formatting a pivot table with AS in Excel 2008 (From: Jim Skibbie <email@hidden>)

  • Prev by Date: Re: Finder thru Safari
  • Next by Date: Re: How do you test if a property is empty
  • Previous by thread: Creating, modifying and formatting a pivot table with AS in Excel 2008
  • Next by thread: (no subject)
  • Index(es):
    • Date
    • Thread