Short takes: Excel tips to display the worksheet name and validate data; editing Microsoft Project files stored on SharePoint; and an XPS to PDF conversion service

Posted on By Mark Wilson
This content is 9 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Another collection of mini-posts based on recent IT trials and tribulations…

Excel tips to display the worksheet name in a cell and to validate data

Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet.  I’m quite pleased with the result but I did need to work a little on some of the tricks.

Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.

The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

Incidentally, I also needed to look something up that I’ve blogged about previously: if a cell shows the formula rather than the result, check the formatting is General and not Text.

Editing Microsoft Project files stored on SharePoint

Much as I try, it seems I can’t avoid working with Microsoft Project. Unfortunately, when working directly from SharePoint the files are opened read-only. The answer, it seems, is to work on a synchronised local copy – as described by Victor Butuza on his Microsoft Office SharePoint blog.

XPS to PDF conversion web service

Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files.  There’s an API for those who want to make the conversion programmatically too.

A tale of two XML document formats

Posted on By Mark Wilson
This content is 16 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Once upon a time, there were two competing standards for XML-based document formats. The big bad company that everybody loves to hate created an XML-based format for its Office Suite and called it Office Open XML (OOXML – also referred to as OpenXML), even producing converters for the many business customers that were working with old formats (except if they used a version of Office for a competing operating system). Meanwhile, some guys who like to share things (because all well-brought-up children know that sharing is A Good Thing) had already decided that the big bad company’s idea was too proprietary and developed a competing XML document standard called the Open Document Format (ODF).

The big bad company had been told off many times for not playing nicely and they wanted to show everyone how they were changing their ways, so they made Open XML an open standard and submitted the format to some standards bodies. One of the standards bodies was happy to endorse the format, but the biggest and most relevant of the standards bodies took its time, initially favouring the other format, even though the big bad company’s software had become the de facto standard in many markets around the world. Eventually both OpenXML and ODF were agreed as standards, allowing everybody to be confused by the proliferation of so-called “standard” document formats with similar names.

When the big bad company heard that their document format had been approved, they were very happy and decided that competing formats were no longer a threat. They were concerned that customers would be confused by the various standards with similar names and announced that they would include support for competing formats in the next service pack for their Office suite – even allowing users to select a competing standard as the default. They also said they would include support for a well known portable document format (PDF) that competes with their own XML paper specification (XPS) portable format, despite previously having had to remove PDF support from their Office suite because the company that owned the format threatened to sue them (they had already made it available as a free plug-in).

Everyone was happy. Or nearly everyone was. The European Union said it would be investigating whether the new file format support was good for consumers (they don’t like the big bad company). And some of the guys who say they like to share everything were unhappy because the big bad company was sharing with them and they didn’t really want to play. This made the big bad company very sad because it wasn’t really such a bad company and most of the people who work there just want to write great software. But, now we can share our documents and know that people can read and write to them, at least most of us can live happily ever after.

One of the reasons why OpenXML document formats are so useful

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’ve written before the frustrations of working with OpenXML document formats on a Mac but this evening I found out that a lack of native support for these files can be very useful. I downloaded a .docx file that I wanted to lift some graphics from – of course, Mac OS X and Office 2004 for Mac didn’t recognise the file but it’s really just a .zip file and after letting StuffIt Expander work on the document, I was soon able to locate and extract the images that I wanted from the document! Very efficient.

Of course, this was on a Mac, but the same principle applies for a Windows or Linux (or even MS-DOS) PC. If you can find a utility that can read .zip files, it should have no problem extracting the constituent parts of an OpenXML document.

Quick guide to getting video content onto an iPod

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Apple iTunes can play back a variety of video formats but the iPod (5th generation) and Apple TV each have their own limitations and only play videos that are created in specific formats. I understand that not all codecs will be available for all platforms but I’m a techie and it’s taken me a lot of time to work out what can and can’t work on my iPod. And yes, the the iPod may only have a 320×240 pixel QVGA screen but it can play back at larger resolutions using the iPod AV cable (or another cable if you can get it to work) – it might not look great on a high definition display but I only have standard definition TVs and it’s perfectly good enough for them (sure, there are a few compression artefacts but I get them with satellite TV too).

