CRM 2011 now works with SharePoint online

I don’t usually blog unless I have something original to say, and try to avoid becoming just another echo in the blogosphere, but felt this announcement is important enough to merit it.

You can now properly integrate CRM 2011 with SharePoint online – in other words the SharePoint list component can be installed and configured properly and the previous problem whereby you could not get the SharePoint online server to serve up .htc files which are a vital component of the “seamless” document management experience enjoyed by on-premises customers has been resolved.

This also means by extension that you now use CRM online with SharePoint 2010 online (eg via Office 365) for a totally cloud-based setup. Note this is supported for SharePoint 2007 and 2010, but only 2010 gives the completely integrated look and feel inside CRM.

Thanks to Donna Edwards for tweeting this article by Eric Boocock: CRM Integration with SharePoint online is here

Previous problem is described and documented very well by Jukka Niiranen here: Office 365 launches without Dynamics CRM integration

More information on how to setup SharePoint to work with CRM 2011 is on MSDN:

Advanced Office Documents 2010 Edition by Stephanie Krieger

It took me a while to realise that when Stephanie Krieger said her new book was on the way, it was not necessarily going to be published under a similar title to her previous one “Advanced Office Documents 2007 edition”, in the MS Press “Inside Out” series.

So I’ve only just got round to finally ordering Documents, Presentations, and Workbooks: Using Microsoft Office to Create Content that gets Noticed which is the updated version.
Not the snappiest of titles, and if it is anything like her last it should have really been called something like “How to make Office 2010 really rock”.

I’m hoping it will be as brilliant and have the same deep content as the previous one, which certainly taught me loads about the packaging and XML structure of the new document formats, as well as some great stuff about using content controls in Word. If I get time I’ll do a proper review when I’ve worked my way through it.

Office 2010 Service Pack 1 – sp1 download available

Office 2010 logoLast month I wrote about the planned availability of Service Pack 1 for Office 2010 and SharePoint 2010 for the end of June. And it’s here!

You can now download the whole service pack file as a self-extracting executable and simply run it to install, or you can use Windows Update, where it is listed as an Important/High Priority update (rather than critical or security) for you to manually install (after 90 days this will change to an automatic update if your system is configured for that). At the moment my 32 bit install claims this would take 409 MB via Windows Update compared with only 361 MB for the full exe package download.

Even if you only have 1 machine to do, you will save marginally on the file size if you manually download Office 2010 sp1, and then of course you will have the file to use again on any other machines that need it – if like me you are the de facto IT support for family and friends, this can be quite useful.

A few key changes relating to other products are that Outlook 2010 sp1 will fully support the now-released Office 365 online business applications suite, while SharePoint 2010 will support SQL 2011 and has improved support for users of Internet Explorer 9.

So, there’s lots of information about this important update, as well as the downloads themselves, so let’s dive straight in with a load of links to the things you probably want to get hold of straight away.
Find out more about Office 2010 and SharePoint 2010 service pack 1 downloads, resources, and information »

Producer for PowerPoint

PowerPoint 2010 logo

The Microsoft Office blog has an article about Producer for PowerPoint, as well as links to the download page, and importantly to the Office Animation Runtime which you will need if you have PowerPoint 2010 (previous versions installed this along with the application, whereas 2010 does not). What is strange here is that the download page describes this as version 2 with a release date of 29th April 2011, yet the actual download page and file is identical to the version released and announced at the beginning of May last year.

The previous release was really a bug fix version which sorted out compatibility for Office 2007 and 2010, and there were vague claims that there would be new features in some later release, although as always according to policy there were no specifics about software in development.

Producer is a great way to turn a presentation file into a polished multimedia show which anyone can view using their browser. This is great for e-learning, tutorials, or any situation where you want to take something which would normally be delivered in person and make it available to a wider audience.

Oddly enough the download page refers to this as version 2, but the program itself claims (through help > about) that it is build 3.0.3012.0, but the MD5 hash for this file is identical to the year-old one. I’ve had a couple of problems with it – for example if you delete a load of slides from the timeline it expands the last one to fill up the space, and when you try to shrink it back down it takes while for no obvious reason, in my case chewing up one of my four processor cores flat out for a couple of minutes (tip: only add slides when you know you need them rather than all at once to avoid this problem).

Office 2010 file viewers

Office 2010 has now been released, so inevitably some early adopters (like me) will be deploying this in their businesses. If they are your suppliers, customers, partners or just other folks you know, they might want to share their files with you. So how can you read these documents if you don’t have this latest greatest version yet? There are various free options available to you to view them, depending on which version (if any) of Office you have.

