Windows XP taskbar icons disappearing?

Here's a great fix for any time where some of your icons are missing from your Windows XP system tray (AKA notification area) at startup. What you need to do is make sure that Universal Plug and Play Device Host is set to start Automatically at startup. Do this by:

1. Start > Run > services.msc
2. Right-click Universal Plug and Play Device Host, choose Properties
3. Change Startup Type to Automatic
   
   
4. Click OK
5. Log off, then log back on (no need to reboot completely)

To test this out before you log off and back on, see if the service is even running. In my case, the service was NOT running, and so I clicked the blue Start link in the upper left. Then, since my Sound icon is one of the icons that went missing, I went into the Control Panel, opened up the Sound properties, then disabled and enabled the Sound icon. What happened? The sound icon actually showed up and stayed put this time.

Did this work for you?

Automating Excel copy and paste jobs across multiple sheets

When I first started this project, I found myself with this Excel workbook that had a few sheets in it, each sheet containing an extract layout for data of a given record type. For example, if the record type was "Person" the extract layout might look like this:

FIRST_NAME
LAST_NAME
PHONE_NUMBER
etc.

Those things in capital letters are called fields, or headers. In my example above, there are 3 fields/headers. In reality, I currently have a record type that has 118 fields. The problem, as you'll soon see, is that these headers are shown in a top-to-bottom format, instead of left-to-right. My job is to get these guys showing in a left-to-right format. (That's easy; there's a Paste Transpose feature in Excel that does this for me--but it doesn't delete all the other crap in the spreadsheet that I don't need.)

I work with a program called PDI (Pervasive Data Integrator) that doesn't much care for the way these fields are presented in my handy dandy Excel spreadsheet. Instead, it prefers to have those headers across the top of the very first row, like this:

FIRST_NAME LAST_NAME PHONE_NUMBER

Over time, this small Excel spreadsheet grew from having 3 sheets (record layouts) in the workbook to 13 at the time of this writing.

My goal was to automate the process of moving those fields from a top-to-bottom format to a left-to-right format while simultaneously deleting all the extra junk that appeared to the right of each field in the spreadsheet, like the length of the field, the field's data type, which database table it came from, etc.

So basically my goal was to turn this:

FIRST_NAME CHAR 12 PERSON 5

LAST__NAME CHAR 17 PERSON 5

PHONE_NUMB CHAR 10 PERSON 5

...into this:

FIRST_NAME LAST_NAME PHONE_NUMB

