The Ken Puls Blog

So during my infatuation with converting my workbook to use IFERROR statements throughout, I suddenly realized that the reports I had to send to head office needed to work in Excel 2003. After a bit of… um… motivational speaking… I elected to do the following:

• Create a new workbook
• Copy each of the required worksheets to the new workbook
• Break the links

This method would allow me to take full advantage of Excel 2007's additional functionality, speed up my calc time by using more efficient formulas, and also still provide head office with the reports they need. And easy enough to do as well, since it's just a bit of VBA code to automate it all… right?

Yeah, right. Nothing is ever as easy as it should be.

Copying the worksheets to a new workbook was simple, but the code to break the links is bizarre. I used the code that I'd posted on this blog a while back, but I had to do it in a weird way. Here's the method I ended up using:

• A loop to copy the worksheets to the new workbook
• A call to break links within the loop
• Save and close the workbook
• Re-open the workbook and break the links
• Save and close the workbook

Seems a little redundant, doesn't it?

The reason I ran the link breaking within the loop that I was using to copy the worksheets to a new workbook is because is dramatically improved the speed of the routine. I can only surmise that it did break some links, as it certainly didn't do them all.

After that I tried to break the links with the workbook still open (saved or not), and it didn't have any effect. Basically the only way I found to reliably kill those links was to save, close and re-open the workbook. Very irritating and slow, but effective.

Of course, as trying to work this out wasn't hard enough, I was also battling this issue, which was leaving me with phantom VBA projects. I'm virtually certain that this was affecting some of the code, although I still had to resort to the above method once I had uninstalled that add-in.

My conclusion… breaking links on an unsaved workbook is hokey. This should work.

And just in case anyone wants it, here's the routine I used to break the links:

In a word, linked sub-tasks. I use Outlook every day for my task list, my calendar and my email. I spend hours in the this program and, while I've fully aware I could always use more training in it, I don't think I'm a total dummy in it's use either. (Glad to be proven wrong though!)

