macOS OneDrive, ScanSnap PDFs and the "could not be opened" error in Monterey

When I migrated from Mojave to Monterey I ran into the typical array of macOS upgrade issues -- including having to reinstall Monterey. There's a reason I dread updating macOS.

One of the issues was that OneDrive didn't seem to work with my ScanSnap PDF uploads. In this case there were two suspects - the Monterey update and a OneDrive update. (One of the reasons I upgraded after migrating off Aperture was that OneDrive was no longer supported.)

The iOS ScanSnap client seemed to work as before, and the PDF appeared in the OneDrive folder I used -- but the file could not be displayed by Preview. I got a "could not be opened ... It may be damaged or use a file format that Preview doesn’t recognize."

It took a few searches to find the answer ...

If you were previously navigating to useraccount/OneDrive/DocumentFolder and opening your files from there, that seems to have stopped working now, and you have to go to Locations/OneDrive/DocumentFolder instead.

I'd had a Favorites link to the OneDrive folder that held my scans prior to the update. When I study where that link goes now it's not to the old file system folder, it's to:

/Users/jgordon/Library/Group Containers/UBF8T346G9.OneDriveSyncClientSuite/OneDrive.noindex/OneDrive/ScanSnap

So the folder that used to be in the file system was now buried in Library but the Favorite somehow resolved to it still.

I created a shortcut to a folder of the same name as displayed in Locations/OneDrive and the path there is

/Users/jgordon/Library/CloudStorage/OneDrive-Personal/ScanSnap

Digging into OneDrive preferences it claims my location is "/Users/jgordon/Documents/One..." (yep, truncated path). This is the path OneDrive used to use, but now there's just a Favorite there.  The true path is ... yep ...

/Users/jgordon/Library/CloudStorage/OneDrive-Personal/ScanSnap 

Even though none of the files are in a location that I expect Spotlight to index it does appear to index the files stored there once I revised settings so all files were downloaded. Once I did that however the file was now readable even in the location my old Favorite resolved to: 

/Users/jgordon/Library/Group Containers/UBF8T346G9.OneDriveSyncClientSuite/OneDrive.noindex/OneDrive/ScanSnap

 So this is kind of what I think was going on to cause this particular time wasting problem

