Friday, December 25, 2009

Merry Christmas and a Properous New Year

I hope you’ve got Microsoft Office 2010 into your Christmas stockings. Or are enjoying Windows 7 – the family PC (with some quirks) is running fine and the aesthetics are supremely pleasureable compared to Windows XP.

Have fun fiddling – I know I am.

The Christmassy Spirit

Wednesday, December 16, 2009

Tuesday, December 15, 2009

A nice resource to restore default program associations

Sometimes we get carried away and install a utility that promises to do better than the default Windows behavior with documents or files but the utility does not work out. When we uninstall the utility, the assigned extension does not revert to default Windows handling of that type of file. Here is a bunch of registry fixes.

in reference to: (view on Google Sidewiki)

The refreshed look of Office 2010

Monday, December 14, 2009

Resetting Internet Explorer

Internet Explorer, at least on Version 7 onwards, has a Reset Button. It resets add-ons, toolbars and settings.

Pretty useful if IE has problems. Somewhere on the web, there should also be a utility to re-register IE dlls.... I'll update info if/when I find it.

in reference to: (view on Google Sidewiki)

Wednesday, December 9, 2009

In this world of Aero skinning and melange of colours

Jensen and his team can only give us bright pale blue, pale grey and black Office 2007 color schemes.

in reference to: (view on Google Sidewiki)

Commoncraft type video gone wrong....

Commoncraft make really clear and direct educational videos. Repurposing such a training video towards an unintended target - like reconciliating customer expectations can be easily misconstrued as dumbing down.....

in reference to: (view on Google Sidewiki)

Sunday, November 29, 2009

Repairing a Microsoft Office Error

I think I allowed a Microsoft Update for Office Live-Addin. Somehow my MSO.DLL was replaced - the original was dated 2008, the new one is dated 2009. MSKB articles talk about limited Windows users not being able to reach opa.dat and/or registry. Office Repair does not solve the problem. Turns out if you are a legitimate Office owner, it may be that your good MSO.DLL has been accidentally overwritten.

in reference to: (view on Google Sidewiki)

Thursday, November 26, 2009

By default Windows Explorer, many versions hides info

Like file extensions, some %appdata% and hidden files.

You really can't ignore file extensions - nowadays, you could have Word file that is either .rtf, .doc or .docx - learning how to distinguish file types by icons is really counter production.

Sometimes, you need to get to %appdata% to find a setting for a program or a template that you want to adfjust directly.

And with malware happy to put things here and there, you just want to see and know where weird files have secreted themselves.

This is a patient and comprehensive article.

in reference to: (view on Google Sidewiki)

Wednesday, November 11, 2009

When you need quick shortcuts to SendTo

Sometimes, you just need a quick shortcut to send files and folders to other destination folders or programs (like an editor). Add shortcuts to the SendTo Folder

Friday, October 30, 2009

Google Sidewiki for bloggers with accounts

This is really useful and simple. If you come across something to add to your blog, just write a note on the Sidewiki and in the same side panel, add the remark and the url to your blog.

in reference to: (view on Google Sidewiki)

Be careful

Don't click on the "OK" button unless you have made a backup copy of your .pst file. The Ok button runs the process without further prompting. Always nice to have a backup just in case.

in reference to: (view on Google Sidewiki)

Wednesday, October 28, 2009

Classnotes of the 28th October 2009 Access session


A welcome to all from the Monash University SDU class of the 28th October 2009. As I was teaching, I made some screenshots and wrote some notes. I’ve put them here.

I spoke of the free Microsoft online book – Microsoft Access 2000: Building Applications with Forms and Reports

I also mentioned the McFedries New Book: Microsoft Office Access 2007 Forms, Reports, and Queries


Wednesday, October 21, 2009

VBA Editor utilities

Working on an Excel VBA project for a client at the moment. Again, really relying on MZ-Tools for VBA to help write code. And Jim Rech's VBE Bookmarks utility sure makes bookmarks more useful than the standard VB IDE's Next Bookmark method.

Tuesday, October 20, 2009

Thursday, October 8, 2009

Was this helpful?


Microsoft Office Help has got incrementally better, but over the generations, it can be two steps forward, one step back. The new Office 2007 look and feel pastel and themed colours of Help are pretty, the writing may be more lucid, the different font sizes / styles may be more appealing. But still, the Bookmark This feature in winhelp classic never returned (although Visual Studio Help has this) and now, you have multiple entries in the search results with no differentiation of which one is the interesting entry.

Reblog this post [with Zemanta]

Thursday, October 1, 2009

Quick Tips for Office 2007

