• 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
Creating, modifying and formatting a pivot table with AS in Excel 2008
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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
  • From: Jim Skibbie <email@hidden>
  • Date: Mon, 19 Jan 2009 11:25:53 -0600
  • Thread-topic: Creating, modifying and formatting a pivot table with AS in Excel 2008
Title: 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
  • Follow-Ups:
    • Re: Creating, modifying and formatting a pivot table with AS in Excel 2008
      • From: Jim Skibbie <email@hidden>
  • Prev by Date: Re: How do you test if a property is empty
  • Next by Date: Re: Using Applescript Droplet as Default Application? (10.4.11)
  • Previous by thread: RE: How do you test if a property is empty
  • Next by thread: Re: Creating, modifying and formatting a pivot table with AS in Excel 2008
  • Index(es):
    • Date
    • Thread