Derek Pinkerton's blog

Absenteeism

2009-06-25T23:17:00.002-04:00

Well I know there aren't many people who have even looked at my blog, but for those of you whom have sorry I dropped off the radar for 2 1/2 years. Wow I can't believe it's been that long, and for that matter that blogger never deleted my account! :) On that note, I have decided to delete this account soon, but no worries I have started a new blog that will be hosted on my very own spank'in new website http://www.derekpinkerton.com. (Insert lack of creativity joke here.) I'm hoping to start posting a bit more often, than once every 2 1/2 years. A modest goal I know but hopefully one that I can acheive. :) I have copied all posts up to but excluding this one to my new site already.

For my new website I am planning on more than just a blog, although I'm not quite sure exactly what that means yet. For starters the new site is running an ASP.Net blog engine called BlogEngine.Net (available here) I have created a customized theme for the site, but haven't had much time to do anything else just yet.

So again I bid you adieu from this blogger site and hope to see you on the other side.

Excel: my new favorite program :)

2006-12-01T13:53:00.000-05:00

I have an excel spreadsheet with a custom vba function that will simply return the document property with the name given in the parameter. This function works wonderfully. I can enter it as the formula for a particular cell in the worksheet (such as =DocProperty("MyVariable")) and that cell will now display the value of that document property.

Although this does work as expected the first time the cell's value is calculated it does not automatically update if the document property changes. Excel seems to cache the value of each cell. This makes sense for performance issues however I am using DSOFile.dll to update these document properties from another application (namely Qualtrax.) With this setup the values are changed when the document is closed and I would like the new values of these variables to be displayed each time the document is opened.

I have seen similar behavior with "Fields" in Word documents where the displayed value does not correspond to the actual value stored. The solution in Word documents was to add a VBA macro that runs automatically when the document is opened, so I figured I would create a similar macro that would update my Excel documents. I figured something like the following would work:


Public Sub Auto_Open()
    Dim ws As Worksheet
   
    For Each ws In ThisWorkbook.Worksheets
        ws.Calculate
    Next ws
End Sub

However I wanted to be sure that in the event of a worksheet with a large number of calculated cells I only update the ones that need to be udpated. So I came up with the following code:


Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Calculate
            End If
        Next cell
    Next ws
End Sub

Much to my surprise this function did not work. I stepped thorough and discovered that the cell.Calculate call was being made for each cell whose formula was a call to my DocProperty function (shown below), however for some reason Excel is not actually recalculating the value! After a couple hours of torment I found that resetting the formula on the cell will force a recalculate so simply changing cell.Calculate to cell.Formula = cell.Formula fixes the problem and actually makes Excel recalculate the value of that cell. WHY OH WHY does the Range.Caclulate function not actually force a recalculate of a cell whose formula calls a vba function?!?!?

Complete working code.


Public Function DocProperty(property As String) As String
    Dim WB As Workbook
    On Error Resume Next
    If TypeOf Application.Caller Is Range Then
        Set WB = Application.Caller.Parent.Parent
    Else
        Set WB = ActiveWorkbook
    End If
    DocProperty = WB.CustomDocumentProperties(property)
    WB.Saved = True
End Function

Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Formula = cell.Formula
            End If
        Next cell
    Next ws
End Sub

The Wild

2006-09-21T11:00:00.000-04:00

Disney recently released it's latest animated film "The Wild" on video and DVD. My wife and I decided to rent it last night and watch it with our one and a half year old daughter. Did Disney fire all of their talented writers? This was the worst Disney movie I have ever seen. It was so incredibly boring that I kept thinking to myself "It has to get better." My wife was ready to just turn it off less than half way through. The plot to this awful movie is somewhat similar to the recent movie Madagascar, animals escape from the New York city zoo and go to the wild. The major difference between the two is that Madagascar was actually entertaining. Take it from me, don't waste your time or money on watching this pile of crap.

Pay it Forward

2006-08-14T11:36:00.000-04:00