See all that extra junk I need to get rid of? At first, I recorded a macro in Excel, but I found that this was simply not acceptable for a number of reasons. The main reason is that it wasn't dynamic. It always selected a certain number of fields, and did whatever it was supposed to do. So, if I had only 10 fields I wanted to do my wizardry on and I used my original code, it would have selected 150 fields (from A2:A151 in Excel terms) and when I then imported those fields into my PDI map, PDI would recognize all 150 fields instead of just the 10. Making matters worse, PDI doesn't let me simply delete all of those extra fields at once (it's buggy like that) so I can only do groups at a time.

Basically, it's a waste of time.

So, I then took that code and decided to make it generic and adaptable. But then there was another problem. "Great, I can get it to automate the process on one sheet, but what about the other 12?" Imagine if there were a hundred sheets. Scale it to a thousand. You get the idea. Time-consuming to do, unless you automate that process.

How did I do it? I did it by using a loop within a loop. The first loop is a For Loop. It says, "For sheet 1 through the very last sheet, do this stuff."

The second loop is a Do While Loop. This loop says "while the currently selected cell is not empty, do this stuff." All it does is examine the cell it's on to see if it's blank or not. If it's not blank, it moves down to the next cell and evaluates it in turn. It does this until it encounters a blank cell, indicating that there is no more data I need to worry about. The key here is that it keeps track of the cell number that it's on.

So, I now have a variable keeping track of my starting and ending cells (myRange1 and myRange2, respectively). That means I can store those guys into a final variable called finalRange, which will be used to tell Excel which range of cells to select.

Cool, huh?

The code below will look like crap on this blog since it's in such a narrow column, but copy and paste it into your Excel workbook's VBA window and it'll work.

Here's the code:

Sub PrepForPDI()

'

' PrepForPDI Macro

'

' Keyboard Shortcut: Ctrl+m

'

'Set up the variables we'll be using

Dim myCounter, endCounter, mySheetCount, i As Integer

Dim myRange1, myRange2, finalRange

'Step 1. Starting at the first sheet, do the following. Repeat for each one until the very last sheet.

For i = 1 To Sheets.Count

Sheets(i).Select 'Select sheet i (starting with 1 since i = 1 above)

myRange2 = "A2" 'This is our starting range

myCounter = 2 'Counter that keeps track of currently selected cell

'While the selected cell is not blank, increase my counter and update my ending range

Do While Range(myRange2).Text <> ""

myCounter = myCounter + 1

myRange2 = "A" & myCounter 'The result of this variable would be A1, A2, A3, depending on where my counter is at

Loop 'go back up to the Do While statement and repeat until a blank cell is encountered

'Once a blank cell is found as we go down our column, jump out of the loop and execute the following:

myRange1 = "A2" 'The start of our selection range in the Excel spreadsheet

endCounter = myCounter - 1 'Subtract 1 from the counter since the counter was last on a blank cell

myRange2 = "A" & endCounter 'The end of our selection range in the Excel spreadsheet

finalRange = myRange1 & ":" & myRange2 'This would look like A2:A114 for example

'Now that we have our final range, select it, copy it, paste (transpose) it, delete the

'rows below row 1 since we only care about preserving this new row 1, and select cell A1

'so that we're always looking at the very first field on any given sheet.

Range(finalRange).Select

Selection.Copy 'Copy it. :-)

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Rows("2:150").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

Range("A1").Select

Next i 'Now i = 2, so when the For statement loops back to Step 1, it will select Sheet number i, or Sheet number 2.

'Select the very first sheet to bring us back to home base.

Sheets(1).Select

End Sub

Quicken: Bank of America and the OL-226-A Error

UPDATE: Are you still a Quicken user? What are you doing? Go download YNAB and change the way you budget... for the better.


----- Original Post -----

QUICK & DIRTY:
Here's a quick PRO TIP that WORKS for the infamous OL-226-A Error that Quicken produces whenever you try to download your Bank of America credit-card-only (i.e., you don't have a checking account with them, just a credit card) account information via Direct Connect:

Create a new account and choose Bank of America - All Other States. Once your new account is created you'll need to categorize all your transactions, but from here on out you should be able to download your monthly statement via direct connect without issue.

DETAILED BACKGROUND:
For years, I was an MBNA credit card customer. Then in January 2006, Bank of America bought MBNA, and I became a Bank of America customer by default. During this transition, an important banking feature seemed to become quite flakey: the ability to download account informaiton (transactions) directly into Quicken, without going to my bank's web site and downloading them separately.

The inconvenience of not being able to download transactions directly through Quicken was one reason I lost touch with my finances for some quite some time--it was always such a pain to go update my account that I kept putting it off until there was so much data to go through I gave up all together.

Recently, I found myself in a situation where it now made sense to track my income and expenses. I went through all the motions and downloaded my transactions from my financial institutions, categorized the transactions, etc. All was well--even Bank of America--until the very next day after I set up all my accounts.

For some reason, my Bank of America account was only able to download transactions the very first day. Today, it decided it didn't want to update. Now... having had plenty of problems with Quicken in the past, I immediately wrote it off as a quicken problem and began my search.

The error code Quicken gave me was OL-226-A. Seems like lots of people had this error, and most of them had to do with Bank of America. I later learned that many of *those* people were specifically using Bank of America - California specifically.

After lots of troubleshooting over many weeks and months, I finally broke down and called Bank of America, where I waited on hold for over 20 minutes before I spoke to a rep, who proceeded to tell me that I could not use Quicken for a Direct Download of financial transactions because I had a credit-card-only account. He went on to say that if I had a checking account with them, I'd be able to do it no problem.

"No way," I thought. I need a second opinion. So I spoke with the rep's manager, who proceeded to tell me the same story: I must have been downloading transactions all these years by, and I quote, "some unexplained glitch in the system."

I'm sorry, what did I just hear? That must mean that somehow I've been beating the system all these years until something went haywire.

<FoulLanguageWarning>
Excuse me? I think I need to grab my boots, because the bullshit's getting pretty thick. I mean, what kind of shit is that? This is the largest fucking bank in these United States of America, and you mean to tell me that I can't download my transactions using the most popular financial software on the fucking planet? Something's wrong here. Something doesn't add up!
</FoulLanguageWarning>