Find out what your options are for different versions of Office»

Office 2007 sp2 Group Policy ADM and ADMX files and OCT available

It’s been a while since Office 2007 service pack 2 came out, but now you can get the files you need to successfully administer this, using Group Policy to apply settings from the ADM or ADMX files, or using the Office Customisation Tool (OCT)..

This Technet page has more information including some important details about making sure to reset some of your policies before replacing the ADM files, as you won’t be able to edit them afterwards:

If you have previously configured any of the Group Policy settings affected by this update, you must set those policy settings to their Not Configured state before you remove the previous 2007 Office system ADM files and load the updated version 3 ADM files. This removes the registry key information for the policy setting from the registry. This is because if an .adm file is removed, the settings that correspond to the .adm file do not appear in Group Policy Object Editor; however, the policy settings that are configured from the .adm file remain in the Registry.pol file and continue to apply to the appropriate target client or user. This also applies to any policy settings that you had previously configured that are listed in “Removed settings” later in this article.

You can download the Administrative Templates and OCT in a self-extracting exe file. Included are ADM, ADMX and ADML files in various languages (English, French, Spanish, German, Italian, Japanese, Korean and a couple of flavours of Chinese).

Also has the OPA files and a settings reference, but this other page claims that this is the definitive version of the Office 2007 GP and settings file. I can’t tell the difference – they are the same size and have the same number of rows on the list pages, and have identical MD5 checksums, so they are the same file.

I suspect this was a newer version than the old version in the old download before the newer version superseded the old version so it is now the current version. Clear as mud?

Anyway, most of the focus of these is on fixing a few broken things and targeting settings relating to Open Document format files (making it the default for saving, or blocking it being used at all, that sort of thing.)

Happy policy making!

Office 2010 first thoughts

Well, there’s some information finally coming out about Office 2010 and some of the features we will hopefully be seeing in the final release version next year. As the Technical Preview gets released to an invited audience only at this stage, there aren’t loads of sources of details, but a few places are showing off some interesting ideas and if you watch the videos carefully and look closely at the screenshots there are nuggets to be found.

If you want to be considered for the technical preview yourself, you can still sign up via the “Get a pass” link on the main “launch” site at Office 2010 – the movie. This site started out just hosting a teaser movie but now has a look and feel similar to the new “Backstage” interface which has been added to the Fluent UI to replace the current Office button menu to help you work with different aspects of your document in one place. There are a few videos posted on there right now, no doubt more to come soon.

Where can I find out more?

There are some useful overview documents on the Microsoft PressPass site, including an Office 2010 FAQ which covers a number of things, notably an outline of which products will be included in which versions of the suites available through retail or volume licensing. The oddest thing is that the various press releases available here are all Word .doc documents. Not a universal format like PDF. Not even Microsoft’s own portable format XPS. Not Word 2007 DOCX (probably a good idea not to assume people would already be on board with that, even with the compatibility pack for older versions). Other documents linked from that page give more detail for each of the products individually, but only at a brief marketing level, nothing too technical.

What are the biggest changes?

The most obvious change across the Office system as whole is that all the applications will now have the fluent UI and ribbon, which has also had slight facelift – they have removed many of the borders round buttons, reducing the visual clutter and “flattening” the overall effect (almost exactly what they did in the evolution of the toolbar from Office 97 to 2000). Selected or active options still appear to have borders to make them clearer. When you have additional context-sensitive tabs appearing in the Ribbon, the coloured highlight above them seems to be bolder because it extends from a solid colour at the top of the title bar fading out as it goes down into the Ribbon tabs area, rather than at the moment where this is only visible in the title bar area and fades quickly upwards. This may make the additional tools more obvious to new users when they need them, and help distinguish between similar items by getting used to the colours used.

The other big news items are the introduction of browser-based document viewing and editing (discussed below), and the availability of a 64-bit version of all the products (as well as 32 bit for legacy compatibility). This may provide some speed and productivity benefits to those who have appropriate hardware and OS to take advantage of this, use more memory and so on. Larger Access models might make more sense, but Excel spreadsheets of over 2GB? Hopefully not too often. I do know some people who could probably build PowerPoint shows that big though…

Read more of this post

Is the Office button a menu or a dialog box?

Another of Simon’s excellent posts about the Ribbon and other parts of the Fluent UI in Excel 2007 has prompted me to respond. Read the ribbon file blunderfest, where Simon says (I snipped a few bits out here for brevity, and the bold is mine):

I already mentioned the lack of file open icon, and previously I have talked about the ridiculous blob. And the initial flashing they had to incorporate to tell us its a button. But when you actually get closer it just gets sillier – I really wouldn’t have thought that was possible!