I’ve been fighting with video incompatibilities for a few days now and think I’ve pretty much got everything sussed, so, here’s my quick guide to getting video content onto an iPod (some of the software mentioned below is Mac-only and so Windows/Linux users might need to search for something else… sorry).

  • Firstly, (courtesy of Apple’s frequently asked questions about viewing and syncing video with iTunes and iPod), iTunes is your friend. Not only is it the centre of the Apple digital experience but it can convert files to iPod or Apple TV format. Simply select the video or audio content that you require, right- (or Ctrl-) click and select convert selection for iPod or convert selection for Apple TV as appropriate. This seemed to work for much of my video content that has come from an eclectic mix of locations using a multitude of codecs (the main changes seem to involve converting from QuickTime movie file to MPEG-4, using a low complexity profile and the H.264/AAC codecs) although I have a few files that seem to have lost their audio track along the way. For these files, I used iSquint to retry the conversion and it seems to have got around whatever the encoding issue was. Using iSquint’s default settings, content is resized to either 320×240 or 640×480 (depending on whether the output is optimised for iPod or TV) but I did find that by using the option to Optimize for TV there was a noticeable increase in the picture quality, even though the source file was only 320×240 (obviously the quality will not increase from the original but, when viewed on a TV, the 640×480 version that was optimised for TV had a noticeably clearer picture with fewer compression artefacts than the 320×240 version that was optimised for the iPod).
  • The tip above for converting content to an iPod/Apple TV format seems to be non-destructive, which is good but does require some management to ensure that only the correctly formatted content is synchronised (in order to avoid errors like the one shown).iTunes error message explaining that some of the videos in your iTunes library were not copied because they cannot be played. Also, note that the duplicates may not appear in the same location as the originals – some of my video podcasts have relocated themselves within iTunes to the movies section and whilst the video type can be changed (between movies, music videos and TV shows), I’m not familiar with any method to move the content into iTunes’ podcast section. Some video podcasts are available in alternative formats via different RSS feeds, but that’s a nuisance where you might want to watch a high-definition version on the computer but still have it available on the iPod when out and about. Consequently I have multiple copies of podcasts like Systm, where I have both the small QuickTime and large QuickTime feeds in iTunes and different episodes marked as played in each. I’ve yet to find a way around that particular issue and it is relatively minor.
  • Assuming that it is legal where you live, applications like HandBrake will rip DVDs into a format that can be transferred to the iPod via iTunes and videos look surprisingly good on the iPod screen (yes, really – certainly good enough for entertainment on a long train/plane trip). Add to that the potential to use an iPod connected to a TV and it’s an easy method for watching video content in the living room, hotel or even hooking the iPod up to a screen in the car to keep the kids amused without spending lots of money on an expensive in-car entertainment system (I haven’t tried it yet but I may do soon). Encoding video (and re-encoding via an iTunes conversion) is time consuming and also the quality will degrade with each conversion, so it pays to get it right first time – I had some difficulties using the previous version of HandBrake to rip a DVD using the HB-iPod preset but version 0.9.0 seems to be working well for me (although if it doesn’t work, some advice suggests ripping to disk before performing the conversion, using a tool such as Mac the Ripper). If the content is already on the computer, then QuickTime Pro may help with the conversion (although iTunes is based on QuickTime so I recommend trying the conversion in iTunes first).
  • Finally, for content producers, Apple provides a tutorial about creating video for iPod.

These techniques have allowed me to transfer most of the content that I want to access on the move to my iPod. There are a couple of issues to iron out (as mentioned) but I’m a lot further forward than I was a few days back. Please leave a comment if you can add to the advice.

Microsoft’s MacBU is moving in the right direction, just not fast enough

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Office for Mac product iconsA few weeks back, I wrote about the frustration of working (or rather not be able to work) with Open XML documents on a Mac. Some wag even pointed out on a recent podcast that Apple beat Microsoft to provide support for its own document formats in the new iWork 08 application suite. I hear good things about iWork and it’s very reasonably priced (especially when compared to Microsoft Office) but I work with Microsoft Office 2007 on Windows and need something functionally equivalent for the Mac so I’m sure I’ll be getting a copy of Office 2008 for Mac in due course (attempts to get a beta invitation have failed dismally). There is light at the end of the tunnel though – since my original post, the MacBU has released a (time-limited) beta of the Microsoft Office Open XML File Format Converter for Mac, so that at least gives me something to work with for now (the previous version was only for Word documents).

Mac RDC logoAnother new product from the MacBU is (at last) a universal binary version of the Remote Desktop Connection Client for Mac. I’ve been beta testing this and whilst it’s far more stable on an Intel Mac than the old version, it still doesn’t seem to offer something that I need – support for multiple client connections. I’ve provided feedback on this (others were less charitable in their contributions). In the meantime, I’ll be sticking with CoRD.