Heading Numbering appears to be through a different user interface in Word 2007. This seems the easiest and direct:

  1. Write some text
  2. Apply Heading 1, 2 or 3 using Styles
  3. Click on one of the Headings e.g. pick a Heading 1 paragraph with the cursor
  4. Click on the Outline Numbering button in the toolbar and specifically choose Heading Numbering icon

Word Heading Numbering


Ending your Powerpoint Show without a Black Slide – it’s in the Office Button > Powerpoint Options

end with black slide

Friday, September 11, 2009

Working smarter with email software

Microsoft Office Outlook

Image via Wikipedia

Yesterday, I was getting quite frustrated in Lotus Notes, trying to chase down one email item in the new threading feature. I was mouthing profanities until I realised that I had no hope of finding that email item. I had routed a duplicate of Lotus incoming items to my Yahoo inbox and was subsequently retrieving such items via the POP3 interface into Outlook. Why didn’t I use connect Outlook directly to my Lotus Inbox via Lotus IMAP4 emulation? Well, because the Lotus IMAP4 interface gave up on me some months ago. Never found out why from our sysadmin team.



Anyway, so here I am in Outlook 2007 hunting for another email item. Blast! Really hard to find. Even though I have just installed Microsoft Desktop Search 4.0 (which even searches non POP3 local stores, even IMAP4 transitory things.) Even though I had Categorised items (but obviously forgotten to categorise this one).

Soo, I switch the Inbox View to Arrange By: From. That looks half promising. But I can’t see the Preview and I have to scroll like crazy to get to another From: sender.

Lightbulb blinks on. View > Expand / Collapse Groups > Collapse All Groups

Aaah! Tags: ,,
Reblog this post [with Zemanta]

Thursday, September 10, 2009

The Excel Data Picker

I was leading a training session the other day, demonstrating in-cell validation using lists or number limits and Deepa asked whether she could drop down a date list, like we do in a custom list. I blinked and said, “you mean a Date Picker?”. Then I realised, it was a good request. I immediately pointed to the date picker living in Access 2007 cells (even though they annoy Pepe) and the new Word 2007 Date Picker Content Controls. But Excel cells? I muttered something about the richness and maturity of Excel and said I would have a look on the internet.

There it is, an effort by ISamRad – it’s offered plain, unwrapped, an .xla in all its glory (download it here). I just tested it with Excel 2003 and Excel 2007, both work fine. Sam is actually Sam Radakovitz, a program manager on the Excel team – he contributes to the Excel Blog.

Enjoy! Tags: ,,,
Reblog this post [with Zemanta]

Saturday, August 22, 2009

Microsoft - the love / hate thing

To be fair, it's any software and any software company. Microsoft just gets more flak because more of us use more of their products - statistically, Microsoft products dominate the marketplace and this causes anguish when what we hope is designed, wasn't designed that way.

Lately I have enthused over Outlook 2007 Categories - not just colour names, you can assign new Categories and rename existing Category tags so that they are more meaningful - like VIP, Must Do, The Boss, The Wife - you get what I mean.

One of the nice things about Outlook in most aspects is that if your machine dies, you can rescue or keep safe your stuff by copying the .pst file before hand. The .pst file contains your Email items, stored in whatever folder hierarchy structure you designed, your Calendar, your To Dos and so on. Except Category customisations. It appears they may be stored somewhere in registry like HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Categories

It's enough to make your cry.

Monday, July 20, 2009

Laying out the carpet for to recipients for your Excel files

I was looking at the MSKB 942385 and then downloaded the Office 2007 Service Pack 1 Changes_all.xls package. Irritating, that file is. Excel is now a great grid and table tool – it’s natural formatting, data handling (filtering, sorting) abilities exceeded Word’s model a long time ago. So whenever you need to deliver a dense grid of data, Excel is the way to go. Of course, Access would be as good but not everyone has Access on their machine. But, people still have a Word mindset – if you want to deliver for example 400 over rows of info to your recipient, leave that word processor approach of laying it out nice so that people can print (do you help them print?) but help them more pro-actively. Facilitate their hunting for gems in that 400 row grid, ensure that they can hit “print” and it prints well.

Download that file and play along with me. See what you can do to help the recipient along.

Suggested Best Practices

  1. Forget that danged banner proclaiming that the file is what it is – you can put a brief title in the Excel header / footer. Additionally, you can repeat the banner in the worksheet tab
  2. Provide Folder Path / Filename info as well as Page x of y in the Excel Header/Footer
  3. Ensure that the grid has a column of category names. Remove arbitrarily placed Category Headers from the grid.
  4. Put the data in an Excel Table. So that the recipient can sort/filter easily
  5. Freeze the column title / row titles. Dang! Frozen worksheets can’t go into the new Page Layout View.
  6. Set relevant Print Titles so that the paper follows your frozen worksheet titles.