Over the weekend my wife, daughter and I went to the annual CCS company picnic. On our way home we decided to stop by Brusters for some ice cream. We had never been to a Brusters before (and only knew about it by having driven by the week before) so we had no idea what to expect. I ordered a single scoop of Chocolate Raspberry Truffle and my wife a Peanut Butter Cup Sunday. It was only after the ice cream had been scooped that I realized that there weren't any Visa or Mastercard logos to be seen. Me being someone that does not often carry cash and relies heavily on my debit card I had not even thought to ask if they accepted debit or credit cards. Well just as we were about to have to leave to get some cash one of the other patrons, a nice couple that looked to be in their 30's, offered to pay for us. I was completely amazed, and with a bit of reluctance accepted. They only asked that we "Pay it Forward" when we come across someone else in need. If you are reading this, thanks again, your generosity will not be soon forgotten!

Oh and in case anyone was wondering the ice cream was delicious! If you are ever in the Southwest Roanoke area I highly recommend stopping by Brusters for a waffle cone!

Fundraisers for Ian Herbst

2006-08-14T10:53:00.000-04:00

The following restaurants have agreed to donate a portion of their sales for a certain night to Ian's campaign--so if you are in the area, let's get the crowds out! Hey--people have to eat anyway, right, so they may as well eat and contribute? Mark your calendars and foward to all your friends!

Sept 11th Arby's in Radford Charity night

Sept 21st Chic-fil-a in Christiansburg Charity night

Oct 24th Texas Roadhouse charity night

If you would like more information about Ian Herbst please check out www.ianfund.com.

Formatting QualTrax dates in Word headers and footers

2006-07-28T08:35:00.000-04:00

When QualTrax inserts dates fields into Word documents the entire date and time is displayed, such as "Monday, July 17, 2006 2:59:44 PM". In some cases you may want to show the short date format instead, such as "7/17/06". Here are some simple instructions on how to format your QualTrax dates in Word documents.

1) First go to Insert->Field like you normally would to add a QualTrax field to the document.
2) Select DocProperty and then select the field name that you would like to add such as ##DATE_PUBLISHED##.
3) Now before clicking OK to close this window, click the "Field Codes" button in the lower left corner of the window.
4) You should now see a box titled "Field codes:" that should look like "DOCPROPERTY ##DATE_PUBLISHED##" we will need to add the formatting code to the end of this field. First add " \@ " (without the quotes) which tells word that the rest of the text we add is formatting text. If your formatting text will include any spaces you will need to enclose it with double quotes.
5) Next add one of the following formatting codes to get the desired date format.

MM/DD/YYYY => 02/06/1981
M/D/YYYY => 2/6/1981
"DDD M/D/YYYY" => Fri 2/6/1981
"DDDD MMMM D, YYYY" => Friday February 6, 1981

Here's some more information on each of the formatting characters and what they mean.

Year
Displays a year as two or four digits, depending on how many letters 'y’ you use. The letter ‘y’ can be either uppercase or lowercase.

Parameter	Displays	Example
y or yy	The year in two digit form, with a leading 0 for years 00 to 09	06 or 06
yyyy	the year in four digit form	2006

Month
Displays a month as one or two digits, as a three-letter abbreviation or in full, depending on how many uppercase letters ‘M’ you use (lowercase letters ‘m’ represent minutes).

Parameter	Displays	Example
M	The number of the month as a number without a leading 0 for single-digit months	7
MM	the month as a two-digit number with a leading 0 for single-digit months	07
MMM	the month as a three-letter abbreviation	Jul
MMMM	the full name of the month	July

Day
Displays a day of the month or the day of the week, depending on how many letters ‘d’ you use. The letter ‘d’ can be either uppercase or lowercase.

Parameter	Displays	Example
d	The date of the month as a number without a leading 0 for single-digit days	27
dd	the date of the month as a number with a leading 0 for single-digit days	27
ddd	the day of the week as a three-letter abbreviation	Thu
dddd	the full name of the day of the week	Thursday