It seems that the MacBU is releasing new products but at an almost glacial pace. I don’t care that it’s been 4 years between Office releases – there was a similar gap for the Windows product – but surely the file format converters could have been ready when Office 2007 shipped on Windows. Similarly, based on what I’ve seen with the Microsoft’s RDC client for the Mac, it’s not exactly worth waiting for.

Open XML documents driving me insane on the Mac

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A few weeks back, I wrote about how smart Office 2003 had been in detecting my need for an Office 2007 document converter and opening it for me. If only I could say the same for Office 2004 on the Mac. I’m all too familiar with Microsoft product groups working independently but the MacBU has excelled (excuse the pun) in its inability to ship a working document converter for the Open XML document formats more than seven months after the release of Office 2007 on Windows.

To make matters worse, Office 2008 for Mac (which uses the new file formats) is a closed beta so I can’t use that to convert/open the files.

Ironically, there are various reports of using an alternative office suite like OpenOffice or NeoOffice to open the files! Hmm… not such a smart business move for Microsoft then…

My Digital Life has information on the various options for working with Open XML in Office 2004 for Mac. Mac Mojo (the Mac Office team blog) has information about a beta converter for Word documents (only).

Microsoft Office: save as PDF or XPS

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Microsoft doesn’t provide portable document format (PDF) compatibility within Office 2007 but there is a free add-in to allow Office applications to save documents as a PDF or XPS (formerly codenamed Metro) document.

Working with OpenXML document formats in Office 2003

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Just before I left the office yesterday evening, I downloaded some presentations from Microsoft. Not surprisingly, these were in the new Office 2007 (OpenXML) document format and Windows XP recognised them as zipped archives (which they are – if you open one up, there are a load of XML files and graphics – incidentally a great way to extract graphics from a presentation – although curiously they all have the date and time stamp of 01/01/1980).

As I still use Office 2003 at work, it seemed logical to me that these files would be inaccessible, but I opened one up out of curiosity and PowerPoint gave me the option to install a compatibility pack (presumably I’d already installed an update to provide the “hook” for Office 2003 to download the compatibility pack). Once the 27MB Compatibility Pack for the 2007 Office System had been installed, I could work natively with the files, including the ability to save OpenXML from within Office 2003 applications, disproving my earlier predictions of file format nightmares.

Amit Agarwal has more information about working with the OpenXML file formats on his Digital Inspiration blog.

Where are the WVP2 codecs for QuickTime on a Mac?

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

It’s generally accepted that Macs are great computers for graphic design and audio-visual work – so why is it so hard to play Windows Media content on a Mac? I know that QuickTime is the centre of Apple’s audio-visual experience – so why should Apple support competing formats – but perhaps I should really ask why the various software companies have seen fit to introduce such a myriad of audio and video codecs? I’m a techie and I can only just keep up – think about the poor consumer who just wants to share some family videos with the grandparents!

The trouble is that Microsoft, as the developer of the most widely installed operating system on the planet (with a correspondingly huge number of multimedia file formats as described in Microsoft knowledge base article 316922), has seen fit to dump development of Windows Media products for other platforms. Quoting part of the Wikipedia article on Windows Media Player:

Version 9 was the final version of Windows Media Player to be released for Mac OS X before development was cancelled by Microsoft. WMP for Mac OS X received widespread criticism from Mac users due to poor performance and features. Developed by the Windows Media team at Microsoft instead of the Macintosh Business Unit and released in 2003, on release the application lacked many basic features that were found in other media players such as Apple’s iTunes and QuickTime Player. It also lacked support for many media formats that version 9 of the Windows counterpart supported on release 10 months earlier.

The Mac version supported only Windows Media encoded media (up to version 9) enclosed in the ASF format, lacking support for all other formats such as MP4, MPEG, and Microsoft’s own AVI format. On the user interface front, it did not prevent screensavers from running during playback, it did not support file drag-and-drop, nor did it support playlists. While Windows Media Player 9 had added support for some files that use the WMV9 codec (also known as the WMV3 codec), in other aspects it was seen as having degraded in features from previous versions.

On January 12, 2006 Microsoft announced it had ceased development of Windows Media Player for Mac.[4] Microsoft now distributes a third-party plugin called WMV Player (produced and maintained by Flip4Mac) which allows some forms of Windows Media to be played within Apple’s QuickTime player and other QuickTime-aware applications.[5] Mac users can also use the free software media player VLC, which is also able to play WMV-3 / WMV-9 / VC-1 Windows Media files.