Ultimately, what I want from Outlook is an EASY way to manage the projects that I work on. I don't want Microsoft Project and I don't want to have to go to the hassle of setting up Sharepoint for a one-off project. (Steve French showed me a cool trick for doing pushing repeated tasks into Outlook from Sharepoint, but that's overkill here.) The big issue that I run into with Outlook is that I can't link the minor milestones to the overall project goal.

What I do now is create a task, and within it create a table of what needs to be done and the status, like this one that I'm working on now:

This has a few inherent problems. The very first is that the Due Date is what shows in my calendar on the task view. As you can see below, where the work week starts on Sunday, the task is showing up on Friday (the 20^th). I have to complete a lot of these tasks as I go along through the week. So if I don't want an overdue task in my calendar, I have to update that field every day.

What I want is to be able to create all of the milestones into "Sub Tasks", then link them to the main task. When I click on the main task, I should be able to get a status update that shows me exactly what I'm seeing above in the first image… Okay, maybe not subgroup like I've done the "Build Statement" tasks, but I could preface each of those as something if I wanted. With proper sub-tasking, I could then manage my calendar.

Honestly, my needs aren't that big… I just want a central place to manage related tasks as compared to those that aren't. Surely this is something that should be native in Outlook as a "productivity" app, shouldn't it?

Some of you may recall I wrote up a post on things to check before you send your resume out for jobs.  Sadly, based on yet another job posting we've had here, I have to add this point:

Make sure you spell your own name correctly.  Or, if you are going to misspell it, don't include it written one way in your email address and resume, and another in your cover letter.

I seriously wish I was joking about this, but there you have it...

Okay, seriously... what is going on?

Over the past week, I've put my phone on do not disturb.  I've had at least a half dozen calls trying to sell me everything from payroll systems, to box storage and shredding, to offers to help me reduce my American Express costs.

Is this a final sales push of the quarter, or something? I seriously need call display here...

On Tuesday, I left work feeling a little run-down, but generally happy with the things that I'd got accomplished so far this week. I had a bunch of things planned out, and have been able to book time to get certain things done. Overall, the week was looking pretty good. From there, I went to a family picnic event, and progressively felt more and more run-down all evening, as a sore throat manifested. "Crap, I'm getting sick. I don't have time for this." was a recurring thought.

So Wednesday morning I wake up and feel like garbage. Headached, stuffed up head... I called in sick. I figured I've worked enough hours that I can do that, so I did... till my phone rang...

I don't believe that any news could possibly have been worse. The systems at work will let people log in, but they can't access any files. Damn... I drag myself out of bed, log in to work to check our file server... If I wasn't feeling sick before, I sure was about halfway through the day...
What follows is my notes for what has transpired over the last two days. I started taking these to make sure I wouldn't miss un-doing any steps that I'd done for testing, protecting, etc... It's long... about 6 pages, but gives an interesting glance into the lack of control you have as a tech guy when things go wrong, and just how much goes on before we make some of the critical decisions we have to make.

Oh... and because this is pretty much into the detail, I'll give you the readers digest version: The backup software corrupted our files and shut down our virtual file server. Once brought back up and all directory errors had been cleared, there was still corruption in individual files, so we restored our Property Management Software (records all of our sales transactions) to two days previous. The names, of course, have been removed to protect the parties, and all server names were changed.
‘****************

06/18/08 8:46 AM

• File & Print (FP-Server) server is down.
• Called IT Contractor for tech support (my main tech is out of town for a few days, so need them to find me someone else.)
• Let IT Contractor know that I would initiate case with VMWare while waiting for a tech

06/18/08 9:15 AM – 12:00 AM

• Logged case with VMWare. Case log follows:

**** Start VMWare Case Log ****
Problem:
-VM would not start citing inability to read a virtual disk snapshot file
Findings:
-opened up the vmdk descriptor files
-found that the snapshot CID is identical to its parent
-->therefore the parentCID referenced appeared to point to itself
-->however the parent file name was correct
-the parent snapshot did not match up with the base parent CID
-->the filename was correct here as well
-the FP-Server.vmsd file referenced an old filename that did not exist anymore
Resolution:
-hand edited the CIDs in the descriptor files and made up a new CID# for the child snapshot that had the same CID as its parent.
-edited the FP-Server.vmsd file to reflect the proper files
-started up the VM - all looked good
-deleted the snapshots through snapshot manager.Please follow this action plan before creating anymore ESX ranger backups or snapshots
1) make sure there are no snapshots
2) power down the vm
3) un-register it from VC
- right-click over VM -> un-register
4) delete the FP-Server.vmsd (this still contains old incorrect snapshot info)
- # rm /vmfs/volumes/SAN_VMFS_OS/FP-Server/FP-Server.vmsd
5) register the VM with the ESX host
- right-click over datastore in esxhost summary tab --> browse datastore
- browse to /FP-Server
- r-click over FP-Server.vpx -> register VM
6) migrate to desired server
7) start VM
**** End VMWare Case Log ****
06/18/08 10:00 AM

• IT Contractor called. Missed call as on line with VMWare

06/18/08 12:00PM

• Servers restored to production environment
• Called IT Contractor to advise
• Called all departments to advise

06/18/08 1:00PM

• File system issues began surfacing
• PMS Issue – Admin cannot post journal entry. Says “Disk is full”
• Attempted to save file on the disk. Success
• Referred issue to PMS Vendor
• Excel Issue – Admin cannot save Excel file on server

• Checked file permissions, all OK
• Called IT Contractor.
• Referred to email GM as contact was out of office. Did so.

06/18/08 1:38 PM

• Called IT Contractor as no reply
• My contact will track down GM

06/18/08 2:00 PM

• PMS issues appear to be wider spread
• Most departments cannot post sales. Nothing appears to happen. Affects
• Marina
• Pub
• Clubhouse
• Fitness Centre
• Café CAN post sales, but receives “disk full” error when attempting to:
• Print chits (bills)
• Close chits (bills)
• Print cashouts

06/18/08 2:56 PM