When you click and look, if you decide to cancel and move to the traditional cancel location (lower right) and click that button, does it close the file open dialog/ribbon? Or does it close Excel?

Everyone I have asked (and me) has accidentally closed Excel numerous times before eventually learning that this particular piece of the interface is not ‘normal’. In fact to cancel that thing you click anywhere else in Excel – and Excel ignores the click but closes the dialog! How ridiculous is that?

They have created a thing that is not as powerful or controllable as a dialog, but is too big and intrusive to be a menu or toolbar so they butchered an existing UI concept – the click away to cancel menu concept to work with this quasi dialog. But dialogs never worked like that before or in other applications. So now Office is the most friction-full application in the widows world (excluding perhaps Ulead products).

So, does the Office button bring up a (poor) dialog, or is it just a menu?

Sorry Simon but I have to disagree with you on this one (I seem to recall being told I was the voice of balance on smurfonspreadsheets by someone…).

Just because you think it’s a dialogue and call it a dialogue does not mean it is a dialog or should behave like one. Shredding a straw man / ribbon does not make a valid argument. To me it looks and behaves pretty much like I would expect a menu to behave:

Read more of this post

Excel cell styles – useful feature or waste of ribbon space?

I agree with Simon in his article about the usefulness of cell styles in Excel, where he says:

Styles in Excel are one of those things that sound good in theory, but are significantly worse than useless in reality. In an isolated world they may work but as soon as you start copying a pasting between workbooks…then you get a right royal style mess.

Cell styles as a concept seem pretty weak to me. The built-in ones are hopeless; I know hardly anyone (actually no-one that I could name right now) that uses them.

I have recently done some extensive work for a client on a set of templates, themes, etc for the whole Office suite. For the Excel templates I included some cell styles to make it quick to format things in “corporate” colours for headings and so on (as well as default table styles for the same reason). This provides user convenience and helps them create more consistent documents with more of a “branded” feel to them.

As to imposing a regime of “pink means bad” and “orange double underline means linked” (linked to what?), no chance.

Why styles don’t address the real need for good formatting

I teach students on my Excel training courses that formatting of spreadsheets should be used for three purposes:

  • to highlight (data outliers; estimates as opposed to actuals)
  • to group or associate data together (months in the same quarter or year having a light shaded background say, next group no background; using matching colour for axes and lines in a two-series chart with two different scales)
  • to separate data by category or type (line above the first month of a new year; making the title row bold)

These principles of using formats to help interpret the data, rather than help it look pretty tend to get people focussed on the task rather than the appearance. The built-in cell styles only seem to address the concept of highlighting, rather than being useful for grouping or separating. The highlighting they provide seems arbitrary at best, and quite likely to cause headaches with some of the colours involved.

Read more of this post

Changing many cells in Excel to recalculate new values after VAT changes

So you have a spreadsheet with lots of values in – future monthly invoices for service contracts, say. Actual values, not calculations which multiply up by a VAT rate stored in another cell, or a named range, or even as a fixed number in a formula. And the Chancellor of the Exchequer just announced that the VAT rate (sales tax for our colonial cousins) has changed so all your values are now going to be wrong for the next twelve months.

What can you do to change many cells at once by a specific amount?

A few approaches spring to mind, depending on the scale of the problem and the structure of your data.

Read more of this post

Microsoft Certified Application Specialist times five

MCAS logo I took five MCAS exams on Friday and passed them all. Some were easier than others, as always, but overall I found them a lot less stressful than when I took four on the same day to get the Microsoft Office Specialist:Master qualification.

Overall I like the way the Office exams work – the real application (minus the help!) running in the top half, and the questions at the bottom. Each question has a few tasks to complete, and you are measured on the end result, not how you got there.

This is a much better test of real-world ability to use the software than any multiple-choice questions can ever hope to be. Yes, it means that you could take a few wrong turns, and click on some irrelevant buttons before finding the thing you were looking for, but you can do that in real life too. The exam is limited to 50 minutes, so you can only afford to do this on a handful of questions, and you need to be able to make up the time on other questions by reading it once and going straight to the correct feature or function.

Read more of this post

Using Field Chooser to find out when an Outlook appointment was created

Ewan Dalton posted a tip for finding when an appointment was created on his blog “The Electric Wand”. This involves dipping into the developer tools to have a look at the actual fields that Outlook / Exchange uses to store the data about the calendar entry, as opposed to the standard stuff that gets displayed through the default form view.