Hour
Displays an hour of the day as one or two digits based on either a 12-hour clock or a 24-hour (military) clock, depending on whether you use an uppercase or lowercase ‘h’. A lowercase ‘h’ bases time on the 12-hour clock. An uppercase ‘H’ bases time on the 24-hour (military) clock. You can also include ‘AM/PM’ in uppercase or lowercase.

Parameter	Displays	Example
h or H	The hour as a number without a leading 0 for single-digit hours	>8 or 8
hh or HH	the hour as a number with a leading 0 for single-digit hours	08 or 08
am/pm	‘AM’ or ‘PM’	AM

Note: You can change the AM and PM symbols that Word uses by changing the Time settings in the Regional Settings dialog box in the Windows Control Panel. For example, you could use this to force the ‘am/pm’ parameter to display as ‘a.m./p.m.’.

Minute
Displays the minute as one or two digits, depending on how many letters ‘m’ you use (uppercase letters ‘M’ represent months).

Parameter	Displays	Example
m	The minute as a number without a leading 0 for single-digit minutes	43
mm	the minute as a number with a leading 0 for single-digit minutes	43

Second
Displays the second as one or two digits, depending on how many letters ‘s’ you use. The letter ‘s’ can be either uppercase or lowercase.

Parameter	Displays	Example
s	The second as a number without a leading 0 for single-digit seconds	10
ss	the second as a number with a leading 0 for single-digit seconds	10

QualTrax Training

2006-06-27T16:08:00.000-04:00

Yesterday we had a customer coming out for a one day training session on QualTrax, this is generally a two day course and is usually given by our Training Coordinator Patsy Clark. Patsy is currently out on vacation so it was up to me to train this person. Although I know the product inside and out I have never given a training session before. Well on my way in to work I got caught up in a mess of traffic on the 460 (Orange Ave.) in Roanoke that was all caused by a small mudslide. VDOT closed one lane which forced traffic to back up for miles. I ended up showing up 45 minutes later than I had planned on, which was only a few minutes later than the customers arrival. Luckily Vicky came to the rescue and started the training session on time in my absence. Thanks Vicky! :) To make matters worse as soon as Vicky left the room and I took over the training, the wireless router we use to connect each of the training computers suddenly stopped working and I had to get our IT Guru Kevin to come fix it. It turned out the power cord broke, but we did have a spare that we were able to use instead. After all this we finally got back into the training and the session seemed to go very well.

Gotta Love Word!

2006-06-06T09:07:00.000-04:00

Last week I had a customer that wanted to add a calculated field "Effective Date" to his Word documents that would automatically be updated with each revision of the document. This field would be calculated as the published date of the document plus 2 weeks. QualTrax itself will only enter the actual date of an event (such as published) so in order to get a date calculated based upon the published date I had to turn to Word. In researching this I found that there were two ways to do to this. The first method is pretty straight-forward; you simply write a vb macro to get the date published (inserted automatically by QualTrax as a Document Property) add two weeks and store this date into a new Document Property, which can then be accessed in fields in the same way as all other QualTrax variables.

Method 1 (Macro): With this method you would add a macro to each document that would calculate the "Effective Date" based upon the Published Date and store it into a new document variable called ##EFFECTIVE_DATE##. This macro would be setup to run automatically each time the document is opened so that you are always viewing up to date information. the problem with this is that if the person to open this document has their Word security setting set to anything higher than low, they will get a warning message asking them to enable/disable the macros. If they decide to disable the macros within the document, the information will not get updated and they may be looking at an old copy of the data. If you have the document converted to PDF using the PDF Module you will not have this problem.

The steps to implement method 1 are as follows:
1) Open the document in question and go do "File->Properties" and click on the "Custom" tab.
2) In the "Name" field enter "##EFFECTIVE_DATE##" without the quotes.
3) Select "Date" in the "Type" drop-down list.
4) Enter any date in the "Value" field. It doesn't matter what date you enter as this will automatically be overwritten when the macro runs.
5) Click the "Add" button then click "Ok"
6) Insert the field into the appropriate place in the document using the "Insert->Field" command like you normally do.
7) Go to "Tools->Macro->Macros" if you have already entered the "AutoOpen" macro select it and click Edit, if not type "AutoOpen" into the Macro Name field and click the Create button.
8) Overwrite the auto-generated code with the following:

Sub AutoOpen()
Set tmpProps = ActiveDocument.CustomDocumentProperties
dPublishedDate = tmpProps.Item("##DATE_PUBLISHED##").Value
dEffectiveDate = DateAdd("d", 14, dPublishedDate)
tmpProps.Item("##EFFECTIVE_DATE##").Value = dEffectiveDate

Dim aStory As Range
Dim aField As Field
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
aField.Update
Next aField
Next aStory

ActiveDocument.Saved = True
End Sub

Method 2 (Field Code): With this method you would add in a field using the "Insert->Field" command and put in some custom field codes that will calculate the effective date. This is much more difficult than using the macro because you cannot simply copy and paste the field code into the Word document. Most of it can be copied and pasted however each pair of curly braces {} would have to be entered by pressing ALT+F9 which turns out to be a bit of a pain especially with how long the field codes are.

{QUOTE
{SET Delay 14}
{SET a{=INT((14-{DOCPROPERTY ##DATE_PUBLISHED## \@ M})/12)}}
{SET b{={DOCPROPERTY ##DATE_PUBLISHED## \@ yyyy}+4800-a}}
{SET c{={DOCPROPERTY ##DATE_PUBLISHED## \@ M}+12*a-3}}
{SET d{DOCPROPERTY ##DATE_PUBLISHED## \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{=dd*10^6+mm*10^4+yy \# "00'-'00'-'0000"} \@ "dddd, d MMMM yyyy"}

I made each of the curly braces in the above code bold so you can tell them apart easier. Again each of these pairs of curly braces must be entered in Word by pressing ALT+F9 rather than typing them in.

You would also need to keep in mind that Word 2003 does not automatically update these fields so in order to force an update in this version of Word you would need to add in a macro that would do this.

Although you do have the security warning to worry about, method #1 would be my recommendation. It would be easier to implement and can easily be copied from one document to another.

*Credit for the Word field codes above goes to Woody's Lounge. See this forum post for more information.

Clear Type: Better readability for all!

2006-06-06T08:50:00.000-04:00

A while back I came across some information about the Clear Type technology that is included in Windows XP, but disabled by default. Clear Type adds lines of resolution to font rendering by working with sub-pixel coloring which makes fonts appear smoother and more readable. According to Microsoft this technology has the biggest impact on LCD displays but can marginally improve some CRT displays as well. I have not turned on clear type font rendering on a computer with an LCD display yet so I have not bed of comparison, but I really love the improvement it has made on my CRT!

Microsoft has an online tool for turning on and tuning your clear type fonts available at http://www.microsoft.com/typography/ClearType/tuner/Step1.aspx. They also have a power toy available at http://www.microsoft.com/typography/ClearTypePowerToy.mspx which will install itself as a control panel applet.

Floating over an IFRAME

2006-05-30T15:50:00.000-04:00

In some recent work on the new QualTrax user interface I had to load a document into the main interface using an IFRAME. This worked great using the Response.BinaryWrite that I mentioned in my previous post to dynamically load the correct document to the user's browser. While I was testing this I noticed that a "pop-up" menu I had, which was actually a div whose visibility was set to hidden, and then reset to visible when the user clicks on a certain menu item, was appearing below the document that I loaded into my IFRAME.

Of course a pop-up loading behind something else isn't very useful so I found the following workaround that allows you to "float" div tags over an iframe.

Let's say my original source looked something like the following:

<div style="position:absolute; left:100px;top 100px; height:400px;">
Floating Menu content Here
<a href="someOtherDoc.htm" target="document">
</div>
...
<iframe name="document" src="someDocument.htm" Frameborder="0"></iframe>

If the IFRAME in the above source happened to lie in the same space that the absolute positioned DIV, it would get precedence over the div and display above it. From what I have read on the subject this is because the iframe is a windowed control, which means that the same thing will happen with any other windowed controls like the drop down list. I found a few suggestions online that did not work for me so here is what I came up with that does work (at least it has for me :) )!

<div style=" position: absolute; left:175px;width:300px; top:105px; height:400px;visibility:hidden; z-index: 10; background-color:White;border:solid 1px Gray;">
Floating Menu content Here
<a href="someOtherDoc.htm" target="document">
</div>
<iframe id="treeHolder" frameborder="0" style="position:absolute;z-index:9;left:174px;width:303px;top:105px;height:402px;filter:alpha(opacity=0);"></iframe>
...
<iframe name="document" src="someDocument.htm" Frameborder="0" style="z-index:1;"></iframe>

In the above code I am using another IFRAME that will sit just underneath the "pop-up", and both of these will appear on top of the IFRAME that my actual content is loading into. (Notice the Z-Index of both the div and both iframes.) The "filter:alpha(opacity=0);" style that is applied to the new IFRAME makes it fully transparent so it does not cover the pop-up. We have to do this because if you set it's visibility to hidden, or it's display to none it will not be rendered and therefore the pop-up will still pop under.

ASP.Net 2.0 Response.BinaryWrite

2006-05-26T14:17:00.000-04:00

I couple of weeks ago I started getting involved with the User Inteface team on the new version of QualTrax, which is due out later this year.

In order to ensure any documents stored in QualTrax are as secure as possible they will all be stored outside of the web-root. This means that they will not be accessible outside of QualTrax. There will not be a direct URL to go to any document. The documents can be stored anywhere on the server, or on an entirely different server. This also means that we have to come up with some alternative means of sending the file to the user. A common method of doing this in the past would be to copy the document into the web directories, and then remove it after a predetermined amount of time had passed. While this does work, it is not the most secure method available, and it requires you have a service running in the background to clean up these files periodically.

A good alternative for this is to use the ASP.Net Response.WriteBinary method to send your file to the browser. Here is an example of this:

Response.Clear();
Response.Buffer = true;
Response.ContentType = @"application/pdf";
System.IO.FileStream myFileStream = new System.IO.FileStream("Filename.PDF", System.IO.FileMode.Open);
long FileSize = myFileStream.Length;
byte[] Buffer = new byte[(int)FileSize];
myFileStream.Read(Buffer, 0, (int)FileSize);
myFileStream.Close();
Response.BinaryWrite(Buffer);
Response.Flush();
Response.End();

This code is in an aspx page that I send in my document ID as the only parameter. So to bring up the document my url would look something like "showdocument.aspx?ID=XXX"

In a perfect world this would work as expected on all browsers, however we all know that this isn't a perfect world so as you might guess I had some problems with it. In particular Internet Explorer (who would have thought) has a problem with this. In researching this problem a co-worker (Vicky Herrala) came across this microsoft knowledgebase article, which points the blame at a script not returning a "content-length" header. We found that you can add this header by adding the following line to the code above.

Response.AddHeader("Content-Length", FileSize.ToString());

After adding this line, I tested again only to find that IE still had problems with this. Firefox renders it perfectly. ;) Looking into this further we found an msdn article that explains how IE decides how it should open the file. According to this article IE should be looking at "The server-supplied MIME type, if available." As you can see in the above snippet, we have specified the content type and yet IE still cannot render this document correctly. The msdn article also mentions that IE will examine the URL in an attempt to determine the content type. In order to fool Internet Explorer into realizing that this is in fact a PDF document I added a dummy parameter to my url that puts in a fake document name with the appropriate extension. My url now looks like "showdocument.aspx?ID=XXX&doc=test.pdf" I fired this up and was presented with my PDF!

I know there has to be a better way to do this so if anyone out there finds a suitable replacement please let me know, but as for now this is working.

Happy Mothers Day

2006-05-16T16:14:00.000-04:00

This mothers day my wife and I decided to go out to lunch. We got up mid morning and I was getting ready to take a shower. I turned on the water and much to my surprise water started spraying from the wall! That's right straight from the wall not the showerhead! I shut off the water and decided to try tightening the pipe that comes out of the wall that the showerhead is attached to and it broke off in my hand!

My wife and I got ready using a different shower and went ahead with our lunch plans. After I went to Lowes to buy the new parts and ripped a hole in the wall to repair the pipes. Being the master plumber that I am I asked my brother-in-law, who just recently built a shower, to help me out with welding the new piece in place. He used the torch to remove the old fitting and as he went to put the new fitting on the pipe he dropped it in the wall! Rather than knocking another hole in the wall to get the part out he decided to go pick up a new one. By this time it was 8:30 at night on Sunday and Lowes closes at 7:00, which my brother-in-law found out only after he drove a half hour to get there.

Monday night I went ahead and repaired the pipe myself and it works great! It took all of about 5 minutes to weld in place. Now all I have to do is patch the hole in the wall.

Q-Tip #2

2006-05-11T09:06:00.000-04:00

Can I add a link to my toolbar that will open a specific instance of a workflow by entering the workflow ID number?

Yes! To add a "Goto Workflow" link to the left hand toolbar go to Administration->System->Toolbars select "Button" from the drop down menu and click the "Add Toolbar Field" button. In the next screen type "Goto Workflow" (or whatever you would like it to say) in the "Caption" box. For the URL copy and paste the following javascript code.

javascript:void window.open('Default.asp?PageID=30005D00&WorkflowID=' + prompt('Please enter a Workflow ID', '0'), 'Bottom');

Q-Tip #1

2006-05-05T16:09:00.000-04:00

Q-Tip, a term I have my co-worker Vicky Herrala to thank for, stands for QualTrax Tip. This tip was recently featured on a newsletter sent to our customers but I figured I would repost it here for anyone that missed it.

When my users click on a link in an email, QualTrax prompts them to login even if they are already logged in. How do I get QualTrax to recognize that the user is already logged in?

When users click on a link from within an email, the link needs to tell QualTrax who to expect to be logged in. If the link does not do this, QualTrax will simply prompt the user to login to continue. The solution for this problem lies in your email actions (Administration->Email->Actions.) Any link within an email action that links to something in QualTrax that requires the user to be logged in should have a section that looks like "&PersonnelID=##RECIPIENT_ID##" If the link in your email action does not contain this part, you can simply add it to the end! For example the following link for document approval will prompt for the user to login, whether he or she is logged in or not.

##HTTP_PROTOCOL####SERVER##:##HTTP_PORT##/HTTP_QUALITY_REF##/ASP/SYSTEM/EmailAction_DocumentEntry.asp?DocID=##ID##&Action=APPROVE

To make the above link recognize that the intended recipient is logged in already, you would change it to:

##HTTP_PROTOCOL####SERVER##:##HTTP_PORT##/HTTP_QUALITY_REF##/ASP/SYSTEM/EmailAction_DocumentEntry.asp?DocID=##ID##&Action=APPROVE&PersonnelID=##RECIPIENT_ID##

Make sure you do not add "PersonnelID=##RECIPIENT_ID##" to a link that already has this.NOTE: If you copy and paste the code from the examples above, please be sure to remove any spaces.

QualTrax Inc.

2006-05-05T15:41:00.000-04:00

I work at QualTrax Inc, a wholely owned subsidiary of CCS-Inc (Comprehensive Computer Solutions.) I am a support technician for our flagship product QualTrax. QualTrax is a document and process management software. This product allows you to keep all of your corporate documentation in a secure central location. This software runs as a website, which ensures that your staff will always have access to the current version of any documents that they might need, such as forms and SOP's, as long as they have access to any computer in your organization. There is no special software to install on each client machine!

From time to time post tips about the use of QualTrax and experiences I have had while supporting QualTrax.

Pandora

2006-05-04T16:28:00.000-04:00

Today I discovered www.pandora.com. It's a neat website that plays internet radio. The difference between Pandora.com and other internet radio sites is that it attempts to play music that you will like. It will ask you for a song/artist that you like, then based on the rhythm, beat, vocals and other aspects it starts playing music that it thinks you will like. You can then help it learn your taste by telling it whether you liked the song or not. I've been listening to it all afternoon and so far I love it!