Creating, modifying and formatting a pivot table with AS in Excel 2008
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