It seems that the Flip4Mac WMV Player, which should provide the missing Windows Media support for Mac users (as endorsed by Microsoft) does not support all Windows Media codecs, namely it refuses to play content encoded with the Windows Media Video 9 Image v2 (WVP2) codec.

I can understand Microsoft’s position – after all they want to preserve their market share – so why doesn’t Apple make it easier for switchers with legacy video content? As the iLife applications are such a selling point for Apple, why not make it easier to convert from the Windows equivalents?

My problem is that, for the last few years, I’ve been creating home video content using Windows Movie Maker and Photo Story. They may not be the best video applications in the world but they are fine for movies of holidays and the kids and are included with Windows XP (well, Movie Maker is – Photo Story is a free add on). Nowadays, I have a Mac but I still want to play my old content.  The resulting WMV content from Movie Maker hasn’t caused too many problems as it uses the Windows Media Audio 9.1 and Windows Media Video 9 (WMV3) codecs and simply needs appropriate QuickTime components to be installed. Unfortunately the Photo Story output refuses to play the (WVP2) video track in either QuickTime (WMV Player) or Windows Media Player for Mac OS X and as far as I can tell there are no suitable codecs available.

In desperation, I went back to PhotoStory and tried to export in another format but there is no such option (it supports various screen sizes and frame rates but they all seem to be using the same codec).

One macKB thread suggests using Dr Div X to convert the file but the latest version of Dr DivX failed (on both Windows and Mac); similarly the DivX Converter didn’t work for me.

Eventually, I found a utility that could convert the file for me (Advanced X Video Converter) – it’s done a good job although whilst the quality is acceptable for my home movies there are some visible compression artifacts (I used the H264 video and 24bit audio codecs to convert to a .MOV file). In fairness, the compression artifacts may also be visible in the original WMV file and anyway they are hardly surprisingly as the video was created from compressed JPEG and MP3 files, which have then been compressed to WMV and once more to MOV so the quality is certain to have suffered along the way. What’s possibly of greater concern is the resulting increase in file size – up from 19.5MB to 431.4MB.

I’m glad I got there in the end – for a while it seemed that I would have to keep a Windows virtual machine just to play old home movies – and there I was, naively believing that converting to digital capture and storage would save me from issues with legacy formats.

Missing QuickTime codecs

Posted on By Mark Wilson
This content is 17 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Earlier this week, I needed to play back a .AVI file in iTunes/Front Row. That’s not really a problem as it’s easy to convert the video to a .MOV file using Apple QuickTime Pro but one major issue was a complete lack of sound.

Now, before I go any further I should explain that there is one common theme throughout the comments section of every site discussing media formats and players – someone always says something to the effect of “use VLC – it plays everything”. VLC is a great media player but:

  • I have Apple QuickTime Pro.
  • I use Apple iTunes and Front Row (both of which depend on QuickTime).
  • QuickTime components are available for many audio and video formats.

In other words, using VLC isn’t the right solution for me. QuickTime gave me a clue as to the problem as it informed me that:

Some necessary QuickTime software is missing. It may be available on the QuickTime Web site.
If you have a dialup connection to the internet, make sure it is active, then click the Continue button to check for the software.

I could have worked out for myself that I was missing a codec (and that message is pretty poorly written… should I not click continue if I don’t have a dialup connection? Maybe I’m reading the message too literally!) but clicking continue took me to the QuickTime components page and I didn’t know which one I needed. I was pretty sure that the video was an XviD movie and I already had the DivX codec (v6.4) as well as Christoph Nägeli’s XviD codec (v0.51) installed but then I found a big clue in the XviD FAQ:

It’s important to understand that video and audio are two separate things, which when combined make up movies. A movie consists of a video stream for the picture and an audio stream for the sound. The XviD codec is what makes it possible to decode the video stream, but it has nothing to do with decoding the audio stream. If the sound in a movie isn’t working you have to find out which audio codec is missing and install it.

The FAQ continues to explain how to use a Windows utility called GSpot to identify the necessary codecs but after reading Mike Peck’s article about playing XviD movies on an Intel Mac (and Paul Stamatiou’s follow-up post on getting Front Row to play XviD, DivX and 3ivX videos), I realised that the missing codec was for AC-3 (Dolby Digital). After installing the A52Codec (v1.7.2) for AC-3 playback and restarting QuickTime and iTunes I was able to watch my video, complete with the previously-missing audio stream.

I’m sure that over time I’ll need to add more codecs and one potentially useful resource is afreeCodec , offering downloads for Windows, Linux and Macintosh computers, games consoles and mobile phones.