Now, was it that hard to extend some courtesy to the recipient? What other tips for any Office program can you think of that will facilitate recipients?

Friday, July 17, 2009

The Excel 2007 .XLSB file format Tags:

The Excel Team Blog has this to say about the curious .XLSB file format.

Even though we've done a lot of work to make sure that our XML formats open quickly and efficiently, this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data, or that would require a lot of XML parsing during the Open process.

(In fact, we've found that the new binary format is faster than the old XLS format in many cases.)

Also, there is no macro-free version of this file format – all XLSB files can contain macros (VBA and XLM). 

Runtime performance - once loaded into memory, the file format has no effect on application/calculation speed


Reblog this post [with Zemanta]

Thursday, July 16, 2009

Interesting Business Intelligence Tool – Gemini

Just saw the YouTube demo of the Gemini project (SQL Server and Excel) handle 20 million data rows and related tables (on a netbook PC) in an impromptu expression, interactively, in a few seconds.

Wednesday, July 15, 2009

Put it in a table

Microsoft Office Excel 2007

Image via Wikipedia

Excel 2007 has enhanced the object formerly known as Lists (in 2003) – they’re now called Tables. Tables are now super cool, the greatest thing in Excel since, oh, AutoFiltering.

How do you make a Table?

I guess the obvious way is to Insert > Table.

But I much prefer placing your cell cursor on a cell in the midst of a dense rectangular shaped block of data cells and doing the Home > Format As Table action.

What can Tables do for you?

Let me count the ways

  1. A Table can have banded rows and/or columns. This colouring is independent of Direct Formatting or Cell Styles
  2. A Table auto-extends in columns if you type a new heading
  3. A Table auto-extends if you insert a new column or row in the midst of the cells
  4. An auto-extending table also extends any range names that you have assigned to column blocks or row blocks
  5. A table has a TableName and has ColumnNames which can be used in new Structured Formulae.
  6. If you type a formula in an empty cell in a column of the table, Excel 2007 tries to give you a Structured Formula (MyTable[ThisRow][Salary] * 9% instead of the classic A2*9%
  7. Completing a formula in an empty cell in a column of the table will cause Excel 2007 to fill the rest of the cells in that column with relevant formulae.
  8. A table does not mind you inserting a new row at the bottom of the table and it auto extends the banded shading as well as the Table’s cell addresses to that new row.
  9. A table acts as a a suggestion to be the source for a Pivot Table.
  10. A table overcomes the guessing glitch Excel 2007 has with regard column titles which look like numbers. For example if your column titles were CountryName, 2008, 2009, when you select the whole block to make an Excel chart, Excel 2007 thinks 2008 is a Datapoint and not a Data Series Name. So you then have to unconvince it by excluding the column titles of your block and manually editing the Data Series Name. Or maybe you have to apply Numeric Formatting as Text.
  11. A table supports a Totals Row which can have the aggregate statistic, normally SUM, AVERAGE, COUNT, etc…
  12. You can refer to totals of Table Columns by using the Expression fragment – SUM(TableName[ColumnName]) – the Table Name and Column Name appear in the autocomplete list when you progressively type the formula into the cell.
  13. A Table has the Remove Duplicates Command
  14. A Table has the AutoFilter drop down arrows in the column titles row – you can do all the AutoFilter activities

What you can’t do with a Table

  1. You can’t carry out the Data > Subtotals command in a Table. It’s greyed out. You can convert the Table back to a Range and then apply AutoFilters.
  2. You can’t use the TableName syntax in a Data Validation of a List. Is there a workaround?

Curious things about a Table

  1. If you change the Accent1, 2, 3… colours in a Theme, that affects Table Colours in an unexpected manner.
  2. If you want to use the Table smarts but don’t like the coloured banding and coloured borders in a Table, you can clear this colouring. There is one choice in the gallery of Table Formats called “None” but this choice is not visible on the Home Tab of the Ribbon. It is visible in the (Table Tools) Design Tab of the Ribbon – as well, there is a Clear All text item in that Gallery of Table Formats.
Reblog this post [with Zemanta] Tags: ,

Monday, July 13, 2009

Friday, July 10, 2009

Spotted Book: Business Analysis with Microsoft Excel

You can get the paper or the Adobe Acrobat version of Conrad Carlberg’s book.

Instead of teaching how to push buttons in Excel, he delivers working with Excel in the context of accounting and finance. Tags: ,,,

Spotted Book – slide:ology

I tend to browse the bookshelves when I shop. There are some interesting books out there. Nancy Duarte has a really worthwhile presentation book called slide:ology. Her blog features some extended content freebies which are worth looking at and downloading themselves.


Thursday, June 18, 2009

That pesky security alert message for external workbook references

Microsoft gets flak when there are macro virus alerts. Each iteration of Microsoft Office has toughened up default security. Excel 2007 now displays a security alert when you open a workbook which has external workbook references. MSKB 826921 explains the issue and workaround.


No need to work so hard. There is a deep menu nested way of switching off the alert – thanks to Lilith for telling us.

Office Button > Excel Options > Trust Center > Trust Center Settings > External Content > Enable automatic update for all Workbook Links (not recommended).

Yeah, not recommended but do it anyway – of course you should have a good, up to date, anti-virus system in place and running real time.

Don’t confuse this with:

Data > (Connections Block) Edit Links > Startup Prompt > Don’t display the alert and update links

Thursday, June 11, 2009

Pardon the Range Name

Range Names are a lot of fun. Lots of “A1” cell reference people don’t know about the benefit of using Range Names – but with Excel 2007’s new in-your-face Formula Tab with Name Manager, we hope more people will use this feature.

One wrinkle in an otherwise simple explanation of Range Names, is the scope of the Name. You see, there is a global-to-workbook scope and a local-to-worksheet scope. Here is Simon Hurst’s attempt at explaining it:

Names must be unique within their scope. So you cannot have two identical names in one sheet, but you can set the same name up in different sheets. This could be very

useful – a similar formula on several different sheets could refer to the range name defined for that sheet

You are also meant to be able use the scope setting to define the same name at local worksheet level and global workbook level. If you refer to the name on a sheet where it has been defined for that specific sheet, then the local reference will be used. If you refer to the name on a sheet that doesn't have that name defined locally then the global workbook reference will be used. If you want to refer to the global name from a sheet that has the same name used locally, then you have to prefix the name with the workbook name, for example: ='2007 range names.xlsx'!profit.

Training Matters has an interesting article on effective use of Range Names – and explains how to replace cell references in pre-existing formulae that didn’t use a Range Name previously

The Define Name option includes the Define Name and Apply Name options. Apply Name is unchanged from previous versions of Excel and allows names to be applied to existing formulae where they will replace any direct references to the named cells or ranges.

It is also possible to create dynamic range names. You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

Templates and the default document

There are good knowledgebase articles for templates used by Microsoft Office 2007 and 2003.

Did you know that instead of a single template location, there are now four? As described in MS KB 924460, they are:

  • The user templates file location
  • The workgroup templates file location
  • The advertised and installed templates file location
  • The non-file-based templates file location

The article is a bit too brief though. It does not explain for example that Excel responds to two global templates – a default template for the new workbook – book.xlt and a default template for the new worksheet – sheet.xlt. This is explained in HP052292861033.

It’s also interesting how the default font and font size in Excel is set. You can directly set it in Office Button > Excel Options in the When Creating New Workbooks section for new workbooks. Or you could go the book.xlt route. Or for the current workbook only, you can modify the Cell Style called Normal.

Numbers as Text in Excel 2007

It’s an old issue. Goes back as far as Lotus 1-2-3. Yes, the classic DOS edition. Maybe even to Visicalc but I’m not sure. These spreadsheet programs of course accept numbers and calculate numbers. Text can be secondary. But what happens when you have to enter leading zeroes in product codes or mobile phone numbers? The leading zero is swallowed up by the numeric expectation.

Excel 2007 has a nice solution. Thing is, it’s not phrased in help in such a way that easily comes to surface. Search Excel Help for Format As Text.

For pro-active work, select a range of cells and apply numeric formatting. Yes, Yes. Numeric formatting. But you nominate the numeric format called Text. Guess what? Not only is the selection formatted as text (e.g. with leading apostrophe) but future data entry into that range will retain the text entry behaviour. So you don’t have precede your intended number with an apostrophe – and your leading zeroes are preserved, of course.

For post processing, your zeroes have already been sucked into the null bucket. So using this technique will not restore them. Use the TEXT function to manufacture the leading zeroes in a new cell.

Tuesday, June 9, 2009

Graphics and Pictures in Access 2007

We sometimes discuss how it’s not a great idea to store BLOBs / digital pictures / graphics within the Access database file itself. The only advantage is that you keep all the (hopefully small) graphic files in one database file and it’s easy to transport the whole thing. The big disadvantage is that Access does not do anything or use anything with those graphic objects – the database engine does not index, search within or recognise faces (for example) within the digital pictures. In previous versions of Access, the situation was even worse, Access would manufacture several bitmap formats as per the OLE specification so a small JPEG picture would bloat up considerably once it was stored as an OLE picture. There are third party solutions but they expect you to licence and install .dlls on the participating machines. Here is an article describing the problems you may face and a comparison of the different techniques.

The redoubtable Larry Linson, and Stephen Lebans have various examples. See Stephen’s Picturebox , LoadJPEGGif webpages. Tony Toews’s famous website seems has an Image Handling webpage with resource links.

Northwind 2000 has an example with the Employees Form. Get it and take it apart.

Access 2007 has a new field type, the Attachment field type, which avoids this bloating, but you still fatten the Access database file with an “dead” matter.  Here’s an Office knowledgebase article "Attach files and graphics to records in your database

Wednesday, May 27, 2009

Access 2007 Resources for CAE Class 27th May 2009

Just a friendly hello to the class and to anyone interested in Access resources to follow up. Microsoft Access is a veteran program, bits have changed particularly from Access 2.0, but many of the basic fundamentals have been steady and reliable since Access 7.0 / 1995. So, whatever skills, knowledge and culture people have written about in books and on the web are still relevant to Access in 2009.

Access 2007 has of course a new look – the Office Button, the Ribbon, the Navigation Panel, but the fundamental features are still there.

Microsoft Access Resources:

Access USENET newsgroups of course have been there for yonks – you can tap on reasoning and ideas that were discussed more than 10 years ago. They are easily accessible now, via Google resources.

microsoft.public.access (often abbreviated as m.p.a.) is one group – but there are many subgroups under the microsoft.public.access tree.

comp.databases.access can be more roughhouse but lots of knowledge there as well.

Utter Access is a more moderated forum, again with lots of interaction and knowledge.

We discussed the Leszynski naming convention – here is a wikipedia link.


Saturday, May 23, 2009

Access 2007 Power Toys, well, nearly

Access 2007 has a host of new features, some of them long awaited over more than 10 years. A few features have been deprecated and lost. I don’t miss Data Access Pages at all, but the classic Database Container Window, losing that is really uncool. Yes, yes, yes, the new Objects Panel has some swish in operation and I like how it stays open to allow you to drag objects into the Designers smoothly. And initially, the collapse, expand is fine. In the long term, when a your database ages and you have heaps of objects, getting to one isn’t so fine and you can’t see remarks you’ve made on the objects in a grid view. Gunter Avenius has a lovely tool that looks and feels a lot like the old DCW called DBC2007. It’s a COM Add-In, programmed in .NET Framework 2.0 and is triggered from a new custom icon on the Home Tab of the Ribbon. Just lovely.

Not to forget one indispensible and free programming tool, the venerable MZ-Tools 3.0 VBA IDE enhancement.

Herr Gunter has also produced a WYSIWYG Custom Ribbon Creator for Access, Excel and Word. There is a limited capacity free edition and a paid edition. Garry Robinson talks about it in a Database Journal Article. If you don’t know much about custom Office 2007 Ribbons (I don’t have much experience), here’s an article and download on the Custom UI Editor Tool – Ken Getz, a veteran has a three part article on MSDN on Customizing the Ribbon. He makes reference to the 2007 Office XML Schema Reference. You’ll also want to read about the List of Control IDs, the Icons Gallery as well as the general overview of the Office 2007 UI.

Saturday, May 16, 2009

Access 2007 New Features

Reiterating new features for products can be a soulless exercise. Sometimes you brighten up when those features are accessible and relevant. Here's a concise and useful article at fmsinc on Access 2007.

Tuesday, May 12, 2009

TED Presentations

I’ve enjoyed a fair number of recorded TED presentations and thought their speakers were riveting. This is the first time I have seen the TED Commandments (graphic hosted by


Friday, May 1, 2009

Office 2007 Service Pack 2 followup

Good to know Office Watch has a bead on Service Pack 2 and are determined to track it’s health. They’ve summarised Microsoft remarks in their Changes and Problems post. I also noticed that Service Pack 2 does not come with revised Office Help files – but I do remember seeing Office Help updates once in a while via Windows Update. These new files though are dated 28th April 2009 which postdates SP2 release to distribution. Tags: ,

Thursday, April 30, 2009

Little things from training


It’s good to go to Office training. For several reasons – given the right environment and instructor, you get cross fertilisation of ideas. And it keeps me in a job (grin).

Tip 1: Someone asked about the keyboard shortcut in Excel to switch to the next Worksheet – it’s Ctrl+PgUp / Ctrl+PgDn.

Tip 2: In Outlook 2007, the message body editor is always Word 2007 and since it is that, it responds to AutoText and Building Blocks. However, it does not use Normal.dotm, it uses NormalEmail.dotm. Good thing is that the two dotm files are separate, bad thing is that the two dotm files are separate.

Tip 3: In Office 2007, you may have several documents open for cut and paste. Office has been moving towards the SDI (Single Document Interface) for sometime, because Internet Explorer popularised the simplicity of that notion. But your Windows Task Bar may be cluttered with other items or your document windows may be grouped by application, require several mouse clicks. Put the View > Switch Windows command on the QAT (Quick Access Toolbar). Better yet, if you are good with your keyboard, press Ctrl+F6 to switch between documents of the same application.

Reblog this post [with Zemanta]

Wednesday, April 29, 2009

Microsoft Office 2007 Service Pack 2 hits distribution

Came back from a day of providing training in migrating to Microsoft Office 2007 and switched my PC on. Whoa, a list of updates.

Service Pack 2 itself

The big one is Microsoft Office 2007 Service Pack 2. The list of fixes and changes are in MSKB 953195 . There is a detailed list of changes in the 2007 Office Service Pack 2 Changes Excel datafile.

So what’s to look forward to?

Access regains the ability to export Access reports to Excel – I seldom used this function because Access reports may have sub-reports and they don’t translate well but some users clamoured for a return of that function. There are also fixes to “issues that occur in the import data wizards, in report printing and previewing, in macros, in Excel integration, and in date filters”

Excel gets a debugged charting mechanism and the chart object model has been made available to Word and Powerpoint.

Outlook has slow startup and proper shutdown operations fixed (didn’t we suffer even in Outlook 97?) The Calendar Printing Assistant has an update.

Word, Excel and Powerpoint now open Open Office documents. In fact, developers now have a converter interface for third party file formats.

There may be some installation issues.

If your Windows Installer is out of date (see MSKB 942101) then you’ll have problems. If an Office program is running, ActiveSync is running, Communicator is running, Groove is running, Desktop Search Service is running, your machine may reboot unexpectedly (see MSKB 942995). There are some other issues that could impact successful installation. If you want to diagnose the installation problems (groan), see MSKB 954713.

If you install it and want to roll back the SP2 update, you now have an Uninstall Tool

Viewers Service Pack 2

Powerpoint Viewer has an update, Excel Viewer has an Update, Visio Viewer has an update

Office Compatibility Pack

In pairing with this, we have the Microsoft Office Compatibility Pack Service Pack 2 (eh, the Pack’s Pack?) for Office 2003 and other classic versions. Have a read of MS KB 953331.

Other product Office 2007 products Service Pack 2

Microsoft Project 2007 has an update.

Sharepoint Designer 2007 has an update.

The full Service Pack list is in MSKB 968170

Psst. (Did you know Wordpad in Windows 7 can open .docx and .odt (Open Office) files?)

Well, are you gonna wait for Woody to give you the all clear? Tags: ,,

Reblog this post [with Zemanta]

Saturday, April 4, 2009

Powerpoint and other Office Templates

Whilst reading Duarte’s blog entry, I was reminded of these templates that were made available some time ago, for Powerpoint 2007. They’re quite impressive and contain detailed notes on how to achieve the effects. Tags: ,

Monday, March 30, 2009

Microsoft Access VBA

I just had a look around at some Access VBA books. I would recommend some human interaction, tuition so that you get the “inside story” on why one would use one approach or another as supplement to these books though.

Access 2007 VBA Programmer's Reference (Programmer to Programmer) (Paperback) seems to be a well documented reference.


Automating Microsoft Access with VBA (Business Solutions) (Paperback) by Mike Gunderloy and Susan Sales Harkins is probably Mike’s last book on Access. Comprehensive.

Beginning Access 2007 VBA (Paperback) by Denise Gosnell is more brief.


Access 2007 VBA Programming For Dummies (For Dummies (Computer/Tech)) (Paperback)  should have a fair number of tips, but it is not intending to school you in VBA.

Helen Feddema’s Access 2007 VBA Bible: For Data-Centric Microsoft Office Applications (Paperback) appears not so much about Access and VBA but about invoking Outlook, Word, Excel and making them play with Access.

Expert Access 2007 Programming (Programmer to Programmer) (Paperback) is for more esoteric manipulations.

Thursday, March 19, 2009

Snippets of Microsoft Access Tutorials

Whilst training, I may show and walkthrough a feature that is relevant to my class but don’t have the resources or time to produce a documented tutorial. Microsoft is doing it’s part in providing activity specific tutorials.

Using queries as a datasource for Forms – In introductory classes we tell people that a Form can display records from a table and a query (and optionally an SQL string). You can create a Form by walking through a wizard. If you want to set up a form manually, well, this is the tutorial.

There are two ways to create an Access Switchboard – i.e. a Form festooned with buttons that acts as the main “menu” to your system. One is to create a blank form and put buttons on yourself. The other is to use the famous Switchboard Manager. Tags: ,
From Melbourne

Wednesday, March 18, 2009

Luke Chung speaks on Microsoft Access on MSDN

Microsoft Office Access

Image via Wikipedia

Luke has been a hero of mine. He sure looks a lot younger than I feel, his hair is still black! I love his FMS Inc. product – Total Access Analyzer.

Luke’s Channel 9 MSDN video interview here.

He writes about the Top Features of Microsoft Access 2007 that aren’t available in Prior Versions and Microsoft Office Access 2007 Form Tips.

Reblog this post [with Zemanta]

Saturday, March 14, 2009

Around the Office

Microsoft PowerPoint

Image via Wikipedia

There seem to be more businesses and government departments migrating to Microsoft Office 2007. Although Microsoft Office and Windows are intertwined products and Microsoft uses this as a marketing approach, it’s my perception that Microsoft Office has overcome a lot of trials and tribulations about The Ribbon interface, picking up small and big business customers, inspite of the lacklustre reception of Windows Vista. Maybe that’s why Sinofsky, the Office head in Microsoft is now helming Windows.

Powerpoint 2007 is to me, a real re-architection of classic Powerpoint – full, redblooded restructure. The completely new Slide Master, Slide Layout framework is very welcome but also requires some double takes as one grapples with how Slide Headers and Footers work. Why did they nuance Headers and Footers this way, when they could re-jig the Slide Master, Slide Layout concept? Maybe they ran out of time or they had a roadblock in the idea?

My good friend Gerhard also notes that Microsoft Word’s Headers and Footers User Interface has been re-jigged. But that’s more User Interface – substituting the classic Header / Footer editing pane and icon bar with reworked QuickParts (which were there as AutoText in Word classic). You can still, if you knew / know how, carry out classic Word Header / Footer production using Word Field Codes.

Meanwhile, on a side note, the Tutorial Blog has a tutorial on using Hyperlinks in Powerpoint 2007.

In the Simple Thoughts blog, amongst the 10 MS Excel Hacks and Tips, there is one that would quickly become a favourite with Excel users:

9. How to Paste Column Widths

To copy paste the cells you use Ctrl C and Ctrl V. But this doesn't paste the column widths that can accommodate that data. So what you do

• Use the normal Paste option to paste in the data and cell formats
• Right click on the new range of data you have pasted
• Click the Paste Special, and select the Column Widths radio button.


Related articles by Zemanta

Reblog this post [with Zemanta]

Wednesday, March 11, 2009

What about VBA?

I have an eye on Dick Kuleiska’s blog. He just posted a quick one on the future of VBA. There were some informed responses, I thought, might as well weigh in with my 50lb boots….

Read my response here.

Thursday, February 26, 2009

The QuickPart formerly known as AutoText

Outlook 2007

Image by Paul Watson via Flickr

Client called today – she wanted her AutoText, formerly in Outlook 2003, to be available in Outlook 2007. Hmmm. I was puzzled. I had already copied her NORMAL.DOT over to where Word 2007 expects it to be. I don’t normally use AutoText in Outlook, so I had a close look. Outlook 2007 does have QuickParts since Word is inseparable from Outlook in Office 2007. But the QuickParts button isn’t really healthy. It does not appear to pull down a gallery (or maybe I haven’t defined enough clips there) and there is no Organize subcommand. All it does is allow you to Save a Selection to QuickParts (assuming you are in the body of the email and have selected a chunk of stuff).

Turns out, when Word is the servant of Outlook, the AutoText is taken from a Word Template called NormalEmail.DOTM rather than the classic NORMAL.DOT when Word is its own master. So, you manage the AutoText clips using Word, then copy the whole file and replace NormalEmail.DOTM.

How to get to the folders for all this? The nest of folders are:


for, NormalEmail.dotm

%appdata%\Microsoft\Document Building Blocks\1033\ for

Building Blocks.dotx

See Serdar Yegulalp’s article for more details. Incidentally Sue Mosher links to the seminal document Reading and Editing E-Mail in Microsoft Office Outlook 2007, an 840k Word document (not docx, not pdf, fellas, it’s a classic .doc). She’s written Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators

By the way, did I tell you the Word Ribbon when enslaved by Outlook is grossly mishmashed? Why else would someone put the Zoom command in the Format Tab (and of course suppress the cool zoom slider that is at the bottom right of the Word window?

From Drop Box

Reblog this post [with Zemanta]

Tuesday, February 24, 2009

Sunday, February 22, 2009

Pressing Enter, Enter

I was having a vigorous discussion with a peer on the architecture of Microsoft Word 2007 and how I had been slapped on the wrists in the mists of the 1990s when I pressed Enter, Enter to start another paragraph in Word/DOS. Said peer had not heard of that guideline and had pressed Enter, Enter all his life.

I knew I had seen a classic article about encouraging people not to press Enter, Enter. Did a web search and up pops Shauna Kelly’s article.

Note: Word 2007 default Normal Style in new documents off default normal template (the template you use when you are not using a template) has inter paragraph spacing as encouraged by Shauna and I. Word previous versions, don’t.

Are you an Enter, Enter person?

Reblog this post [with Zemanta]

Monday, February 16, 2009

Microsoft Project for “non pros”

Microsoft Project 2000, showing a Gantt chart

Image via Wikipedia

Gerhard told me about the combination view / split view for the Gantt Chart. We demonstrate and coach people on it all the time. It’s a must-have view when assigning Resources to Tasks. It is less modal than the Task Information Dialog and workflow is smoother than popping the dialog up every few minutes. Someone in Microsoft thinks it’s for “Pros”. Have look at the tutorial video. Soon, you’ll be managing Views “like a Pro”. I wonder what the other people who use Microsoft Project are called. “Amateurs?” “Kids?”

Reblog this post [with Zemanta]

Friday, February 6, 2009

Styles in Excel

A good article about Excel Styles with sample VBA code to better manage them. Too few (I think I am the only one I know who regularly uses them) Excel users understand and use them to advantage. Office 2007 with the new Styles Gallery in the Ribbon should aid discoverability but the feature has to be gospelised.

Pointy Haired Dilbert has some unusual Excel workbooks

He's got a web query demo for stock monitoring as well as a twitter client in VBA.

Lots to check out.

Friday, January 30, 2009

A quick heads up on Excel stats and data analysis books

Albright, Winston and Zappe write academic quality serious books for learning and using Stats, Maths in Data and Modelling. I've picked up an old first edition copy, missing the CD - and visited their website and their catalogue at Thompson / Cengage. Enjoy!

Thursday, January 29, 2009

The Intricacies of Powerpoint 2007

Office Button in Microsoft PowerPoint

Image via Wikipedia

I used to teach Powerpoint 4.0 – it used to puzzle me why Training Providers would schedule Advanced Powerpoint classes. To me, there really wasn’t that much in the program, unless you wanted a degree in making art shapes with Word Art and compositing vector shapes.

Powerpoint 2007 is quite different. It has evolved substantially, this time, leading and encompassing Office 2007 Themes. And not well documented in approach and techniques.

Working with the footer textboxes

In PPT 2003, you can do this in one of two ways. The intuitive way for someone who knows about Powerpoint Masters is to switch to Master View and type into those textboxes. In Powerpoint 2007, the Master has been feature enriched with Layouts. But those textboxes are greyed out. You can type into those textboxes but they don’t appear when you switch to Slide View. You can Insert > Header / Footer and work with the textboxes that way, but it’s counter intuitive to the Powerpoint Masters and Layouts structure. An activated footer text box still looks greyed out in the Slide Master – I can’t see any properties hint that tells between a footer textbox that displays or not.

Applying A Slide Template

  • You can apply a Slide Template at the time you start creating a new Presentation file (Office Button > New). However you must have placed your own Templates in the Templates Folder otherwise you won’t see them at this stage.
  • You can apply an Office Theme from the Themes Gallery. What is not obvious is that you can choose other documents which are not in the Themes Gallery. These documents can be anywhere on your file system and can have .pot, .ppt in addition to .thmx – look at the More.. pointing arrow.
  • You can Insert a New Slide. And you can Re-Use slides from other presentation files – however, if you Re-Use slides this way, it brings the content of the slides, it does not bring the Theme elements

Powerpoint 2007 has grown horns.

Reblog this post [with Zemanta]