1. Apple made everyone switch to their preferred approach to managing Cloud files.
2. The folders that were once in the local file system were gone, but an old Favorite somehow resolved to a similar folder buried in a virtual file system. The file, however, was no longer resident locally, it only seemed to be available if one inspected the virtual folder with Finder. Preview could not access it because it wasn't there, and in Monterey Preview gives a misleading error message.
3. When I used the Locations OneDrive "folder" to navigate I went to a different Library CloudStorage folder where OneDrive will auto-download folders on demand. If, however, full download is active (as it was previously so I can backup but that's not the default) then even the internal system OneDrive uses has a full copy and Preview will open it.

Apple wants all Document folders to be stored in the Cloud and may eventually want all user folders in the Cloud, so part of this is probably to prevent different Cloud Providers from cross-synching folders.

I think the bug hits those very few people who had a Favorite to an old style OneDrive folder prior to upgrading OneDrive. Although these kinds of complex emergent bugs don't hit many people, there are thousands of bugs like this so sooner or later we run into them. Which is why it's now very hard for non-geeks to use a personal computer.

How to remote (push) install a xbox store purchase to xbox from Mac

I couldn't find out how to push an xbox game install to the console with Google searches. Here's what I learned on my own.

1. Go to xbox store.
2. Login
3. From profile picture choose 'My Microsoft Account' 
4. Click Order History
5. Click on appropriate order
6. The bold text below the order number is actually a link. It's evidently a secret
7. Click on the link (Resident Evil ...)
8. You'll the item description in the store. There's a button to push install.

ToDo apps: Microsoft's solution

I've used Appigo's ToDo app for about 12 years (with Toodledo at first). It's had problems over the years, but in general it's been a good subscription choice. There's a fairly hard data lock (maybe SQLite?) but manual reentry is feasible albeit annoying.

Lately, however, ToDo has been more ragged. A recent server side change induced a date bug (time zone?) that in turn showed me I was using a macOS app last updated in 2016. It appears to have been abandoned on the Mac App Store. When I went to Twitter I found Appigo's account was closed years ago for violating TOS. Eventually I found I could download a current version of their other App Store app from their web site.

At the moment the app is more or less working again, though parts of the macOS app UI are kind of weird. I figure there was some violent ownership transition with lost dev passwords in Appigo's history (maybe they got ransomwared?).

I decided to go shopping again. I'm looking at:

• Apple Reminders: hard data lock and I have to upgrade from Mojave to get to latest version (not happening).
• Google Todo: this is one hell of a weird product. WTF is their web strategy? Tied to Gmail? Tied to Calendar? At least there's data export.
• Things
• OmniFocus: poor Omni is in some disarray ...
• Microsoft To Do

Today I dug into Microsoft To Do. Of course it's a mess, but this is 2020 so we expect that. The mess starts with Microsoft reusing product names. To simplify a bit:

• There are classic Outlook Tasks. I'll call these TasksClassic. TasksClassic was excellent in many ways, including, once upon a time, great import/export options and lots of view flexibility (I like to sort by last modified!). Unfortunately it's dead, just barely hanging on in the current desktop app with some degree of synchronization with the new product.
• There's the new Wanderlist-based product variable called Microsoft To Do and ... Outlook Tasks (name reuse!). I'll call these TasksW for Wanderlist.

If you open the Help screen page for macOS TasksW (To Do) it takes one to a page on Outlook synchronization that's obsolete -- because the Outlook.com version of Tasks has switched from TasksClassic to TasksW. On the other hand the version of Office 365 on my Mac still has TasksClassic, and it does synchronize with TasksW as displayed on macOS and iOS Microsoft To Do.app.

Are you still with me?

This gave me a brief moment of hope that there was some data freedom here. I remember the import/export options of old Windows Outlook. Alas, the only import/export from macOS Outlook is Microsoft's PST format. There might be some way to do things with Outlook Windows or with 3rd party tools but I don't have the energy for that.

At this time I think TasksW is probably a decent enough product, but this has reminded me how screwed up Microsoft is. So I'm setting this one aside for the moment.

See also:

• There are no great task managers for the iPhone - but there's hope for 2011: My current task list started out on the Palm III and has migrated many times. I probably have uncompleted tasks from the 1990s. I think we're coming to the end of that data migration though. Note from 2010: "Google might finally provide an API for Google Tasks, allowing iOS client development."
• My ancient spreadsheet on migrating from Palm to iPhone - I think I used Outlook Tasks as part of the migration process, or maybe even Microsoft Access. Alas, too long ago. Painful though.

I really like my SharePoint list based blog that's not a SharePoint blog

I don’t think anyone is terribly interested in this, so I’m not going to provide a lot of detail, but if you want to know more just email jgordon@kateva.org.

So why am I writing anything? I’m writing because this thing has worked very well for years. Alas, nobody at work appreciates the simple genius of how it works. So here I can vent.

I ignored SharePoint’s clumsy native blog. I built my blog off the SharePoint announcement list which has basically worked the same way for 10-20 years. Like every list in SP it has an RSS feed (deprecated in newest versions of SP alas) and email notifications with user controlled update frequencies. So the subscription side works fine.

I added a couple of fields to the basic list. One is for tags. Tags let me create topical views of the list by creating SP views that filter on tags.

The other field (and this worked better than I imagined) was to create my own PUBLICATION_DATE field and sort the blog (list of announcements) by publication date. The default value is the date created — but I can edit the publication date without changing the post URL!

The last is wonderful. Instead of having to point to an old post and perhaps add some new additions, I just update the old post and revise the publication date to current. SP regenerates email notifications to subscribers, updates the RSS feed, and the view shows the updated post at the top — but the URL is unchanged so links don’t break.

It’s really simple, it’s worked very well for 2-3 years, it should work indefinitely. I wish other blogs worked that way — let me revise publication date, sort by that date, don’t break links.

PS. Why SharePoint? Because where I work that’s what we have. We aren’t getting anything better.

Escape from Outlook Notes - export as text files, import into NvAlt or other

Back in 2010 I wrote Gordon’s Tech: Escape from Outlook Notes - ResophNotes, Simplenote for iPhone and Notational Velocity.

Seven years later ResophNotes, Notation Velocity (I now use nvAlt), and Simplenote are still around — despite lack of a revenue stream for any of ‘em. Not only are they still around, but it’s still possible to keep notes in plain text or RTF — which is as future proof as computing gets.

They are still around — but not in great health. ResophNotes was last updated in 2012 or so and it is donation ware (always was). Simplenote was purchased by Automattic (WordPress) and is now open source and apparently run as some kind of charity operation [1]. nvAlt is ancient but Brett Terpstra recently updated it to run on Sierra (a notoriously buggy version of macOS).

These apps are old and kind of worn — but so am I. So we’re a good fit. 

Recently I had another set of Microsoft Outlook Notes files to move to Simplenote. Talk about old and kind of worn! Outlook Notes is old, odd, and useful. It’s a winner in a category of one. Functionally it’s a lot like Simplenote — though you can’t print from Outlook [2].

The problem with Notes isn’t that it’s old and odd, it’s that everyone has given up on it. Microsoft tries to make it invisible. Apple dropped support for Notes sync via iTunes/iCloud — though I think Exchange sync may still work. Google ignores them too.

Which is why I needed to again move a data set of out Outlook Notes. I think export to Outlook CSV them import to ResophNotes is still the best bet. From there to Simplenote and from Simplenote to nvAlt, etc.

There’s another way to go though. You can use VB to script export from Outlook to c:\notes:

Sub NotesToText()
    
Set myNote = Application.GetNamespace("MAPI").PickFolder
  
For cnt = 1 To myNote.Items.Count
        
noteName = Replace(Replace(Replace(myNote.Items(cnt).Subject, "/", "-"), "\", "-"), ":", "-")
        
myNote.Items(cnt).SaveAs "c:\notes\" & noteName & ".txt", OlSaveAsType.olTXT
   Next
End Sub

The key thing is this script creates file names with the note title. It’s not a perfect result because the top of each file looks like this:

Modified: Thu 1/12/2017 2:36 PM

accidents and injuries

In this case ‘accidents and injuries’ becomes both the file name and the third line of the note. The “Modified: …” bit is just annoying. I suppose it could be removed using regex and a text editor that can iterate over a set of files … or script the removal.

I imported the plain text notes into nvAlt where they got the title from the file name so it looks something like this:

accidents and injuries

Modified: Thu 1/12/2017 2:36 PM

accidents and injuries

A bit of redundancy in there, and, of course, the Modified string is still around.

Overall this doesn’t work quite as well as the ResophNotes method, but it’s helpful to have options.

- fn -

[1] Automattic recently released a redo of the macOS Simplenote client. I haven’t tried it, but I hope it fixes the perennially broken search of the current client. In any case, Simplenote is not dead yet.

[2] Outlook 2013 broke Notes by essentially removing the list view — I think this might have been fixed in Office 365.

Update 3/13/2017

Speaking of ResophNotes, the current version has an impressive set of import options. Outlook CSV, Toodledo Notes CSV, text files, single file with note separator …

I donated years ago, time to send another donation.

Upgrading Office 365 from Personal to Home - it could be easier

I like Office 365 Personal. The Mac version has bugs of course, but it seems to improve with each incremental release. For $70 a year I have a version running on my Mac and a version running in a Windows 10 VM (in theory it only installs one either 1 Mac or 1 Windows machine, but in practice it seems to allow both at once). The cost seems entirely reasonable to me. There’s little data lock because so many apps read and write (more or less) Office files. It’s the kind of subscription software I love.

So I didn’t mind when I had to get another license to cover my son. I figured I’d just upgrade to Home. 

Except it’s quite unclear how you do that. This 2015 article suggests there’s a bit of an underhanded trick to it: How I upgraded Office 365 Personal to Office 365 Home for $10. That’s sort of how it works except it’s as designed, it’s not a bug or trick.

I had a month left on my Personal (1 machine) subscription. I bought Home (5 machine) for $10/month (renewable). MSFT switched my remaining month from Personal to Home. Then a month from now, it was to start charging me $10 a month. In MSFT parlance the subscriptions “stack”.

I suppose the trick deal is to buy a year of Personal, then immediately get a $10/month subscription to Home. Then you’d get a year of Home at the Personal price.

In my case it took a call to support for me to understand what had happened. Microsoft could improve this process. Once I figured it out I switched from $10/month to $99 a year. That switch was easy — and MSFT threw in a free month (standard behavior). 

Microsoft OneDrive does archival photo sharing better than I thought (with one bug)

Yes, I’m being cautious.

In testing, however, I can do this:

• Create a folder that I can upload full resolution images to and ALSO make it shareable so other OneDrive registered users can upload to it.
• Create an album that is based on that folder.
• Share the folder-based album to people who do NOT have a OneDrive account.
• Update that folder-album using the web UI. (Switch to Files, choose Create Album from Folder. If folder actually exists then it updates - including deletes.

Unfortunately there’s a bug with the undocumented update feature. In addition to updating the album it creates duplicate albums with an iterating integer suffix. The duplicates are easy to delete. I can’t see how to submit a bug report for One Drive unfortunately.

The current behavior is very close to what I want for sharing images in our sports team. There are also album share to Facebook options.

I haven’t tested whether any ICMP metadata will be used by the albums. There don’t appear to be any Photos.app uploader plugins but I’m still using Aperture so I’d be exporting.

I have 1TB of OneDrive data thanks to my Office 365 subscription. That subscription, which supports installs of Mac apps as well as Access on my Win10 VM, has been a great purchase.

Clean install of windows 10 on VMWare Mac - getting a license ($130)

I needed to use Microsoft Access.

I know what you’re thinking. You’re wrong. Yes, Access is a horrible old patchwork beast, but for some kind of data manipulation it’s still unequaled. It’s particularly good at mixing local data store with ODBC stores. It helps that I know where the bugs are buried — though Access 365 on Windows 10 is particularly buggy.

So about two weeks ago I fired up my old copy of VMWare Fusion 7, downloaded Windows Pro 64bit OEM and used my multi-platform multi-machine Office 365 license to install Microsoft Access. It was all relatively painless. I did find Fusion 7 isn’t happy with Yosemite virtual desktop, so I only use full screen Win 10 in just one display. That works until I do my El Capitan/Fusion 8 update.

The entire package takes up about 23GB on an external SSD. 

I did wonder how I was going to pay for Win 10. It was working without complaint. I figured I’d get some kind of notice. About two weeks after installation it began showing a watermark on the screen requesting activation and personalization features were turned off. That was a polite reminder.

I went hunting for a Win 7 or 8 license  to get the free upgrade — but no-one I knew had one to spare. My own Windows licenses was for XP, that didn’t help. I couldn’t find any good educational deals either. Amazon had lots of Win 7/8/10 licenses at suspiciously low prices, all of which seemed a mixture of counterfeit and genuine product. (Amazon — the crooked pawn shop of the Net.)

In the end I remembered PC Connection and found that while MSFT charges $200 for a Win 10 Pro 64bit license PC Connection had an OEM version for about $145. Once I knew the right price range I found an OEM version on Amazon that shipped from Amazon for about $135. I can’t link to it because Amazon’s fraud-friendly habit of consolidating product listings that ship from multiple sources mixed in their source with $105 versions that seem to include counterfeits.

It came in a legitimate looking Microsoft white envelope holding some kind of disk thing envelope (what’s a DVD?) with a sticker and license number on the front of the inner envelope. The license number was all I needed, it worked.

Excel 2007 and 2010 can save multicolumn sort order criteria - but only for tables

The sort order amnesia of Excel 2007 was one of the odder regressions in the history of Microsoft Office. After decades of remembering the last set of sort criteria, Excel forgot them.

Sometimes, if you had a data range with headers and no gaps, and if you clicked on a header in the table, the sort order seemed to stay with the header.

Maybe.

Apparently this is true for Excel 2010. Microsoft documents this (emphases mine):

Sort criteria are saved with the workbook so that you can reapply the sort each time that you open the workbook for an Excel table, but not for a range of cells. If you want to save sort criteria so that you can periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is especially important for multicolumn sorts or for sorts that take a long time to create.

Tables are more special in 2007 than in prior versions of Excel. I found a description of how to do this in an otherwise obscure forum (maybe a splog?) by dFrank:

Why Excel 2007 doesn't save ... Data -> Sort ... settings?: 

It is amazing, but why such a simple question take ages to resolve? 

Why Microsoft didn't put a huge warning label that SORT ORDER in EXCEL 2007 is now behaves completely different from previous versions. 

For years now, I was under impression that is it just a bug, and nothing can be done about it. 

Finally, some super-small font on some supper-obscure web site whispers that you only can save sort on a table, but on on a range. 

What the h*** is a table. A table is LIST in previous Excel versions. Never heard of it. But we do not need to know about this. Let's just go thorgh the steps:
-01- Select a range of cells just a bunch of columns and convert it to table (Ctrl T);
-02- Remove annoying unneeded table formatting (Design --> Table Style --> Clear);
-03- Remove filters (Data - Filter);
-04- Apply a sort. 

Next time you are in the file, your sort is finally preserved.

In my limited testing I don't think you need to remove data filters, they are compatible with tables.

Wiki impressions: XWiki

After a disastrous SharePoint 2007 to 2010 migration [1] it was clearly time to replace my team's use of SharePoint wiki. The mangled conversion was a red-flag-and-air-raid-siren declaration that SP was going to be a longtime world of hurt.

We looked around at the wiki options, and considered TWiki, XWiki and Confluence (WikiMatrix). Atlassican Confluence is the best of breed, but for corporate users like us it's expensive. Hardware we have, platform licenses we have, networks and backups we have, and expertise we have. What we don't have is a budget.

TWiki is the easiest of the three to configure and it has the fewest technical requirements. XWiki looked like more work, but we liked the rich text editor and (limited) table support. We're now starting up on XWiki enterprise. Here are a few impressions from the process that may be of interest to other wiki searchers ...

• We started out on a Linux server but couldn't get XWiki working. We're not Linux experts. We switched to Windows and our engineer resource had XWiki setup within a day. I wasn't impressed with the ease of installation.
• XWiki requires a Java Servlet Container. That rules out Dreamhost and many other hosting options. (TWiki can install with Dreamhost.)
• XWiki includes a blog service but there's no support for Blogging APIs. So you can't use MarsEdit or Windows LiveWriter to write.
• The documentation is weak. For example, how do you delete a Space? Turns out it's easy if you have privileges, but the documentation claimed we needed an extension.
• XWiki Enterprise is a geek platform, though much of the complexity can be hidden.
• I like the approach to URLs and page titles. You can change a page title, but the page URL is fixed. XWiki provides an effective UI for looking up local pages during link creation. (SocialText retitling creates a redirect page with the old title/url and a link to the new title/url. Sharepoint changes the URL and title, but updates intra-liki links. Foreign links break.)
• The rich text editor is simple but works fine for my purposes.
• IE 9 doesn't work with the controls on gadgets, but seems ok for non-admin users.
• XWiki has the universal wiki curse -- no ability to migrate posts between systems. I wonder if people who complain about being unable to move their medical records between providers see the similarity. OTOH, when our Linux install failed it was easy to move to Windows.

I'm reasonably optimistic that XWiki will work for us. I'm glad to be free of Sharepoint 2010.

See also:

• XWiki - Open source, LGPL, WYSIWYG
• Offline Browsers (web robot) – options to review
• How can I create a local copy of a Sharepoint wiki?
• A workaround for image uploading to Microsoft’s Sharepoint Wiki.

[1] With SharePoint it's not possible to separate software issues from implementation. Maybe Microsoft provides wiki conversion tools that our IT department didn't use. I have discovered that if I copy/paste of our 500+ SP 2007 wiki created pages into Word, then remove all styles, then past back into SP 2010, I can edit them again.

SharePoint 2010: migration of a SP 2007 wiki

My business group has maintained a large SharePoint 2007 wiki.

Recently we had to migrate to SP 2010. The wiki went along for the ride.

I've seen some bad outcomes in software over the past 20 years. I mean -- really bad products.

I've never seen anything like this.

Wiki pages that look like they were edited by intoxicated baboons. Pages that can't be edited in IE 9, but can be edited in Chrome. Pages that can't be edited in Chrome but can be edited in IE 9. Pages that can't be edited anywhere, save as HTML.

SharePoint Designer is an option. I tried it. I shed tears for the lost spirit of FrontPage and Vermeer.

I despair, sometimes, when I see what Apple's interns are doing to iPhoto or OS X. Even they, even they, have never done anything like this.

This is the worst.

Microsoft, astonishingly, is truly finished. We really are in the twilight of the personal computer. 

Windows Live Writer - beware the Trojan Horse updater

I received a notice to update my beloved Windows Live Writer* yesterday. I unthinkingly downloaded the installer.

Fool.

It was a Trojan Horse. No, not a "Trojan" as in a carrier for anonymous malware, "Trojan Horse" in the historic sense of a gift containing unwanted vermin.

The installer has plagued my system with a suite of Windows Live products that I don't want and that are almost certain to reduce system stability. Now I have to tediously uninstall:

• Live Call
• Live Messenger
• Photo Gallery
• Live Family Safety
• Mail
• various Outlook add-ins
• heaven knows what else

Now you know. Don't make my mistake.

*The only good, new, Microsoft product in five years. An acquisition of course. Microsoft has since abandoned it; I think the original (Minnesota?) team is gone. Microsoft is doomed to immense wealth and mediocrity.

When themes corrupt: Fixing a possessed PowerPoint

Twenty minutes before show time, my PowerPoint 2007 presentation (Sorry kittens) was possessed. I tried adding a drawing item to an image and nothing happened. I couldn’t get the the image to display in the normal slide view. I couldn’t fix the problems, so I gave up and went with what I had.

Later I tried to figure out what went wrong. As best I can tell the themes/layout control data had been corrupted. This particular presentation started with a corporate theme as PPT 2003 and had round-tripped between 2003 and 2007 a few times.

Evidently, a few times too many.

Setting a theme on a slide didn’t fix it. The fix was

1. To to View – Presentation views – Slide Master
2. In this view, choose Themes and apply a theme
3. Save
4. Go back through presentation and fix everything up

I had to make do with a standard PPT 2007 theme, but I could again edit my presentation.

VMWare Fusion 3: Migration, PowerPoint and Shrink Disk

I've used VMWare 2 with Windows 2000 and Office 2003 on my MacBook for almost a year. I hardly ever use it, but it's compact and fast.

I wasn't sure I'd bother installing VMWare on my i5 iMac, but then I discovered how lousy PowerPoint 2008 really is (an especially bitter discovery since my first impressions were very positive).

So I downloaded the 30 day trial version of VMWare 3, installed VMWare Converter on my creaky XP box, and created an XP image on my iMac including Office 2003. I'm pleased to report that PowerPoint 2003/Win in the Fusion VM is at least ten times faster than PowerPoint 2008 for OS X.

Here are some discoveries of note:

• VMWare on the iMac had trouble connecting to VMWare Converter. I had to restart the XP box to make it work. I think a pending install created a problem.
• The conversion took an hour or two.
• On VM startup it looked at first that only one account had been created -- the XP box had had 3 accounts. I restarted the VM and it showed all 3.
• I couldn't get VMWare Tools to install. I had to login and connect to the share then run setup. This took a couple of tries I think, and a restart or two. It wasn't as smooth as VMWare 2, but my previous efforts didn't involve migration.
• I had to re-authenticate the VM XP box with Microsoft. That took a few minutes. I'm still running the old box so I unplug the network cable when the VM is running. I'll be putting the old box out to pasture soon. (It's amazing how silent the office is when only the iMac is running.)
• The VM migration created about 50 2.5 GB files in an OS X Package (executable folder). This can be changed in settings. It's done to get around FAT max file sizes; I wonder if it might help with backup. (If you create a 100GB single file VM, each time you open it you'll create a 100GB file that needs backup.)
• I didn't remember than I had two drives in the XP box. The VM had both drives; one held an old redundant backup. I deleted that drive (Settings:Hard Disks) but the VM didn't shrink. I used the "Clean Up Disk" function and that shrank it to a nice 50GB.

See also:

• Experiment with VMWare -- for free
• Parallels to VMware - my experience (Win2K install)
• VMware academic discounts: Fusion and Windows Workstation
• Why I'm downloading Windows 7 RC tonight ... (It runs on my MacBook)
• VMWare Converter - turn your XP box into a VM platform
• How to fix permissions in an OS X Package and how deal with VMWare Fusion Insufficient Permission problems

Update 3/11/2010: It's been performing excruciatingly slowly. I haven't been able to find any explanation. Startup times of about 3-5 minutes, intermittent very slow operations. It behaves like it has no working memory.

Update 10/26/10: I finally get around to speeding it up.

Using Excel matrix operations to sum a range of inverted numbers

There’s an interesting story here about how Google makes us smarter, but I’ll try to post that one to Gordon’s Notes. This post is about sharing what I learned about Excel.

As we all know Excel is the gem of Microsoft. Word was once great, but it fell (though Word in Office:Mac 2008 is surprisingly good). Excel, which started on the Mac, has always been impressive. This time I used one of its more obscure features to solve a problem of my own creation.

The problem was that I’d asked team members to rank their top three topics in a list of about 40. So their top choice was numbered 1, 2nd choice 2, etc. I knew I’d have trouble interpreting the results, but I wanted to make the data entry process very simple.

When it came to creating a cross-topic metric I ran into the usual troubles. I couldn’t just sum them up. I’m sure there are better solutions, but I decided to sum up the inverted numbers. So if 3 people had rated a topic 1, 2 and 3 then the sum would be 1/1 + 1/2 + 1/3 multiplied by scaling factor to give a more readable result.

Thanks to Google (Google Suggest is mind blowing) I learned that summing the inverse of the non-zero (null) values in a row or column is a matrix operation (I have vague memories that there’s a mathematical name for this value), and that you can do this in Excel (credit to the hideous Experts Exchange for the key entry).

It’s a bit bizarre, but here’s what the formula looks like:

={SUM(IF(ISERROR(1/E41:T41),0,1/E41:T41))*10}

Okay, more or less looks like – because you type it in like this:

SUM(IF(ISERROR(1/E41:T41),0,1/E41:T41))*10

Then you hit Ctrl-Shift-Enter to tell Excel to treat this formula as a matrix operation.

You need the “ISERROR” function so Excel ignores the divide-by-zero (null) cells. The “E41:T41” says that the range goes from column E to T on row 41.

This formula did the job. I’d never have come up with this fix if not for Google, but that’s a topic for another post.

Office 2008 for Mac - first impressions and the PPT type lag bug

I've revised this post.

My first impressions were of Microsoft Office 2008 were very positive ...

I'll put my Microsoft disgust up against that of any other geek.

So watch out for the end of days, because I have something ni ... n... nuh ... not so bad to say about Office 2008 for Mac (about $80-90 on Amazon).

Look at this ...

Yeah, two PowerPoint windows open at once.

You're not impressed? Then you don't use Office on Windows, where the #$!$# Windows are glued inside the app window. You can't move one presentation or spreadsheet to one monitor, and a different one to a second monitor.

I must say more, even though it pains me so.

I could mention Microsoft's licensing, compared to, say, Nisus Writer Pro ...

Amazon.com: Microsoft Office 2008 for Mac Home & Student Edition: Software

... Don't need Microsoft Exchange Server Support or workflow management? Home and student users pay for just the features they need. Office 2008 for Mac Home and Student Edition comes with three licenses of non-Exchange-enabled Office 2008 licensed for noncommercial computers...

Three licenses. In case you're wondering, this is effectively 3 machine licenses -- you're not asked for a license for each user on a single machine.

The multiple service pack updates are a pain, but the install was smooth. None of Adobe's problems with non-admin users. The only gotcha is you need to go into Entourage (dead and rotting software) and make sure every feature is turned off lest it seize control from iCal (undead and rotten software).

Pigs not flying yet? How about performance. Office 2008 is responsive on my G5 iMac. The Apps are much more Mac like than, say Aperture -- or many of Apple's products. The file formats are de facto standards (I wish this were not so).

Ohh, yeah. No button bar. Thank god.

I haven't made heavy use of it. I'm sure there are bugs. Even so, it's good enough that I'm willingly using it. Never thought I'd say that about a Microsoft product*.

* Ok, So I love Windows Live Writer. But that was developed outside of Microsoft and seems to have been abandoned by the borg.

Then, about a month later, I tried to use Office 2008 PowerPoint on a real presentation. This time I was using my quad core iMac with Snow Leopard, my earlier experience had been on an old G5.

It was unusable. The keystroke delay is intolerable. I wasted an hour then gave up and finished the work using PPT 2003 on my seven year old XP box.

I think it might work well on a G5, but it doesn't work acceptably on Intel machines.

I'm removing Office 2008 from my machines. I'll install iWork.

Update 4/6/2010: I was working on a presentation that seemed fine. Then I added text to a graphic slide. Instantly all text input became extremely slow. I reset the theme and text lag cleared again.

I think this is a theme corruption bug of some sort related to PPTs that have moved from XP to OS X.

There are some Office 2008 forum discussions of the type lag bug.

Update 4/6/2010b: I think there may be both theme and master slide associated bugs. I don't see any way in the view master slide UI to remove master slides (reset to standard). There are few to no master slide related help topics. PowerPoint 2008 is not a serious product. I expect the user base is becoming very small -- basically academics who don't use Keynote. I've uninstalled Office 2008, I'm going to use Office 2003 in my Fusion VM and I'll evaluate iWork and Keynote.

Update 4/8/10: A colleague tells me that PPT for XP has the largest and most intractable code base of any Microsoft project. I'd not have guessed that; maybe it explains why the Mac version is so bad. Keynote does a nice job of importing PPT files, but for now I'm using Fusion. I will probably buy iWork.

Now I understand why Jobs insisted Apple develop Keynote, and why the other iWork apps followed Keynote.

--
My Google Reader Shared items (feed)

Microsoft Access 2007 - it's still lousy

I'm back using Access 2007 a year after I wrote about Microsoft Access 2007 reliability issues and suggested workarounds.

In the interim I've been using Access 2003 again.

There are some good things about 2003 (ok, just Sharepoint support), but, by and large, it's busted. It's broken in deep and inexplicable ways. Heaven be your friend if you should change a column name -- you may get weird and persistent side-effects.

Since nobody can imagine Microsoft going away, this kind of thing is more than a bit depressing. It's like spending an eternity in Limbo ...

Update 10/22/09: A bit more detail on the two latest bugs, now that I've figured them out.

I was getting a common and useless error message: The setting you entered isn't valid for this property every time I ran a query on a link to a Sharepoint List who's schema had changed since I'd created the link. In particular a field had gone from memo to string. I had to delete the link and create new one. Access 2007 is supposed to be able to regenerate its link on command, but it turns out that doesn't work.

The other bug I got was related to creating an alias to a Sharepoint List column name where the alias happened to be identical to another Sharepoint Column name that wasn't part of the query. Turns out it sort of works, but not really.
--
My Google Reader Shared items (feed)

MobileMe – Getting WebDav (iDisk) support working on XP

No matter what I tried, I couldn’t get my XP machines to mount an iDisk using the WebDav protocol per Apple’s directions:

Connecting to your iDisk from Windows Explorer

1. Click the Start menu and choose Network Connections > My Network Places.
2. In the window that opens, click "Add a network place" to open the Add Network Place wizard.
3. On the next screen, click "Choose another network location."
4. When prompted for the URL for your iDisk, type the following URL address (replace "YourMemberName" with your own member name):
   http://idisk.me.com/YourMemberName/

I thought the problem was that my user name had a dot ‘.’ in the middle of it. MobileMe usernames become webdav directories.

Wrong. Google (praise be) gave me the fix …

I'm not sure if this is the right place to post this but I found a solution to my problems with XP and Apache2 here: http://blog.pclark.net/2005/03/fun-with-windows-xp-and-webdav.html...

The secret is to add a port number to the URL - for instance, use:
http://my.site.com:80/mydirectory
rather than
http://my.site.com/mydirectory.

When you do this, you'll get the AuthName from your httpd.conf file in the authentication window above the username and password fields, and the username and password should work, without having to have my.site.com\ prepended to the username…

So I tried http://idisk.me.com:80/first.lastname/ (actually, I forgot the terminal ‘/’ but it worked first try. I just had to enter my username and password, telling XP to remember the password.

I suspect this is actually an XP bug. There’s something familiar with it, it wouldn’t surprise me to learn I’ve had to do this before (yep, I solved this one a year ago with DreamHost, it’s known as the /# hack!)

See also

• Gordon's Tech- The MobileMe Massacre begins
• Gordon's Tech- WebDAV, Microsoft, DreamHost and the insane slash and pound hack
• MobileMe- Perspective of a crusty Palm veteran – in which I ran into a similar iDisk problem in July 2008

Update 10/21/09: I tried this on another XP SP2 machine and all I had to do was enter http://idisk.me.com/YourMemberName/ with the trailing forward slash (per directions). I don't know what's really going on here. If you're having problems first try the forward slash, next try entering the port.

Update 10/23/09: An Apple Discussion contributer pointed out that Apple's kb article recommends installation of a May 2007 Microsoft "web folder" update:

• description: http://support.microsoft.com/kb/907306
• download: Software Update for Web Folders (KB907306)

I can't tell if this was ever included in any XP service packs, I don't think Microsoft always includes all of their fixes.

Stack Overflow now has global computer question site (beta)

First Stack Overflow came for the coders. Then they came for the Sysops. Now the dynamic duo of “Joel on Software” Spolsky and “Coding Horror” Atwood are going for all the rest of the geeks. I’ve joined the Super User beta (how did they get that url?!)…

… Super User is a collaboratively edited question and answer site for computer enthusiasts – on any platform. It's 100% free, no registration required….

Stack Overflow’s children are the heirs to the pre-spam usenet. Experts-exchange is finished.

Fantastic work, and very much appreciated. I very much hope there’s a fortune in it for them somewhere – I suspect there is.

I love these guys.

Microsoft Bing – is this some kind of trick?

My IE 8 update somehow injected Bing into my life. I don’t use IE 8 all that much, so I left it there and I’ve been playing with it over the past 10 days or so.

Wow.

Bing really stinks. Their algorithms are tightly bound to large marketing-heavy commercial sites that are potential advertisers. Bing feels like a “pay-to-play” strategy. For my purposes these sites are rarely useful. Bing works pretty well for searching Microsoft’s material, but not significantly better than Google.

So is Bing the best Microsoft can manage, or is there some kind of deeper ploy at work?

I can’t figure out what the heck they’re trying to do here. Maybe Microsoft has Google stock options and they’re trying to boost Google’s share price?

PS. To change search providers click on the IE 8 drop down next to the magnifying glass link (top right) then choose “Find More Providers …” then scroll down a ways to find Google Search suggestions (if they could move it lower on the page they would …). I do give full credit to Microsoft for one thing. With a few clicks you can completely remove Bing from the IE Add-ons Search Providers list and you can make Google the default including search suggestion handling.