Monday, October 24, 2011

The Trouble with PDFs

A client was having some issues where Adobe Acrobat Standard and Reader would not display some pages inside a PDF document. The PDF document was composed of a mix of pages cut and pasted from several PDF documents.

Turns out that the Acrobat PDF standard is a moving target and there are multiple authoring apps (even hardware document scanners) which may or may not comply to one of the standards - there is an Appligent document that explains the issues - and yes it is a PDF document.

Anyway, I found that Acrobat Reader and Standard X (10.1.1) refused to display the thumbnails and the pages in a Version 1.6 document but free NitroPDF Reader 2 (and other third party readers) would display those pages. One work around with some files was to use the free and Portableapped PDFtkBuilder to export the file to a Version 1.4 document. Whether this corrected the malformed PDF or Version 1.4 compliances are less rigid, I don't know.

Excel Macro does SEO comparison

I was looking at Chandoo's recent blog entry - he notes that there is an Excel macro by the Search Engine People that allows you to put in keywords, your website - it then returns a list of competitor websites ranking above your website. Nice example of VBA executing HTTP calls.

Friday, August 19, 2011

The Importance of the installed Printer in Windows

Ross, one of my clients was having a heck of a time trying to make sense of "CoInitialize" Automation  errors in VBA. It happened in VBA commands for Page Setup. He had enlisted professional help in uinstall and re-install of Office. I thought about it and wondered whether Excel was behaving like Word - the existence of a proper printer setup is vital for these programs that need to calculate Print Previews or even calculate document line width and page rendering when they work. He reported back that this was indeed the case.

Whether you are using the Windows machine for long term or for a short teaching stint or short development / testing gig, make sure you install at least one good printer driver. Sometimes a light weight driver like a free Acrobat driver isn't even good enough.

Using Custom Number Formats to Highlight Chart Axis | - Learn Microsoft Excel Online

Hui via has written a fun article on the creative use of Number formatting to create unique charts.

Using Custom Number Formats to Highlight Chart Axis | - Learn Microsoft Excel Online

He's also previously written about Data Tables (no, not the new Tables feature in Excel 2010)

Thanks Hui and Chandoo for freely sharing....

Friday, August 5, 2011

The problem with Microsoft Excel Charts

To put it succintly, Microsoft Excel charts, either work or they don't. That makes it hard to train, particularly as the learning process with charts is non linear and the user interface is non linear as well, there are several entry points to display a menu and not all entry points are obvious. This is particularly maddening considering that Excel is such a comprehensively well thought out and planned program.