So after I got off the phone, flaming pissed, I set out to end this bullshit once and for all--after all, I can't leave my boots on forever.

About 15 minutes of serious digging landed me on this gem of a thread from Quicken, which I seem to have misplaced because I first started this post back in December of 2008. (Side note: wow, the blog post that time forgot.)

In any event, I was able to delete my current Bank of America account in Quicken, the re-establish it using this little trick: when Quicken asks you what state your account is located in, choose Bank of America - All Other States.

That's it! So far, it seems to be working well... but I must say, I'm a Mac user once again and I've been waiting eons for Quicken to come out with a Mac version of Quicken... and I've been let down yet again. Quicken Financial Life for Mac will not be coming out until at least February 2010. I say "at least" because they company has pushed it back several times since 2008. At this point, I think I'm considering moving on to something like Mint.com -- I've been trying the two in parallel, and I'm noticing some interesting differences.

For example, Quicken only downloads Bank of America's credit card statements once per month as an entire statement; Mint, on the other hand, downloads transactions on a per-transaction basis, as if you were logging into the site directly and viewing the latest and greatest. This is particularly handy because after a month passes and I download all my transactions into Quicken, I often wonder what certain expenses are that I don't have receipts for. With Mint, I can check it daily, weekly, whenever the mood strikes me.

What's more, while both programs automatically categorize your downloaded transactions, Mint puts Quicken to shame because it is constantly improving its algorithms/database to predict which category your income and expenses really are. Unlike its aging great grandfather Quicken, which seems a bit like it's playing pin the tail on the donkey--in the dark.

Would I switch to Mint today? Probably not. While graphs are nice, I like the reporting in quicken because I use a zero-based budgeting system and need to make sure certain numbers get down to zero by the end of the month... reporting makes that easy. I also like the flexibility of entering transactions manually--like when you write a check today and nobody deposits it for a month--that Quicken has and Mint desperately needs. Sometimes those laggard checks get deposited at the wrong time, creating surprises. But had I been able to enter that transaction manually, I'd have seen that my balance really wasn't $1,000 but $1,000 minus that $500 check and, well, I wouldn't have bought that $501 collector's postage stamp. ;)

Phew, that was a long post. If you're still reading this, you either really liked what I had to say (thank you) or have too much time on your hands. Or both. ;)

Till next time, World!

Skype crashing on you? It might be a driver issue.

Does Skype keep crashing on your Windows machine? Try this helpful hint: Make sure that your webcam's driver is up-to-date, preferably using drivers developed by the manufacturer.

My mom and I recently had this problem where we couldn't audio or video chat for longer than just a minute or so--sometimes the conversation lasted only seconds. It was very buggy. Skype would crash on her computer, but not on mine (I'm on a Mac, for reference). We found it interesting because I could skype just fine between my home Mac and my work's Dell laptop, so I isolated the problem to my mom's computer, which is a fairly recent and capable model.

The problem stemmed from us not using the correct driver for the web cam, a Logitech Pro 9000. After some troubleshooting, we discovered that Windows automatically installed a default generic driver for the webcam. So we went over to Logitech's website, downloaded the latest driver for it, installed, rebooted, and presto. As I write this blog, I'm video chatting with my mom and the timer is 40:21 and counting.

Scotty: Log Me In

Recently I've found myself spending more and more time wishing I could log into my home computer from work. My home computer's a Mac, which explains why I can't simply Remote Desktop from my work laptop running Windows XP. In addition to that, the corporate firewall creates more connectivity issues than I knew existed. What a drag.

But then I found a service on the internet called LogMeIn. All you have to do is create an account, then download the LogMeIn software to the computer you want to log into from a remote location. Once that's done, remote control of your computer is as simple as visiting LogMeIn.com, and literally logging in. Once authenticated, you can click the Remote Control button and be connected to your home computer via your web browser. (Side note: for the best experience, you'll need to install a small browser plug-in, available for Internet Explorer, Firefox, and other browsers.)

Amazingly, this even works using the iPhone. If you're on a WiFi connection, you can access your home computer (or whatever other computer you've set up with the LogMeIn software) remotely and I must say it works great.

Best part: the service is free. You can't copy files or print to a local printer--for that, you pay extra--but that's why it's free. LogMeIn is willing to bet that if you start using its free service, you'll eventually come to appreciate the value of the added functionality of the paid services it offers, of which there are several.