• Called IT Contractor as no reply
• My contact still trying to track down GM

06/18/08 3:02 PM

• Spoke to IT contractor’s GM
• New tech assigned to case (Tech's first day on the job)
• Attempted to get Tech logged in to system

06/18/08 3:20 PM

• Spoke to Tech at PMS
• Specific files seem corrupt
• Restored two files from Shadow Copy. GL able to post
• PMS Tech ran a check over the Trial Balance. Out of balance by $2 million
• PMS Tech expressed concern about file restoration
• PMS is made up of hundreds of small files. Corruption seems to be in individual files
• Concerned about restoring individual parts of whole, as it could cause issues
• Can run a tool to identify corrupt files in installation
• Recommending full restore from backup and rebuilding prior days
• Means rebuilding a full day of sales transactions…

06/18/08 3:40 PM

• In contact with IT Contractor Tech.
• Get Tech logged in to our system
• Discussed case to date and issues.

06/18/08 4:00 PM

• Disabled logons to Citrix servers
• Began online Chkdisk of all files
• Online Chkdisk did not repair issues
• Initiated offline scan

06/18/08 6:30 PM

• Offline scan completed
• File system corruption is gone. Logs clean.
• Tests yielded same errors in PMS
• Indicates specific file corruption still exists
• Need to speak to PMS vendor, but Tech has left for day

06/18/08 7:00 PM

• Advised all departments that system will not be on in the AM, and to be prepared to go manual for the next day

‘****************
06/19/08 9:00 AM

• Had admin staff test opening/saving/closing files.
• Issue with corrupt file in “06,30,2008 Deferred Revenue.xls”
• Attempted to restore from Shadow Copy from 06/17/08 5:00PM. Corrupt
• Restored from Shadow Copy from 06/17/08 12:00PM (noon). Restore OK
• Admin staff attempting to print off yesterday’s PMS sales
• Only department with transactions was cafe (no others could ring items in)
• All chits are open because corruption prevented from closing chits
• Cannot run end of day reports without closing chits.
• Generating print screen captures of individual chits

• Collected:
• Posting journals from Tuesday (17th)
• Posting journals from Wednesday (18th)
• End of day updates from Tuesday (17th)
• Placed call to PMS Tech and left message to return call

06/19/08 10:00 AM

• Revoked permissions for “PMS Users” on PMS Share
• Added “Accounting Users” permissions to PMS Share for testing
• Disabled Logons to the following applications:
• POS – Bev Cart
• POS – Bqt Auto Grat
• POS – Bqt Manual Grat
• POS – Std Terminal
• Enabled logons to Citrix servers
• Called all departments to advise and request:
• Servers back up for desktop/email use
• PMS not available
• Please open/save/close any files used
• Placed call to PMS Support to track down (specific) PMS Tech
• Requested to know if PMS Tech is in today
• Support desk said they’d email him

06/19/08 12:00 PM

• Placed call to PMS Support to track down PMS Tech
• Requested again to know if Tech is in today
• Made clear my system is completely down and issue is critical
• Support desk discussing with Team Lead
• Tech trying to track down anyone in Vancouver office
• (Different) Tech connected and ran File Reconstruct Tool for audit purposes

06/19/08 1:30 PM

• File reconstruct tool completed with one error
• Called PMS vendor. Tech (same that ran reconstruct tool) connected and fixed error. Advised us to test again
• Uploads to GL now work
• POS workstations still report “Disk full”
• Called and left message for (originally requested) PMS Tech

06/19/08 2:00 PM

• Ran Trial Balance at May 31 to discover $2.2 million out of balance
• Ran out of balance source journals for May. No out of balance entries
• Ran Trial Balance at Apr 30 to discover $2.1 million out of balance
• Ran out of balance source journals for April. No out of balance entries
• Trial Balances as far back as May 05 are consistently decreasing in out of balance portions

Conclusion: Database is corrupted and any attempt to recover will be time consuming, costly, and suspect of defects

06/19/08 2:45 PM

• Initiated restore from June 16 end of day backup of PMS system.
• Pro Shop database (different system) knocked offline due to heavy throughput on server

06/19/08 3:00 PM

• Originally requested PMS tech (finally) called back.
• Advised to email him when restore was complete

06/19/08 3:16 PM

• Restore complete
• Advised Pro Shop to resume sales
• Attempted to load PMS and received Activation error
• Emailed PMS Tech (and got immediate callback)
• Emailed PMS Tech logs of files that were not restored (as they were not backed up due to files being in use)
• Re-Activated PMS software
• Opened PMS and attempted to preview a trial balance report.
• Error in preview
• Certain Lib files were not backed up and therefore not restored
• Re-installed activation software from CD as it copies Lib files onto system
• Preview ran fine
• Error logs from backup don’t indicate any other file issues per PMS Tech

06/19/08 3:30 PM

• Initiated File Reconstruct Tool to ascertain that all data is in good shape

06/19/08 4:30 PM

• File reconstruct finished error free
• Begin testing phase internally
• Tested posting journal entry – success
• Tested posting A/P invoice – success
• Tested posting A/R transaction manually – success
• Testing automated upload of A/R batch – success
• Prepared for wide deployment
• Reestablished permissions on PMS directory for “PMS Users”
• Re-enabled connections for POS terminals
• Allow users back in gradually and alert to error potential
• Advised Café to boot up, test and call if issues
• Called Clubhouse and instructed to log on, make a sale and printed to kitchen – success
• Called Marina to advise system back alive
• Called Fitness Centre to advise system back alive

All systems appear to be functional at this time.

06/19/08 5:05 PM

• Advised PMS, IT Contractor and internal managers of completion

‘****************

06/20/2008 9:00 AM

• Will begin reconstruction of prior days data
• 06/17/2008 from files
• 06/18/2008 from hand tallied sales lists
• 06/19/2008 from hand tallied sales lists

This is a big irritation...

I've started getting errors every time I open an Office application.  Specifically, the error message reads:

"EXCEL.EXE - Bad Image

C:\Windows\system32\mscms.dll is either not designed to run on Windows or it contains an error.  Try installing the program again using the original installation media or contact your system administrator or the software vendor for support."

After clearing that message three times, it comes up with a final error message entitled:

"EXCEL.EXE - Unable to Locate Component

This application has failed to start because mscms.dll was not found.  Reinstalling the application may fix this problem."

Grrrr....

For reference, the application (be it Excel or Outlook, and I haven't check the others) still open and seem to work normally.

So here's what I know:

• I added an IMAP account to my Outlook settings, synching up my gmail account to Outlook
• I installed Visual Studio 2008
• I installed PefectDisk 2008 (with VMWare))

I believe the problem surfaced when I turned off IMAP and deleted my IMAP account from my Outlook profile.

I've tried the following steps to fix the issue:

• Running a Repaid installation on Office 2007
• Running regsvr32 on the file (it does still exist)
• Uninstalling Office and reinstalling
• Running a complete Registry cleanup (as administator) using CCleaner

So far nothing has worked, and I can't find anything remotely hopeful on the net.  In fact, the only reference I've been able to find about this issue so far has been unanswered public ng threads for Outlook.

Suggestions welcome...

In our Microsoft migration a couple of months ago, we moved from Novell's Groupwise for email to Microsoft Exchange 2003, using Outlook 2007 as a client.  I knew things would be different, but some things just make me shake my head.

My boss just took a week off, and set up an "Out of Office" reply using Outlook's Out of Office assistant.  He then sent an email to himself from home... and no reply.  Strange...

So I ask my tech consultant to look into it, thinking that something must not be configured correctly on the server.  In the mean time, I forward my boss an email from my work address... and get a reply saying he's on vacation.  What the heck?  Now I'm thinking... "Did it fix itself overnight?"  So I send an email to him from one of my many external email addresses, but no luck.  Still broken.

I'll admit that this seemed pretty strange.  It works internally, but not externally.

Then my tech comes back to me with this Microsoft KB entry, which says:

"By default in Microsoft Exchange Server 2003, the sending of out-of-office reply messages to the Internet is disabled. Many administrators do not allow out-of-office reply messages to be sent outside the Exchange organization to prevent unauthorized people from learning when users are out of the office."

What the hell?

Privacy is great, really.  But for crying out loud, this is overboard.  Turning off replies to the internet is the default?  Come on!

Give the paranoid system admins a way to turn the feature off if they're that concerned.  But there is no reason in the world that you should force 90% of users to turn on the feature to protect the 10% of users that deliberately neglected to tell their wife they were taking the day off! Honestly, all the people in the office already know he's gone, responding to them is ridiculous.  It's the ones emailing him from elsewhere that need to know!

This morning I received a few windows updates that really made me angry. It wasn't the updates per se, but rather how they were stuffed into my system. Here's what happened...

Like every morning, when I got to work I booted up my laptop running Windows Vista. I then launch VMWare Workstation, and started up my Windows XP workstation that I use for my day job.* So far so good.

*This may sound strange, but I test a lot of different things, including Vista, trying to find what works with our system. I wouldn't normally run such a heavy host OS, but I'm testing it out at home to ensure that I know how to use it once I decide to make it mainstream at Fairwinds. The fact is that some of our main applications STILL don't work on Vista properly, so I ported my old physical Win XP Pro machine to a VM, and I use that daily to do my work.

Now, the guest OS (Windows XP) was running a little slow at first, but that's not unusual at all. It takes some time to check for windows updates, and do the other normal startup routines for programs I have installed. I didn't really think anything of the speed until I was finally prompted with that nice little windows update message. You know the one... "I've installed updates for you, and I'm going to reboot your system in 30 seconds." The only options you have are essentially, "Okay, do it now then" or "remind me every five minutes that I'm going to reboot your system."

I was working on something important, so I said "Restart Later." All was fine for about 1 minute. Next thing I know, my Windows XP session shuts off, VMWare is telling me something, and my Vista OS starts to shut down!

Now, I have a pretty decent system here, running a Core 2 Duo 2.2GHz processor, with 4GB of RAM. There is nothing slow about this machine, and yet it took 15 minutes for the host to shutdown, install updates, reboot, configure updates, and get back logged in. Grrrr... The reason I said Later is that I'm busy! The problem, of course, was that the Vista host had the same "Do or die" message, only I didn't see it as my Windows XP guest OS was running in full screen mode.

At any rate, after rebooting I opened up VMWare and found that it had suspended my Windows XP machine. Great! I clicked to start it up, then turned to other tasks. (I had a couple of thin clients to configure for users.) I figured I had a couple of minutes, as it does take some time for VMWare to resume from a suspended state. But by the time I turned back to it, the guest was shutting down! Arrrgggghhh! I had again missed the "Piss off" prompt! Another 10 minutes, give or take, until I could get back in to do any work on my Windows XP system!

All in all, I was not impressed, and immediately shut off the "Auto Download and Apply Updates" feature in both the guest and the host.

I suppose that you could argue that I should have known better, and done this ages ago, but I don't really feel that way. I try to adhere to good practices as best I can, and one of those is applying Windows Updates. In addition, look at what Windows own OneCare program says when you flip the settings to "Download and Notify me to install":

What the hell? So because I'm taking more control of my system, it's "At Risk", and I have to stare at a little red icon in my system tray? This is a bit of a misrepresentation to me.

I'm not only choked about the time I lost today, although that does play in. No... it's because this contravenes several best practice points in programming, in my opinion. What if I'd been working on my Accpac 6.0 database that I still use? If I were posting, and VMWare suspended my desktop in the middle of it, the database would have been corrupted. The last time I had a corrupted database, back in 2000 or so, it cost me over $1000 to get it fixed. I don't even know if I could find a tech to do it now, as the software is supposedly "obsolete".

Why, Microsoft, do you feel so comfortable shutting down your clients systems without any regard as to what they are doing? The fact that I missed my host's message is irrelevant. Besides the fact that virtualization is a fact now, it could have just as easily been an issue if I were on the phone with my back turned to the computer. This is blatantly wrong, and WILL cost at least one customer some corrupted data at one point.

This is a serious issue that needs to be rectified immediately. An OS patch should NEVER force a reboot without warning. If you want to pop up a message that strongly recommends a reboot after installing, then fine, but don't force it. Even keep reminding me every 5 minutes that I should reboot to apply the updates, fine. Again, DO NOT FORCE IT! Or try things the other way, and warn me that a reboot will be needed once the update is applied BEFORE you install it. At least that way I'm not caught in the middle of things.

Ultimately, I take a lot of responsibility for my systems, running firewalls, antivirus software and even tape backups. If I delay an update and get hacked in some way, then that's my problem, and THAT is a risk that I'm willing to take. Risking my data is definitely not something I should have to do.

I also have a hard time believing that any update is critical enough that it warrants such a blatant disregard for my system. The point behind these is to protect, not risk the clients system. What's more is that the majority of updates are released on "Patch Tuesday", once per month. So what's another 4 hours?

I am far from impressed with this cavalier attitude to my work schedule and data.

I hate Java.

In my not so humble opinion, Java is by far the slowest executing programming language I've ever run into.  I know that it's supposed to be platform independant, but it truly makes me grind my teeth every time I have to use an app that is coded in that language.

I'm not sure what it is about the apps... the look and feel, they delays in execution, or maybe it is that java programmers don't seem to add the polish that other programmers do.  (Why can't I double click something and have it add to a listbox in any of the java apps I use?  It's a default behaviour in anything coded in other languages...)

A few more things that drive me crazy:

• Java automatically installs its update manager as soon as you install it.  No choice, they just assume you always want it.  (I've usually taken to knocking it out of my boot configurations, as it drives me crazy.)
• Every time you install an update, it installs a quasi-new install.  The old version and the new stay in your "programs" list.  Uninstall one version, though, and the all go, but the others remain in the list.

And the crowning one for me...

Tonight I installed version 6, update 5.  I had to deal with an error similar to that below no less than 4 times:

Clicking Retry (each time), let it complete the install.  Sorry, but send me the whole damn package to begin with.  I don't need to stare at messages from either your crappy programming, timeouts on the net, or whatever else caused this.  It's painfully obvious that this is not the way to deliver an application.

This drives me crazy...

I'm working on my year end at work, and we have a pretty tight deadline to get it out the door.  Naturally, time is of the essence, and anything that slows me down gets me a little frustrated.  This particular problem has been one of my pet peeves since we got this software in a few years ago.  So here goes... if you are building a system, and want to irritate the accountant, here's one way:

Build a General Ledger program that doesn't hold a true mapped chart of accounts, and suppress printing of zero transaction accounts.
I've used at least 4 different G/L packages where you could specify both the account and valid departments in the chart of accounts.  Try and run a G/L listing for an invalid combination, and it comes back with an error that says that you can't.  Kind of what you'd expect really.

With this program, though, rather than put in a true mapping, they just decided that you couldn't post a transaction to an invalid account/dept combination, but they never linked the reporting to that logic.  Instead, they just search the database for any transaction that matches.  Finding none, it won't print a report.

Normally, that would be okay.  The problem is that this becomes an issue for valid accounts.

I am an accountant, and I need to know show a report that tells me what the balance of the account is.  I NEED to be able to print a report that shows the value is zero.  These idiots wrote their program with the following logic:

If the opening balance is zero AND the count of transactions for the period is zero AND the ending balance is zero THEN suppress the printing.

Great.  I actually do have accounts where this happens.  I still want the report, and I can't get one.

As if that weren't bad enough, it gives you no warning when this happens.  No error message, no nothing.  As far as you can see, it prints just like any other report... until you run down the hall to the printer... and wait... and wait... and ask everyone if they took your report... and try it again...  It usually manages to waste at least a couple of minutes before you actually figure out what happened. 

I put this one down to stupid programming logic, and ignorance of the true business need, personally.