Category Archives: Formatting

Some Things I Learned Lately

Posted on by
4

Seems like every day I pick up something new. So I thought I’d make a quick list of stuff I’ve learned lately.

1) XMLAGG in Teradata
Teradata’s handy XMLAGG function lets lets you flatten a column of data into a delimited list, thereby avoiding recursion. Oracle, to which I’m migrating, also has an XMLAGG function but the closer, and better-named, equivalent seems to be LISTAGG. The Teradata documentation is consistently terrible, so instead I’ll link to this Stack Overflow answer from the worthy dnoeth.

2) 64-bit Office Text File Connection Strings
While updating a fancy data-sucking addin I got an error message that my data connection was broke. Turns out MS changed the ODBC connection string for 64-bit ever so slightly from:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

to

Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

There’s two differences. The addition of the word “Access” was quickly apparent when looking at this site. The second one took me some time to spot. Can you see it? Yup, they changed the semicolon after “*.txt” to a comma. I think it looks a lot better.

3) Format vs WorksheetFunction.Text in VBA to Mimic Cell Formats
I’ve done a couple of posts that attempt to give you a sneak preview of how different formats will look when applied to cells. I was using my ActiveCell Viewer to preview different date formats for a cell. The Viewer used the VBA Format function. I noticed that in some cases it returned text that isn’t what you get in a cell with that formatting.

For instance, below I’ve applied a formatting of an increasing number of “d”s to a date. With formatting of two to four “d”s the two outputs match:

format differences 1

However with 5 or 6 “d”s the VBA function begins to return a weird result that doesn’t match what you’d see in a cell with that formatting:

format differences 2

You can see below that a cell formatting of “dddddd” still returns “Friday,” just like WorksheetFunction.Text. In fact if you close the Format Cells dialog and re-open it, you’ll see that the formatting has been reset to “dddd”.

format differences 3

I’ve since fixed my Activecell Viewer and added some features. I’ll try to remember to post the improved version sometime.

4) You Can Undo a Pivot Table Refresh
Who knew? All these years I assumed you couldn’t. And then I tried it.

pivot table unrefresh

5) Pivot Table Grouped Number Ranges, Once Sorted, Can’t Be Forced Back to Numeric Order
At least I can’t figure out how.

pivot number ranges grouped and sorted

Can you?

Saving and Reapplying Pivot Chart Formatting

Posted on by
5

I’m still wrestling with pivot charts, and boy are my metaphorical forearms big! Seriously though, I just recently became aware of the crazy problem of pivot charts losing their formatting. I’ve got a bunch of pivot charts with two x axes and other embellishments, and pretty much any change to the chart or the source pivot can erase all the carefully applied formatting. In this post I’ll outline a couple of ways to decrease, but by no means eliminate, the pain of losing your pivot chart formatting.

The Problem

Here’s an example – a chart with two axes, different chart types and non-standard colors. I’m quite pleased with its looks.

pivot chart

However, if I so much as resize a column in the source pivot… much-less-nice formatting.

pivot chart after pivot column resize

It gets worse. Look at what happens when I add and remove a field:

pivot chart formatting loss

One axis is eliminated without so much as a “by your leave,” the line graphs revert to columns and the colors regress to garish defaults. It’s a mess. Unchecking the field doesn’t undo the changes.

A Partial Solution

When I first encountered this issue my hopes were raised by the presence of a long Jon Acampora post on Jon Peltier’s blog. However the two solutions listed there have a huge drawback: they eliminate the use of pivot charts. Talk about throwing the baby out with the bath water! In the post’s comments a couple of people think they’ve found ways to make the formatting stick, but these didn’t work for me.

Looking around the web some more, I found two commands that help me as the chart developer. The first is the “Save as Template” command:

save as template command

The dialog saves to Excel’s Templates>Charts folder by default. My practice is to save early and often to the same distinctively named file:

save as template dialog

Then should my changes get wiped out, I avail myself of the “Change Chart Type” command.

change chart type command

Hey presto, there’s my template with the most recent changes. Yay!

change chart type dialog

VBA Automation

I wrote some VBA to automate this stuff. One of the routines below saves every template in the active workbook to the templates folder. It names the template with the worksheet and chart name to avoid errors from having charts with the same names on different sheets. Another routine applies a template to the active chart, assuming it can find one that meets the same SheetName_ChartName convention. Of course even if you rename or move a chart you can figure out what its template was saved at and apply it using the Change Chart Type command.

Here’s the code:

Sub SaveActiveChartTemplate()
Dim chtActive As Excel.Chart

If Not ActiveChart Is Nothing Then
    Set chtActive = ActiveChart
    SaveChartTemplate chtActive
Else
    MsgBox "No Chart Selected"
End If
End Sub

Sub SaveAllChartTemplates()
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject

For Each ws In ActiveWorkbook.Worksheets
    For Each chtObject In ws.ChartObjects
        SaveChartTemplate chtObject.Chart
    Next chtObject
Next ws
End Sub

Sub SaveChartTemplate(cht As Excel.Chart)
    'if no path specified then default folder: C:\Users\yourumbuddy\AppData\Roaming\Microsoft\Templates\Charts
    cht.SaveChartTemplate Replace(cht.Parent.Parent.Name & "_" & cht.Parent.Name & ".crtx", " ", "_")
End Sub

Sub ApplySavedTemplateToActiveChart()
Dim chtActive As Excel.Chart

If Not ActiveChart Is Nothing Then
    Set chtActive = ActiveChart
    chtActive.ApplyChartTemplate Replace(chtActive.Parent.Parent.Name & "_" & chtActive.Parent.Name & ".crtx", " ", "_")
Else
    MsgBox "No Chart Selected"
End If
End Sub

Does this work for End Users?
Only the very motivated and patient ones, I’d say. If needed though, I think you could attach code like the above to events and maybe create something that would help them retain formatting as they pivot the charts.