The Ken Puls Blog

Tonight I discovered that my site had been hacked… as it turns out I was running an old version of Wordpress and there was some injection code fed into a security vulnerability. I’ve upgraded to the latest version, which has cured that issue, but in the process some things seem to have occurred:

• I’ve got some funny looking code samples
• I’ve got a weird A character showing up all over
• I’ve lost my navigation to the RibbonX TOC

I’ll try and fix this up over the next few weeks (kind of busy), but my main priority is going to have to be my main site, which still has some issues and needs a pretty major upgrade…

This has to be one of the weirdest little programs I’ve ever worked with…

Back in 2003 I built a database to track our liquor inventories at work. It’s fairly simple in that it has a table of product details like name, price, size, cost, etc… and a table that records my inventory counts. We used a little Palm Pilot with a built in Symbol scanner to collect the scan date, UPC code and quantity on hand. As we estimate our partial bottles that sit on the bar, we record decimal quantities on hand. (i.e. 2.1 bottles.)

This has worked really well for the last few years, but as with all technology, it wears out. While the database is fine, the Palm Pilot is another matter. The screen calibration went so far off and despite several resets I couldn’t fix it, rendering it useless. The bad news, of course, is that the unit we used has been discontinued for about 2 years, and my last replacement unit… well… it’s now in the trash.

Fortunately I already had a new device on hand to use. We’d installed a new system in another department last year that came with a handheld ruggedized scanner. Very nice little unit made by Unitech (HT-630). We were so impressed that we bought a second one. One reason was that we now have a backup unit, but the other is that the vendor (whom I know fairly well) convinced me that I could use this as a replacement for my aging Palm units. “Oh yeah,” he told me, “it’s fully programmable. You’ll have no problem knocking up a little program for it. There’s a whole programming environment, you build the job, upload it and you’re good to go. It won’t be a problem for you.”

I love that people have that much faith in me sometimes… then there’s other times where I want to phone them up and…

At any rate, I’ve been meaning to get around to programming this new unit, but just haven’t been able to make it a priority. But since our little Palm Pilot blew up, that kind of forced the issue. And naturally it had to happen two days before quarter end. Great. So last Thursday/Friday I spent time trying to get this thing working in time for them to count inventory on Saturday. Nothing like a little pressure!

The odd thing is that this unit is programmed by building a flowchart. I’ve never seen the like before. To me flowcharts are used for documenting how the program works, not telling the program how to work. Very odd. The shot below is from the program I released on Friday to count inventory. (Yeah… I DID do it, but I wouldn’t say it wasn’t a problem!)

This was a real trip to work with. Ever seen a flowchart that is allowed to go multiple directions at once? I haven’t. To me flowcharts have a decision point between every directional choice, but this little program actually flows data along one arrow and the input screens along another one.

And well creating the flowchart is all good, you then have to configure every option of every node in the chart. You can’t copy an existing node that is setup correctly either… each option of each box has to be configured. Each link, each input, everything. This is just one of at least 14 screens to configure. Each line you see (incoming and outgoing) increases that count by 1, (image below has 20 screens to do) so it’s very tedious:

It took me about 1 ½ days to built this program. I spent a ton of time spinning my wheels trying to figure out how to make a lookup file work, then trying to figure out how to write the data to the text file, then trying to figure out how to upload the program to the unit, and then I thought I had it. Then I reviewed the collection file and figure out that it was submitting a full record every time a single piece was captured. So I’d get four records for every UPC code, one with the correct quantity, three with the prior… arrggh!

I also capture the count date and month end date. I was really happy to see that you could use a formula too, so I figured that I could use a formula to convert the count date to the month end date. I quickly jumped into the formula screen… and after staring at that for about 4 seconds I clicked the Help button:

Seriously? “Write pieces of C code”? Try web searching for some help there… even when I did find something, I couldn’t figure out how to make any sense of it!

In Excel this would be easy…. =If(Day(CountDate)<15,Eomonth(CountDate,-1),EOMonth(CountDate,0))

If anyone has any idea how to write that in C code, I’d be really grateful. There is no manual with this software and the “Help” files are about as useful as… well… they’re not. I ended up ignoring the formula and elected to capture the count date twice. I just have to edit the text file and replace one of them with the month end date right now, and I’m good to go.

At any rate, I DID get the program working in time, and deployed it out there. The staff are ecstatic as it is much simpler to use than the last one. Yay, it’s time to celebrate!