iTunes won't close on Windows XP (SP2); uses 50% CPU

UPDATE 6/16/08: If you run NOD32 as your antivirus program, you might want to try this little trick (thanks, nka!): This article describes how to add iTunes to NOD32's exclusion list if you're running Windows XP. If you're running Vista, simply upgrade (free) to the new version of NOD32. If that doesn't work for you, try what I mentioned on the 5/16/08 update below.

UPDATE 5/16/08: Astute readers point out that simply disabling the Bonjour service as follows should solve the problem: Start > Run > services.msc > Double-Click on Bonjour service > Stop it > and in the Startup Type dropdown list, select Disabled. Then disable sharing in iTunes.

If you're reading this, it's probably because you're running Windows XP and iTunes won't exit/close/quit on its own unless you force it to quit (i.e., End Process in the Task Manager). You've been searching for a solution, but have found none.

Maybe you're just about to give up hope. I know I almost did. But then I fought back--and found an answer. Fair warning, though: it's a little extreme. You can experiment and cut out certain steps as you see fit, but I can't guarantee you'll enjoy the success I am currently enjoying. It's up to you.

And so, without further adieu, here's how to get iTunes back up and running (and closing on its own) once again:

0. Read through these instructions to have an idea of what you're going to do before you decide to do it.

1. Add/Remove Programs

2. Remove iTunes (In addition, I removed ALL Apple products, just to be safe, listed as 2.1-2.4)
2.1. Apple Mobile Device Support
2.2. Apple Software Update
2.3. Bonjour
2.4. Quicktime

3. Delete all leftover Apple-related files from C:\Documents and Settings\*
3.0. Note: do NOT delete anything in C:\Documents and Settings\YOUR USER NAME HERE\My Documents\My Music\iTunes (or wherever you store your iTunes music library file. iTunes will automatically use these files again if they're in the default location.
3.1. C:\Documents and Settings\All Users\Application Data\Apple Computer
3.2. C:\Documents and Settings\YOUR USER NAME HERE\Application Data\Apple Computer
3.3. C:\Documents and Settings\YOUR USER NAME HERE\Local Settings\Application Data\Apple
3.4. C:\Documents and Settings\YOUR USER NAME HERE\Local Settings\Application Data\Apple Computer

4. Delete all leftover Apple-related registry entries from the Registry
4.0. WARNING: Make a backup copy first. If you don't know much/anything about the registry, I'd skip this part unless you feel ballsy and like living on the wild side.
4.1. I use a tool called jv 16 PowerTools 2007 to make this task a little more efficient. Otherwise, it's time to just use the Find funciton of the Registry and do the dirty work.

5. Restart (save how-to guide, if you're writing one at the time) ;)

6. DO NOT UNDER ANY CIRCUMSTANCES WHATSOEVER enable the "Share my library on my local network" feature. This will result in iTunes exhibiting the lock-up you experienced upon closing, and it is impossible to recover from this and reset your iTunes preferences unless you follow the painful steps above. This is about my 3rd or 4th time doing this, so I'm confident this will work for you.

Good luck!

Windows Task Manager (Not Responding)

You know it's a bad day when Windows Task Manager decides it's Not Responding.

...and iTunes reports it's using 116MB of memory.

FAIL.

Safari for Windows is out

In its classic, secretive fashion, Apple has just announced the launch of its Safari web browser for the Windows platform. It works on both Windows XP and Vista. It still shows signs it's in beta form, as the application crashes every now and then when browsing certain web sites.

Can't really say much about it, other than I am pleased to see that much of the Apple user interface has made it into this program, right down to the aqua scrollbar, arrows, and smooth flowing dialogue boxes (try bookmarking this page in Safari, for instance). They even got the glowing buttons right.

However, this is a beta and there are plenty of things to complain about. Since there are thousands of other tech writers out there I'll leave the details to them. I just wanted to write this post in Safari to test how it interacts with different web sites.

It's no replacement to Firefox just yet since it's still in beta, but once it's a finished product for Windows, it'll be a contender. The one thing I really like about firefox is all the plugins. NoScript, FasterFox and the IE Tab plugins are truly useful, as is the crash guard built into Firefox. (If the browser crashes for some reason, you can restore your previous browsing session, for example.)