I talked to my friend Scott for the first time in a long time yesterday.  He and I were like brothers growing up and share like and dislike, almost without exception.  The one major difference between us would probably be our chosen paths.  While he went the photography route (the roundabout way through several stops) I went the web programming route (also the roundabout way).

The most common questions I get asked by people who are really interested in what I do is ‘what are you using to build your websites with’ and ‘what javascript framework  do you use?’  It was in explaining my choices to Scott that I felt like it would be worth explaining them to the world (potential clients may be curious).

The lesser of the two questions is what I’m using to build websites.  The desktop application I’m using is Adobe Dreamweaver but I’m really only using that for the code collapsing, snippets and site organization.  My foundation of choice is WordPress.  I’ve found it to be almost infinitely customizable and flexible enough to cover just about any project.  I found Joomla too big and hit WordPress next.  I’d love to give Drupal a try but I have a strong belief that being really strong with one tool makes you more marketable than being slightly experienced with several.  I have not met a site that WordPress didn’t like.  (But just because WordPress is in my ‘lesser’ category, don’t be fooled into thinking it’s not important.  WordPress is amazing.  It’s only lesser because it’s so easy to use and largely unnoticed by site visitors.)

The bigger impact tool I’m using right now is a javascript framework called MooTools.  Scott had never heard of MooTools and I guess this is where the line between web surfer extraordinaire and web programmer is drawn.  A surfer may know which blogging tool or BB platform he’s looking at but he’s unlikely to know which javascript framework is in use.

MooTools isn’t for javascript n00bs.  You have to be fairly comfortable with coding in general but also with CSS and the DOM.  Once you’re pretty good with those, stepping into MooTools is heaven.  Once I started working with MooTools I realized that there really wasn’t a single thing I couldn’t do with a web page.  It’s that powerful.

Once you’ve decided that MooTools is interesting and deserves looking into further, have a look at Aaron Newton’s Clientside.  All of the things you want to do on a daily basis with MooTools are covered.  Man, it’s almost all done for you!  Aaron’s got a book coming out in August and I can’t wait to get my hands on it.

I used to rely pretty heavily on the official MooTools forum which was shut down when version 1.2 of the framework was released.  I don’t lean quite as heavily on the community now but I invite everyone struggling with a MooTools script to try the unofficial forum at http://mooforum.net.  I cruise it quite often and try to help where I can.  My handle there is Lweel8 (it’s a bumper stumper for my punctuality when it comes to sporting events).

Best of luck to everyone out there.  I hope to see you in the forum.

Yep, it’s true.  It appears that one hand of Microsoft doesn’t know what the other is doing.  I haven’t found a lot of hits on this but apparently both sides of this bug confirm its existence and both claim it’s not their problem.

Symptom:

• Microsoft Excel cannot find the references inside a pivot table that references the same workbook when opened from Internet Explorer

Cause:

• Internet Explorer adds ‘[X]‘ to the end of the file name when storing the file in the IE temp folder, where X represents an incrementing integer based on the number of downloads of the same file, and the square brackets are illegal characters for file names in Windows

Steps to reproduce:

• Browsing with Internet Explorer
• Clicking on a link to an Excel spreadsheet with a pivot table in it that references itself
• Clicking on "Open" rather than "Save"

The only solution I could think of was to write a VBA macro to rename the file and reattach all of the pivot tables.  I hope this saves someone else some trouble in the future.  When you think of me, think of me fondly…

( You’ll have to add a reference to Microsoft VBScript Regular Expressions 5.5 or better. )

 Sub Auto_Open()
    
    Dim sFilename As String   ' the corrected filename
    Dim oASheet As Worksheet  ' the worksheet object
    Dim oPT As PivotTable     ' the pivot table object
    Dim sTempSource As String ' the pivot table temporary source variable
    Dim sNewRange As String   ' the new pivot table source variable
    Dim reg As New RegExp     ' a regular expression for matching wildcards
    Dim i As Integer          ' looping integer
    Dim j As Integer          ' looping integer
    
    ' Set a regex to test whether this is a temp spreadsheet from IE.
    ' Only a temp spreadsheet from IE will have square brackets in the filename.
    reg.Pattern = "[[\]]"
    
    If reg.Test(ActiveWorkbook.FullName) Then
        ' the temp filename has square brackets in it
        
        ' just a little message telling the user what's going on
        MsgBox "Renaming temp file and reattaching pivot tables..."
        
        ' The .Name property already has the brackets stripped out of it so
        ' we can build the new path using it and the .Path property.
        sFilename = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
        ' Now we save the file so that the assignment of source data to the
        ' pivot tables won't fail.
        ActiveWorkbook.SaveAs Filename:=sFilename
            
        ' The range for the pivot table requires a square bracket
        ' just before the file name so we rebuild the path, less the drive letter.
        sNewRange = Replace(ActiveWorkbook.Path, "C:", "") & "\[" & ActiveWorkbook.Name & "]"
        
        ' now set the regex for the tail end of the spreadsheet, including the trailing "]"
        reg.IgnoreCase = True
        reg.Pattern = ".*\.xls\]"
        
        ' now loop through all of the sheets looking for pivot tables
        For j = 1 To ActiveWorkbook.Worksheets.Count
            Set oASheet = ActiveWorkbook.Worksheets(j)
        
            ' now loop through any pivot tables on the worksheet
            For i = 1 To oASheet.PivotTables.Count
                Set oPT = oASheet.PivotTables(i)
            
                sTempSource = oPT.SourceData
                
                ' go ahead and replace the invalid path with the new path
                sTempSource = "'" & reg.Replace(sTempSource, sNewRange)
            
                'oPT.PivotTableWizard SourceType:=xlDatabase, SourceData:=sTempSource
                
                ' assign the new path to the sourcedata property
                oPT.SourceData = sTempSource
                
                ' release the object
                Set oPT = Nothing
            Next i
            
            ' release the object
            Set oASheet = Nothing
        Next j
        
        ' Save the workbook, just to be thorough.
        ' Without saving a user would be able to close the workbook without saving
        ' and then open the workbook from the
        ' recent documents list and have broken pivot tables.
        ActiveWorkbook.Save
    Else
        ' Not a spreadsheet with square brackets in the filename
        'MsgBox "No renaming required"
    End If