Almost.

Today I was trying to import the text file into the existing table in Access. Access is causing me a hell of a hassle as it wants to populate the AutoNumber index field with my text file data. I don’t get that. It was never an issue before. I even put headers in my data fields, but no luck. I may have to resort to VBA to use a SQL insert statement in a loop to get this licked.

Worse though is that I messed something up. All of the decimal counts have been truncated to whole numbers, which makes my count file useless. I HATE having to deploy new technology in a hurry without proper testing. Our only recourse now is to estimate our inventory this month end and deal with fixing the technology issue for next month end.

At any rate, this has been an interesting learning experience. Programming C using flowcharts… it’s just plain weird.

I think it's pretty well known that the Excel macro doesn't always record code when you'd like it to.  I know that there's been several times I've recorded something to get a syntax, and it's been an empty stub after I'm done.  This one was a new one to me though...

I tried to record the creation of a conditional format in 2007 and here's what I got:

The first line was even highlighted red. Seriously, it could get the rest, but not the formula?

Last night I decided to install Windows7 RC on a spare laptop hard drive. So I pulled my Vista drive out of the laptop, slipped in the other drive and away I went.

For reference, I found the install very slow compared to XP or Vista. It seemed to take forever and a day to install it. Oh, and just as a note, you need to license code before it will boot into Windows (duh!). I had to run to my wife's computer to grab a code from MSDN as I kind of forgot that little part.

I'd already installed this on a desktop PC, and found some interesting new features that look nice:

• Screen Magnifier tool (no more need for the magnifier that comes with my mouse software?)
• Sticky Notes (stick right on the desktop)
• Live Preview of application pages/windows from the task bar
• Clipping tools (competes with SnagIt/GrabIt)

Interestingly enough the screen magnifier and live preview did NOT work well on my laptop, where they did work on the desktop.

I'll also say that the version of IE8 included with Win7 sucks. On both the desktop and laptop it crashed repeatedly. I ended up restarting IE8, clearing 2 error messages and going straight to Firefox.com to download a useable web browser.

Windows Live Mesh will not install on Windows7 at this time. That's a bit of a problem for me as I keep some pretty critical documents there to make sure I have a backup of them.

Upon shutting the laptop down last night I also ended up triggering the blue screen of death… I haven't actually seen one of those in a long time.

Of course, I had to work today, so I pulled the Windows 7 drive out and put my old Vista one back in… and just about had a heart attack! The BitLocker drive encryption screen came up. I've never actually seen that before, as it doesn't actually appear to do anything when you turn it on. The momentary panic passed though when I verified that the key I had tucked away was the right one, and I'm up and running again.

We recently updated our main property management system and are now going through the first month since. Over and over again now I'm running up against an issue in the way the vendor decided to start treating dates. In the past they just used "MMM DD', which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:

MAY9/09

MAY10/09

MAY11/09

 

Unfortunately this does NOT translate well into a date format automatically. You'd think that it would convert when you pulled it through the text import wizard, but it doesn't. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can't be used to drive date dependant formulas, and sort like this:

MAY1/09

MAY10/09

MAY11/09

MAY2/09

MAY20/09

 

This is very irritating, and many users would tell you that this isn't trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&" "&MID(A9,4,FIND("/",A9,1)-4)&", "&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a "MMM DD, YYYY" format, which it can interpret. Here's the breakdown:

• Month: LEFT(A9,3) &" " returns the left 3 characters of the text string followed by a single space
• Day: MID(A9,4,FIND("/",A9,1)-4)&", " returns the string in the middle starting with the 4^th character, returning the number of characters between the slash and the 3^rd character, plus a comma and a space
• Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we're not using this spreadsheet in 2100... I can safely say that it won't be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.

It's snowing here… again. We've had more snow this year than I can ever remember in Nanaimo, and it's getting really old. I don't anticipate that this snow will stick, but it's still irritating. So far it has snowed at the beginning of Jan, Feb, Mar and now April, with our first big snowfall of the winter being in mid December. Honestly, there is still a little bit of snow by the stop sign on my street from the FIRST snowfall. Ridiculous.

The bigger part of irritation for me is that it is coming during every month end reporting period, causing us grief on getting to work. Yesterday, as we're busy preparing for our quarter end (which has an EXTREMELY tight deadline), high winds knocked the power out… and now it's snowing.

Grr….

Oh well, we'll get through it. We always do, but I think it's time for spring to come along now!

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...