Formulas Winner

As I type, a copy of Excel 2007 Formulas is making its way across the country from me to John Spanos, the lucky winner. Here are the stats:

Entrants: 186
Qualified Entrants: 123
Entries with something in the body: 44
Duplicate entries: 20
Late entries: 3
Wrong subject: 0

I put the data into swivel.com to make some nice charts. However, Swivel doesn’t let me query or chart the data, it does it for me. I guess I need to put the data in just the right format/structure to get what I want out of Swivel. I was hoping to do some simple queries on the data and display some charts based on those queries. When I first heard about Swivel, I thought it would be similar to YouTube, just more interesting (to me). I want to take data that other people upload, and query it for my own purposes. As far as I can see, there’s no way to do that.

I put the code I used to extract the email into Excel at the bottom of this post. Almost every time I ran this code, it left an OUTLOOK.EXE process running even though Outlook wasn’t running. I think it has something to do with the Outlook feature that allows access for a specified amount of time. I have not been able to get a straight answer on why the process doesn’t terminate properly. I’ve tried every manner of setting object variables to Nothing and quitting Outlook, but I couldn’t get it to consistently terminate the process.

excel range showing entries

After the code below was run, I had a nice list in Excel. I sorted the list by email address and reviewed it for duplicates. Then I sorted it my name and reviewed for duplicates. I’d like to think I was able to identify all the duplicates, but I’m sure one or two slipped by. Next, I put a random number next to each qualified entry and a zero next to each unqualified entry using this formula:

=IF(AND(D2< =DATE(2007,2,23)+TIME(14,0,0),E2=0,F2=”Hook Me Up”,NOT(I2)),RAND(),0)

After filling this formula down, I copied it and pasted special > values so the random numbers wouldn’t keep changing. In the next column, I put a rank formula

=RANK(J2,$J$2:$J$187)

John ranked #1 because he had the largest random number.

Warning: Running this code may leave orphaned processes on your computer.

Sub HookEmUp()
   
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olMi As Outlook.MailItem
    Dim rFound As Range
    Dim dTime As Date
   
    wshEntrants.Cells.ClearContents
   
    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace(“MAPI”)
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox).Folders(“BlogOther”).Folders(“FormulasBook”)
   
    With wshEntrants.Range(“A1”)
        .Value = “EntryID”
        .Offset(0, 1).Value = “EmailAddress”
        .Offset(0, 2).Value = “Name”
        .Offset(0, 3).Value = “ReceivedTime”
        .Offset(0, 4).Value = “BodyLength”
        .Offset(0, 5).Value = “Subject”
    End With
   
    For Each olMi In olFldr.Items
        With wshEntrants.Range(“A65000”).End(xlUp).Offset(1, 0)
            .Value = olMi.EntryID
            .Offset(0, 1).Value = olMi.SenderEmailAddress
            .Offset(0, 2).Value = olMi.SenderName
            .Offset(0, 3).Value = olMi.ReceivedTime
            .Offset(0, 4).Value = Len(StripChars(olMi.Body))
            .Offset(0, 5).Value = olMi.Subject
        End With
    Next olMi
   
    ‘olApp.Quit
   
    Set olMi = Nothing
    Set olFldr = Nothing
    Set olNs = Nothing
   
    Set olApp = Nothing
     
End Sub
 
Function StripChars(sBody As String) As String
   
    Dim sTemp As String
   
    sTemp = sBody
    sTemp = Replace(sTemp, Chr$(9), “”)
    sTemp = Replace(sTemp, Chr$(10), “”)
    sTemp = Replace(sTemp, Chr$(13), “”)
    sTemp = Replace(sTemp, Chr$(160), “”)
   
    StripChars = sTemp
   
End Function
Posted in Uncategorized

10 thoughts on “Formulas Winner

  1. Hi Dick,

    Thanks for mentioning Swivel. One of the features we will be rolling out in the future is a query language that allows folks to type-in requests across all the data at Swivel. That will be probably 6 months out, but we’re prototyping it in the lab now. Great blog, by the way, I’m glad I found it.

    Regards,
    Brian

    Brian Mulloy
    CEO & Cofounder
    http://www.swivel.com

  2. Dick, I am working right now on designing a flow that would let you create a chart your own way. Would you be willing to exchange emails or chat with me on the phone or IM so I can understand what you’d like to be able to do? I’m glad you thought of Swivel for your needs and I’m of course disappointed it didn’t do what you wanted, but I’m pretty confident it can get close with a little bit of your help.

    Thank you and hoping to connect with you,
    Dmitry Dimov
    Product Chief & Cofounder
    http://swivel.com

  3. Yeah me! Now I get to brag about being such a big nerd that I won a free Excel Formulas book!

    I somehow doubt that anyone will be surprised!

    Thanks Dick, I look forward to receiving the book. :)

  4. Dick
    COM servers often hang around for a couple of minutes after the last reference is released. Its part of the optimisation to save the reload if you need it again soon after. If you click on an embeded workbook in a Word doc you get the same effect. After a couple of mins Excel.exe closes if you don’t use it. If your Outlook.exe closes after a couple of mins this might expain it, or not.
    cheers
    Simon

  5. The reason Outlook does not terminate is not because of a delay after the last reference being released. I believe it’s more subtle than that.

    It’s to do with implicit references. In other words, any object you create or reference should be done so with/by an object variable. For instance, in the following (and the With)

    Set olFldr = olNs.GetDefaultFolder(olFolderInbox).Folders(“BlogOther”).Folders(“FormulasBook”)

    there are implicit refs created and held which are not released.

    You need to ensure no implicit references are created anywhere, by assigning each ref (.) to an object variable.

    I tried to find the KB support article that descibes this in detail, but could not find it.

    BTW This is the same problem experienced by people automating Excel from Word, Outlook, VB, etc..

  6. Perfect way of getting and processing the data. Is it possible to collect and process more complex data received by mail? In the contest there were the same data in body of all e-mails, according to the scheme. I often need to get it from many people. I use some Web based software but even for a slight modification I have to ask PHP and SQL men for help. I tried Webform (it was only in Excel 97) but with no success. It crashes in newer vesions, anyway. Some flexible to changes would be nice. Thanks for the blog. I used to be a daily reader.

Leave a Reply

Your email address will not be published. Required fields are marked *