For example, if you have seen several series of data points on a chart and they are drawn on different vertical scales, you have to search Excel Help (well, now, it's actually Office Help) on "secondary vertical axis"

And although it's easy peasy to use your Excel cell cursor to highlight a chartable range and press F11, again, Excel maddeningly makes glaringly bad assumptions when your proposed X axis labels are numeric - Excel assumes they are another data series. Tip: Put a leading apostrophe on your proposed X axis labels to force Excel to treat them as X axis labels. Or go the painful way of Select Chart Area > Select Data

Thursday, June 2, 2011

Cleaning data in Excel

In class, I noted that Excel is a gigantic toolbox of tools - there are so many ways to perform a task. Some ways are old (Excel is a long lived program), some techniques are freshly designed, some are easy but limited in power, some are powerful but need more thinking. Here is the Excel 2010 help page discussing different data cleansing techniques.

Friday, May 20, 2011

Wheee! Microsoft a one click solution to follow hyperlinks

For a long time, I've noticed the onerous security lockdown on Microsoft Office programs that pop up a warning message when you click on a hyperlink. This Fixit program defeats that warning.

in reference to: (view on Google Sidewiki)

Wednesday, May 18, 2011

Cascading Validation Drop Down Lists in Excel

Tony(?) asked in class about enhancing Validation Drop Down Lists in Excel so that he could use a cascading set of three Drop Down Lists. The article hyperlink explains how to do this.

in reference to: (view on Google Sidewiki)

Wednesday, April 27, 2011

A Word irritation that is easily fixed if you knew about the fix

"In Word 2010 choose File > Options > Advanced tab and locate the Cut, Copy and Paste group. From the Insert/Paste Pictures as dropdown list choose Square and click Ok."

in reference to: (view on Google Sidewiki)

Wednesday, March 23, 2011

I never thought I could go out with Nancy Davolio

You now you're a Microsoft Access tragic when you pine for Maria Anders of Alfreds Futterkiste or want to hang out with Nancy Davolio. Seems she's just had a new life and is giving us Access Love.

Wednesday, March 2, 2011

An Exhaustive Guide to working with odd Characters in Word

The people at the University of Reading have created this comprehensive .pdf file on the many ways one can insert a foreign character or symbol into Word 2007.

in reference to: (view on Google Sidewiki)

Saturday, January 15, 2011

Living and Breathing Excel

It’s Excel’s 25th Anniversary Year. As someone who is lives and breathes Excel, it’s great to see Excel greats from Microsoft speak. People like Charles Simonyi who gave us the Hungarian Naming Convention (and influenced not only Excel VBA but many other products) had a hand in Excel. And I remember this colleague called Mei Ling – she was into Multiplan as well – when I said why? It’s R1C1 – that’s dyslexically confusing compared to Lotus 1-2-3, Supercalc, Visicalc and now Excel’s A1 (grin). Little did I know when you program Excel in VBA, it’s often easier to think in R1C1 for cell addressing. Two videos from the Office team:


History of Excel Part 1

History of Excel Part 2 Tags: ,,

Saturday, January 8, 2011

Advanced Font Ligatures in Word 2010

I remembered demonstrating Garbriola aesthetics in a class and promptly lost my reference. Here is the how to.

in reference to: (view on Google Sidewiki)

Managing your Styles

I’ve been teaching more than doing recently, so this start of 2011, I am by chance, having the time to work with some documents with Word 2010. The management of Styles has been enhanced and modified to some extent. I found my favourite keystrokes to strip paragraph formatting Ctrl+Q and to strip direct character formatting, Ctrl+Space didn’t do it for me. Puzzled, I looked around at how things had subtly changed.

Really, if you are writing, you don’t want to be bothered with formatting. And yet when you cut and paste, the alien paragraphs indeed look alien. First thing is to check out the default Paste attributes:


The defaults are to keep source formatting – ask yourself whether you want to change that to Merge Formatting or to Use Destination Formatting. Sure avoids all that frustrating alien looking chunks and the obsessive compulsive behaviour in cleaning up after every Paste.

If you really need to strip Paragraph and direct Text formatting, you need to get the Style Inspector. One way to get it is to click on the Styles Pane Dialog Launcher in the Ribbon – Home Tab. Then click on the icon exposing the Style Inspector. If you are going to use it frequently, consider dragging it to the left or right so that it becomes a static vertical pane (non floating).


Then all you have to do is to place your cursor appropriately in the document and hit the Reset or Clear buttons. There are four of them in All.

Notice the Clear All button. By the way, in the Styles Pane there is a Clear All pseudo style. I did try that, it didn’t seem to deliver the goods.

Thursday, January 6, 2011

PowerPivot demoes on YouTube

Good ol' Bill Jelen shows PowerPivot with 1.8 million records. Other PowerPivot videos on the right vertical panel in YouTube. He shows the automatic match without needing to write VLOOKUP.

in reference to: YouTube - PowerPivot Mashup (view on Google Sidewiki)

Wednesday, January 5, 2011

We're seldom all satisfied

Here we are, Tables in Excel, in 2010, version 2 and it's got even better. But reading responses from users, people are seldom satisfied with anything, all together.....

in reference to: Table Improvements in Excel 2010 - Microsoft Excel 2010 - Site Home - MSDN Blogs (view on Google Sidewiki)