sql - Automatically sort data-bound pivot table in two dimensions in Excel 2007 using VBA -
here's annoying issue. i've got pivot table in excel spreadsheet gets data direct sql server query.
the table has customers on vertical axis, , dates on horizontal access. both need correctly sorted - i.e. customers alphabetical top bottom , dates in date order left right.
i've ensured data coming out of sql recognised excel date field. can sort dates using manual a-z function. need automatically using vba.
i had hoped (againt hope) using 2 sorting parameters on sql query might trick:
sql = "select * myview order customer, date" set pt = worksheets("myreport").pivottables("mypivot") pt.pivotcache.commandtext = sql pt.refreshtable
but doesn't.
i see in excel 2010 onwards there's handy autosort function should need. i'm stuck 2007. there way sort data in both dimensions?
turns out there autosort feature in excel 2007, belongs pivotfields object, rather pivot table itself.
dim pt pivottable set pt = worksheets("myworksheet").pivottables("mypivottable") pt.pivotfields("date").autosort xlascending, "date"
Comments
Post a Comment