Enjoy Life Cereals - Episode 30

Click to Play

3 different cereals reviewed from Enjoy Life: cranapple crunch, very berry crunch, and cinnamon crunch.

Formats available: MPEG-4 Video (.m4v)

Sighting: blue sky complete with silver lining!

See and download the full gallery on posterous

Mike

Posted via email from michaelpaul's posterous

Ginger Elizabeth Chocolates - Episode 29

Click to Play

If you're anywhere near Sacramento, CA, you'll want to check out Ginger Elizabeth Chocolates. We treated ourselves to a small cup of hot sipping chocolate before headed home to review six different kinds of their gourmet chocolate confections. You MUST watch this, and you MUST have some! They're that good. And no, they didn't pay us to say that, and yes, we bought the chocolate we reviewed. Looking forward to our next visit.

Formats available: MPEG-4 Video (.m4v)

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

Farmer's Kitchen Cafe - Episode 27

Click to Play

While passing through Davis, we stopped at Farmer's Kitchen Cafe for some lunch. We were blown away by this unassuming eatery, and found another hole in the wall that's worth the money. What did we order? Watch and find out!

Formats available: MPEG-4 Video (.m4v)

Erewhon Cereals

Click to Play

We woke up with a hunger. So, what better way to start the morning off than reviewing three different cereals from Erewhon? On the menu: Corn Flakes, Cocoa Crispy Brown Rice, and Strawberry Crisp.

Formats available: MPEG-4 Video (.m4v)

Two secret tickets to the planetarium. Can't say how we got these... Shhhh ;)

Mike

Posted via email from michaelpaul's posterous

Inside the Orpheum theater on the set of Wicked

Mike

Posted via email from michaelpaul's posterous

Crave Bakery: Chocolate Cake - Episode 25

Click to Play

We reviewed their apple and apricot tarts in Episode 21. Now we've got our hands on one of their chocolate cakes... although a little dry after sitting in our freezer/fridge for two weeks, none of the chocolate flavor got lost--that's for sure! We recommend dropping what you're doing and eating this cake the moment you buy it, because if it's this good after 2 weeks, it must be a smash hit when it's freshly baked.

Formats available: MPEG-4 Video (.m4v)

Beautiful sunset with a tree... Or is it?

See and download the full gallery on posterous

Okay, okay, the sunset was a picture I found on the Internet (c) Michael Wang 2005, but I did make the tree myself. It consists of one 3-foot CAT-5 Ethernet cable that I cut into small ~5" pieces before slicing open the tops, exposing the twisted pairs of copper wire, which I then craftily fashioned into the life-like tree you see before you.

Hope you enjoyed the art. Re-tweet if you liked it.

Mike

Posted via email from michaelpaul's posterous

The best kept secret of Starbucks: order a "short" instead of "tall" or "grande" and save some dough.

Mike

Posted via email from michaelpaul's posterous

Three Senses Gourmet: Chocolate & Caramel Souffles - Episode 24

Click to Play

If you're looking for a special chocolate treat you can enjoy once in a while that will take you to a whole new level of chocolatey goodness, you had better try these souffles from Three Senses Gourmet. We're serious.

Formats available: MPEG-4 Video (.m4v)

More fall colors on display at work

This one is actually at my office building. Again, taken with iPhone (1st gen FTW), processed with AutoStitch.

Mike

Posted via email from michaelpaul's posterous

Beautiful fall colors on display

Panoramic photo of some trees by my office building. Taken with iPhone and processed with AutoStitch.

Mike

Posted via email from michaelpaul's posterous

Conte's Raviolis - Episode 23

Click to Play

Finally: some gluten-free raviolis! We sample three different varities: spinach & cheese; potato, onion & cheese; and ricotta cheese. It's not easy being cheesy. ;-)

Formats available: MPEG-4 Video (.m4v)

Coconut Bliss Ice Cream Party

Click to Play

Debbie won a contest for an ice cream party from Coconut Bliss, so we decided to use the interwebs and invite some family to the party live! 5 different ice creams reviewed--and they're all freakin' amazing. Join the party and see which was our favorite, won't you?

Formats available: MPEG-4 Video (.m4v)

Crave Bakery - Episode 21

Click to Play

Owned and operated by Cameo Edwards, Crave Bakery offers gourmet gluten-free desserts. Today we tasted an apple and an apricot tart... But will they leave us craving more? Find out!

Formats available: MPEG-4 Video (.m4v)

TweetDeck not working behind your corporate firewall? Try this.

Sometimes you've got a pesky firewall (AKA "proxy server") in your way that prevents you from accessing certain websites. TweetDeck, a popular twitter client running on the Adobe Air platform, seems to have some issues connecting to twitter behind my corporate firewall. I came to find out recently that it is not TweetDeck that is to blame, so much as it is Adobe.

Anyway, here's the fix. Once TweetDeck launches, click on the question mark button at the top right (the tooltip will say "Launch TweetDeck Support"). What should happen is a little window pops up asking you for your proxy server username and password, and then the TweetDeck support website will open. Once this happens, simply click the Refresh button in TweetDeck and your tweets should appear.

Did this work for you, too? Post a comment and let the world know.

Posted via email from michaelpaul's posterous

Another beautiful sunset at the apartment

The bright white in the pic is actually a deep orange that's much better appreciated in person.

Mike

Posted via email from michaelpaul's posterous

iPhone App: Is That Gluten Free? - Episode 20

Click to Play

Our first iPhone app review is here: we've found one of the most popular gluten-free apps for the iPhone and put it through its paces, leaving no stone unturned. What we found is that while the app is generally great for determining whether or not something is gluten free, too much time has elapsed between the verification date and the current date, leaving the possibility that the recipe/formula has been altered--which still requires a phone call to the manufacturer to clear up any questions.

Formats available: MPEG-4 Video (.m4v)