However, he posted a comment a day or so later with a much faster method which is probably less scary to the average user (no mention of words like “developer ribbon”, “forms” and so on). Simply using the field chooser in the search results window means that you can see the created date (and any other additional information you want) at a glance. I thought it would be useful to expand this and give a quick tutorial, since being familiar with the Field Chooser in Outlook is useful in lots of other ways such as:

  • You might want to see the size of emails so you can sort the large ones to the top to delete first, reducing the size of your email file the most amount with the least effort
  • Maybe you have filed sent and received items together which relate to a particular topic or project, and you want to show both the To and From fields in this folder view
  • It is easy to accidentally drag and drop a column heading away which removes it completely, so you need to know how to get it back

So, let’s have some show and tell:

Read more of this post

Great keyboard shortcuts from the Visio Guy

I love using keyboard shortcuts to work more efficiently, especially compared to using the trackpad on my laptop in a cramped or shaky environment like on a bus or when I’m driving (joke!)

I’m currently in the process of updating my shortcut key handouts which I give out to delegates on my MS Office training courses. I’m always finding new key combinations to use, but I try to make sure I teach people the most useful ones based on three criteria:

  1. Does this shortcut do something genuinely useful which people need to do frequently or repetitively?
  2. Is the key combination easily memorable? (Ctrl-B is fine, but Ctrl-Shift-Alt-F7 is less easy to recall when you need it!)
  3. How ‘standard’ is the shortcut across different applications, especially within MS Office?

Visio is an application I use quite a lot but would not really consider myself a “power user” (I don’t create and edit my own shapes, for example). I find it really straightforward to use and great for doing office layout plans, network schematics, and data or process flow diagrams. However, I was amazed to see how many keyboard shortcuts and keyboard / mouse combinations I was missing out on when I read this article yesterday over at the Visio Guy blog:

Work Faster With Our Top Visio Keyboard Shortcuts

Some of these I was already using as they are the same or similar in other applications, but I could have saved myself loads of time over the years if I had known how to do this to draw out a region to zoom to:

Zoom to Region: Ctrl + Shift + Left Mouse-drag

You can specify exactly where you want to zoom with this command. Press the Ctrl + Shift keys together, then hold the left mouse button. You can now drag a net around the area that you want to zoom. Visio will fill the window with the region that you specify.

What are your favourite shortcuts for getting round applications more quickly?

Excel Hacks – David and Raina Hawley

Excel Hacks – 100 Industrial-Strength Tips and Tricks

Authors: David and Raina Hawley

Publisher: O’Reilly

Excel hacks book cover

Suggested Publisher Price: $24.95 US / $36.95 CDN / £17.50 UK

ISBN: 0-596-00625-X Softcover, 284 pages

Excel has fundamentally changed the way we’ve related to numbers for over a decade, but much of its power remains hidden.

Diving beneath the surface of Excel requires looking at features in unusual ways, but offers great rewards. Excel Hacks helps you leapfrog most of the preparatory work of understanding how it all works and what lives where, taking you straight to a set of immediately practical tools and techniques for analyzing, processing and presenting data.

Read more of this post

Group Policy templates and references for Office 2007

It took a while but eventually Microsoft got round to providing the Group Policy administration templates for Office 2007 in ADMX format, so they can be used properly with the Group Policy management tools in Vista and Windows server 2008. By properly, I mean using a central store and having the option to use ADML files to view and edit policies in an administrator’s preferred local language. You can get the ADM, ADMX and ADML files for Office 2007 in a single download here which is a self-extracting file that creates a folder structure containing all the relevant files.

This also has the bonus of including the Office Customisation Tool (OCT) which you can use to create an MSP file to customise a centralised network installation of Office for new installations, upgrades, or reconfiguration. You can find out more about the methods for customising Office 2007 setup files here and specifics about the OCT here. In addition the download extracts an Excel workbook “Office2007GroupPolicyAndOCTSettings.xls” that provides information about the 2007 Office release Group Policy settings and OPA settings, making it clear what can be pre-customised at the point of installation and what can only be set through policies.

You will probably also find the Office 2007 settings reference file useful. This is a comprehensive reference for all the settings in the GUI for Access, Excel, Outlook, PowerPoint and Word 2007. This gives the equivalent UI path in 2003 (where there is one), the default setting, what choices can be made, what policy settings exist and which registry keys those change. A very helpful file for understanding how to customise the user experience, and deciding which parts to do through policies and which settings are better left to users (and perhaps prompting you to educate them about the usefulness of some of these).

Excel 2007 calculation bug fix released after two weeks

A fix for the Excel 2007 calculation bug affecting results around 65535 and 65536 has been released in the last few hours. The Excel team blog post says:

As of today, fixes for this issue in Excel 2007 and Excel Services 2007 are available for download…We are in the process of adding this fix to Microsoft Update so that it will get automatically pushed to users running Excel 2007 or Excel Services 2007.  Additionally, the fix will also be contained in the first service pack of Office 2007 when it is released (the release date for SP1 of Office 2007 has not been finalized).

Microsoft knowledgebase article KB943075 discusses the fix and gives the usual details for what versions and sizes the updated files should have after the fix. The version number of Excel.exe is altered from 12.0.6024.5000 to 12.0.6042.5000. Now read that again – yes, easy to miss the difference from ’24’ to ’42’ if you look too quickly. (NB: you may have a different version, mine is at 12.0.6024 after installing the security update as per KB936509, as far as I can tell.)

The download for the fix for Excel 2007 (33Mb exe file) is linked from the Excel team blog as well as from the KB article. The blog post also has links for Excel Services 2007, both 32 bit and 64 bit.

Excel 2007 bug shows wrong answers to simple multiplications

This is a follow-up post to my earlier one about a bug in the way Excel 2007 displays the results of certain calculations. Read that one first of you have not already done so.

A few people in the comments thread in the Excel team blog post about the bug seem to have some misconceptions about the seriousness of the problem. Some have asked how often it is likely to come up, implying that they think it is vanishingly unlikely. This seems to be particularly those who have misunderstood that the example of 850*77.1 is only one simple example which is easy to remember and to type, but there are several more simple ones as well as thousands of other combinations which lead to the buggy result (due to floating point rounding errors in the calculation hitting a result which is sufficiently close to 65,535 to cause the false display of 100,000). Nine examples are shown in the screenshot below, and in a table you can easily copy and paste to try this for yourself.

» See some simple examples and find out more about functions which reproduce the error, and which ignore it safely»

Excel 2007 calculation bug displays apparently wrong numbers

A bug has been found in Excel 2007 and Excel services 2007 which appears to calculate certain results incorrectly. In fact, the stored value of the result is correct, and other calculations based on that result will calculate correctly. The only error is in the display of the number, not the internal calculation. This is, of course, still a problem for anyone who is reading the values on screen or on a printout, or exporting them to other programs (see further down in this post).

According to the article on the Excel team blog about this bug:

The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535.  The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn’t have anything do to with multiplication – it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance).  Further testing showed a similar phenomenon with 65,536 as well.  This issue only exists in Excel 2007, not previous versions.

Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).

So, it is important to note that most calculations which result in numbers near to or equal to 65,535 and 65,536 will be absolutely fine. It is only through some very specific oddities about how floating point numbers work that you will get one of the 12 situations where this bug occurs. If it does, you will have cells that read “100,000” rather than the correct answer. Anything else in Excel which you base on those cells will be correct.

You can add to them, multiply by them, show conditional formats such as colour scales or icon sets, even draw charts with those values and Excel will correctly handle the real, underlying value and not the displayed one. Macros or external programmatic methods of retrieving the cell’s contents also return the true stored value.

»Read the rest of the post to find out how this bug will bite you»

Industry Insiders article – Don’t Secure Your Documents!

An article I wrote for Microsoft’s Industry Insiders blog site has just been published.

This week I was asked by the IT support guy who works for one of my clients about how a user could put a password on a document. Since I am both their external consultant and their MS Office trainer, I was the right person to call.

To me this question is always a red flag as it implies that the user does not understand the places which already exist for them to save documents in such a way as to give access to the correct group of colleagues (or just themselves). My answer was therefore “I’ll show you how to do it for the sake of argument, but you should tell the user that they should not do this”.

Read the whole of this article about a proper approach to document security and avoiding mere security theatre.

The Industry Insiders site looks at various topics affecting corporate IT, with a slight lean towards information security, which is unsurprising since it is maintained by IT Pro Evangelist for Security, Steve Lamb (and evangelist manager Eileen Brown)

Huge PowerPoint files and how to avoid them

I have used PowerPoint for many years in a variety of job roles and it never ceases to amaze me that other people are able to create presentations which are, quite frankly, vast in their file sizes. There are several reasons for this, but the underlying problem is twofold:

a) users don’t think about file size until it is too late (when they realise they can’t email it, nor fit it on their memory stick nor even burn it to a single CD)

b) they don’t know how to avoid or fix the problem even if they did think about it

This means that many common causes of over-sized files go unchecked, files are used and re-used, and by the time you see there is a problem you have a huge clearing up job to do. Much better to tackle the issue at the source – when creating your presentation in the first place.

Read more of this post