End Sub

My wife is a fan of the Shopaholic series of books and one of her favourite moments is when the heroine decides she’s going to get control of her over-spending. She decides that she’s going to write all of her spending down in a notebook and analyze where everything goes. In order to do that, she goes out and buys a fancy new notebook and a beautiful, and very expensive, pen – thus going further into debt. We had to giggle when we talked about building my office because we were spending what seemed like a ton of money in order to make the money back – to pay for the office!

Well, after only a few days nights in the new space I can already see that it’s going to be great.  The lighting, the tunes, the white board, the dual monitor, the filing cabinets.  I finally have a place to work – and my wife has her dining room table back.

I read it somewhere relating to studying but you really do have to have a space set aside specifically for working.  Doing any kind of job in an inappropriate environment just leads to sub-par results…

Here’s to awesome results, CHEERS!

I’m pretty handy with a chunk of code. Within a couple of seconds I can parse just about any regular expression with a reasonable amount of success. What I can’t seem to do is write the darn things when switching between JavaScript, PHP, VB.NET and VBscript. That’s where RegexBuddy comes in.

RegexBuddy is a little, but powerful, program written by a company called JGSoft (Just Great Software – catchy, no?). I was already a supporter of EditPad Pro, which is a replacement for Notepad that allows regular expression find and replaces, tabbing, line numbering and tons of other features, but I’m downright evangelical about RegexBuddy. You can build your expressions by hand or use the helpful “Insert Token” button, you can have your expression explained back to you in plain English, you can test your expression against a string you insert, but best of all, you can have the program build the entire script you need – in the coding language of your heart’s desire!

Being that I’ve just jumped into SQL Server 2005 with both feet, my knowledge of VB.NET is not nearly as complete as my knowledge of PHP and VBscript. Even once I had my expression, and was confident that it should return the proper results, I didn’t know that I had to import a library to use it. RegexBuddy knew.

Long story short, there’s a text file I’ve been procrastinating about parsing and cleaning up for almost 3 years. Using RegexBuddy I built, tested and implemented a housekeeping function in just over an hour. A 500KB files was reduced to under 30KB and made importing into SQL a breeze. Yay RegexBuddy! Now I just need to find my credit card to pay for the program…

If you’re curious about the expression that made my life so easy, here it is: “^.* (\d?\d/\d?\d/\d{4}) to \1.* (\d{4}[BMR]|\d{7}).* \d?\d:\d\d:\d\d.* \d?\d:\d\d:\d\d.*(\d?\d:\d\d:\d\d).* \d?\d:\d\d:\d\d.* (\d?\d:\d\d:\d\d).*$” which I then used in a replace with “$1,$2,$3,$4″.

Just wanted to rant a little.  I’ve been causing myself fits trying to position elements inside of table cells.  IE handles it just fine but Firefox barfs immediately.

I know what you’re saying, “don’t use tables for positioning, fool.”  Thanks for the advice,  and watch your mouth – this is a family blog.  As anyone who has done a lot of web work knows, tables are much easier to use and normally are the most faithful to the layout you are intending to produce.  For the code snobs out there, divs MAY work just as well but you have to admit that they are not nearly as flexible – right now.  When browser support, nay, when cross-browser support of div positioning is more consistent then I’ll officially make the switch.  Attributes like min-width and auto margins are, on paper, the answer to a lot of layout issues.  The trouble is that they are simply W3 guidelines for future browsers.  By the time Joe Q Public gets around to having a standards-compliant browser installed on their home machine, well, I’ll probably be dealing with cell phone browser layout issues.

Now I hear you saying, “but you can always use the [insert your favourite] hack to make it work, dummy.”  If I have to use a hack then I’m not likely to go that route.  Hacks inevitably lead to support issues and eventual outdating.  Today’s hacks are tomorrow’s bugs.  [and I'll thank you to cease with the name calling]

No, for now I’ll just motor along with tables, where I need them, and I’ll use divs where columns aren’t important.

You know, none of this would ever have become an issue if some of you out there didn’t have a real hate on for Microsoft.  I’ve got nothing against Billy G and the Midnight Racers but having competing browsers out there is making my job more desired.  Thank you, haters.

Stay tuned for my next post entitled, “Cross-browser headaches with lists.”  My one tip for fledgling pros out there is test in IE and Firefox often.  The only consistent thing between them are the inconsistencies.