This drives me nuts…

Background Information

My work computer has 4 physical drives in it:

• A:\ is one of those old and useless 3 1/2 inch “floopy” drives
• C:\ is an actual hard disk
• D:\ is a DVD burner
• E:\ is a DVD drive

My next drives are F, G, H, J, K, L, O, P, T, Y and Z. They are all network drives assigned by our Novell.

Now, this seems all good and dandy, until you plug in a USB drive. One would think that it would automatically assign the drive to B:\ and be done with it. Sadly that is not the case.

For whatever reason, USB drives by default seem to assign themselves to the first available drive after the hard disk. Fine, I can deal with that. The USB drive will be assigned to drive I:\, right? Wrong!

Novell reserves all drives after its “starting” drive. This pretty much means that when I plug in my USB stick, any required installation kicks off, and that’s as far as it gets. (This goes for cameras, MP3 players and all other USB devices, so far as I know.) Now sure, I could change my Novell settings to start handing out drive letters at G:\, but let’s not forget that my users have been using the network starting with F:\ since before I got here in 1999. There are applications coded to these paths, so that would break it.

So now what? Here I am with my USB stick active, but no drive actually mapped to it. Well, we actually have two methods to recognize the drive so that we can access it.

Temporarily Gaining Access to a USB drive

This is actually pretty easy; just right click the F:\ drive and Disconnect it. This will result in the immediate mapping of drive F:\ to the USB stick. This is all good except for two things:

1. If you need to save a file from the USB drive to your F:\ drive, you’ll need to copy it somewhere else first.
2. Once you’ve finished with the USB stick you’ll need to remove it and re-map the drive. The easiest way to do this for most non-savvy computer users is to just log off and log on again.

Permanently Mapping a USB Drive to a Specific Drive Letter

This is a little more work than granting temporary access, but gives a better, more permanent solution. Here’s the steps:

• Plug in the USB drive
• Right click My Computer and choose Manage
• Find Disk Management on the left side of the screen and click on it
• Find the disk that has the correct capacity. If you are using a computer with one hard drive, it will probably be marked “Disk 1″ as “Disk 0″ is your primary hard drive. (For this example I’m going to use a really small, 256 MB USB thumbdrive.)
• Right click the drive, as shown below, and choose “Change Drive Letter and Paths”

• Next you’ll want to click Change
• In the drop-down box to the right of the next screen, choose the drive letter for an available drive. Keep in mind that you can actually choose a reserved (yet not active) network drive here as well!
• When you’ve chosen an appropriate drive letter, say OK and Yes when given the warning about changing drive letters.
• From this point forward, plugging this USB drive in will map it directly to the drive you’ve chosen.

Conclusion

Now, this is all well and good that we can assign a constant letter to our USB drives, and there very well will be situations where this is specifically necessary.  I would argue however, that we should not need to do this just to get access to our drive if we are using a Novell network. Since most hard drives start at C:\, and most computers seem to have A:\ or B:\ free, why doesn’t the first available drive letter get mapped? Okay, granted this is probably not an issue for users who are not using a Novell network, but still. This does seem kind of ridiculous to me.

This is a pretty cool new feature in Excel 2007 that I think will pay some dividends to people.

The main intention is to ensure that users working in 2007 files are notified when they’ll lose functionality by saving back to prior versions.  This is a good thing, of course, but admittedly, it does present itself in a cryptic way under the wording “Minor loss of fidelity”.

One of the really great things in it though, is the fact that it checks for external links when saving.  Of course, in previous versions it was painful to do this.  You either had to write code to do it for you, go browse all the named ranges (Insert|Name|Define), or download Jan Karel’s Name Manger utility to get an easy to read interface.  Needless to say, most people didn’t bother looking at this kind of thing on a regular basis.

Of course, we all know the issues that this can cause.  If your settings were automatically set to update links, then you’d never even know you were doing it.  The values in your spreadsheet could be pointing somewhere else and updated without your knowledge.

I have to admit that since I’ve been using Excel 2007, I’ve now come across 5 workbooks that have had this issue.  Here’s the message you get:

This nice little box is provided as soon as you save the file.  It’s also worth nothing that you can uncheck the box to avoid this message if you know you are using links to external files.  As should be, this setting is maintained on a workbook by workbook basis meaning that you won’t (can’t) accidentally turn it off for all workbooks.

At any rate, a quick trip into Excel 2007′s Name Manager for this file yielded me the following:

As you can see, this link had been carrying forward in the file for over 4 years!  (It is a working paper that we create a new copy of every month, preserving our historical data.)  Fortunately the link was innocuous, as have been all of the links I’ve found so far, but it does have the serious potential for problems.  Suffice it to say that I’m cleaning these up as I find them!

This problem had been bothering me for a while on my wife’s PC.  It happened on her old PC running Office 2003 on XP, and just happened to me again last week when running Office 2007 on Vista.  I went through all the natural steps of disabling all add-ins, and nothing fixed the issue… till about 30 seconds ago…

My (Microsoft) keyboard has an “F Lock” key on it.  I’m not really sure why this key is needed, to be honest, but apparently it turns off the function keys.  So basically, if the light on the keyboard is off, so is my ability to get into the VBE by pressing Alt+F11.  What a pain!  But at least I know how to fix it now.

I don’t seem to recall seeing this key on any other keyboard.  Anyone else run into this or any other weird ghosts that can affect the VBE?

This came as a bit of a surprise to me, but you can no longer open/save a file in the old Lotus wks format in Excel 2007.

Most people probably won’t care about this, but I create batches of journal entries to export to a wks file for upload into an ancient DOS based Accpac 6.0 accounting system.  Fortunately, this database is only used to record a couple of transactions per month, but it does mean that I have to run this process in Excel 2003 as Accpac won’t accept plain on txt files.

The Accpac database is actually only still used because I’ve avoided creating routines for our new accounting system to do the same work.  Maybe it’s time to engineer that process and avoid this issue to begin with…