Pomodoro Timer
Written by: James Climer (http://www.climers.com)

• Pomodoro.exe (Recommended)
• Source code
• Promodoro Worksheet (Excel .xls spreadsheet that I use as a template)

I've decided to start using the Pomodoro technique in hopes of making better use of my time.  After trying it out for a couple of days I've found it actually helps quite a bit in getting stuff done.  I decided to make a little timer for windows because I couldn't find a simple one to download (at least not from a site that I trust).

The Pomodoro Technique™ was developed by Francesco Cirillo and can be found online at http://www.pomodorotechnique.com/.  The Pomodoro Timer is a Microsoft Windows Application written in .NET (3.0 Framework) to be used with the Pomodoro technique as a tool to help track time.  It is a very simple application but serves its purpose well.  The time and audio can be configured via the Promodoro.exe.config file included with the application.  The config file also includes instructions in the comments to help make these modifications. 

I hope you enjoy this tool and that it helps you make better use of your time.

-James

This problem would have been a really good homework assignment for beginning programming.  It requires a little explination, though I will try to brief:

I received an Excel spreadsheet containing various forms of revenue for a company.  I was also given a summary report from a different system which was supposed to represent the same data.  However, the categorizations of the data on the report were different from those on the spreadsheets and the totals weren't adding up.  So I had to take the totals from the report and figure out which cells on the revenue spreadsheet were used to make that total.

To do this, I had to come up with every combination of the cells, get a sum of each combination, and see which ones matched the value I was looking for.

The VBA code below will allow you to select a column of cells on a spreadsheet, right click to activate the program, enter a value to be searched for, and it will return all combinations of the cells that can be summed to make that value.

What was most difficult about this, was coming up with an algorithm for giving me all possible combinations of the cells.  Plus, I wanted to search for the smallest number of returning cells first before searching for combinations containing all cells.  The GetComb function in the code at the bottom of this article works in the following manor:

Given the Cells: A, B, C, D, E, I started with looking for the all single items (in the first column) which required just 1 pointer value. I moved that pointer from left to right (follow it going down the column).  Next, I found all combinations of 2 items (in the second column) which required 2 pointers.  I moved the last pointer (labeled 2) step-by-step to the right.  When the last pointer was all the way to the right, I stepped back to the first pointer and stepped it one to the right, and moved the last pointer to the next consecutive place (row:5 column:2).  I then moved the last pointer again, step-by-step to the right.

This is the algorithm, repeated until all combinations are found.

Here is the VBA code I wrote to do this, Enjoy!

More...

I had a situation at work where a coworker needed to export a spreadsheet to a fixed width pnr file.  The resulting file was truncated at 240 characters, which I believe is part of the pnr standard (I haven't actually looked that up or anything).  So I wrote a script to export a spreadsheet to a fixed width .pnr file regardless of the number of columns.

To make it easier to code, I prompt for the number of columns and rows and it uses a common dialog control to get the destination file path.  If you don't have the common dialog control you'll get an ActiveX error.  The whole section prompting for file location can be replaced with an InputBox function, but that is neither here nor there.

Also, for your Excelling pleasure, I added a PadRight function since I couldn't seem to find one in Excel 2003. 

Just add the following script to your worksheet (Alt+F11 and double-click Sheet1 usually, paste and click the "play" button)

Enjoy: More...

Today I encountered several data files that were created from a mainframe.  The files were in ASCII format, but contained several non-printable ASCII characters (those with values from 0 to 31).  So I wrote the following uitility to replace those invalid characters with spaces.  It seemed like a really good utility, so I thought I would share.

Download  (10Kb - Requires the .NET 3.5 Framework)

Source Code:

More...

The spam in the comments on my blog has really gotten bad lately.  It seems like almost 10 to 20 comments a day are added to my site that I have to go search around for and delete.  I should receive an e-mail every time a comment is added, but somehow, maybe my spam filter is catching them, I don't receive an e-mail.  Which means I have to go through all of my blog entries and search them out and delete them.

My original plan was to use recaptcha, but I'd like to get the spam under control soon, and it's hard to find time to work on things like this.  So anyway, here is my quick fix:

First, I'm assuming the spam is coming from bots, scripts that are specially programmed for BlogEngine.NET.  So if I add any kind of required field that the bot is not expecting, I would think it would mess up the bot.

Now adding such a simple thing would be easy for the bot scripter to get around.  They would simply modify their script to put a value in the new field I created; so what I'm hoping is that they won't waste their time modifying their script just for my site.

I added a checkbox that must be checked to submit a comment.  I'll give this a week to see if it helps, and if so, I'll put a link in the BlogEngine.NET forums.  I would suggest everyone do something a little different so that the bot scripter will have to work hard to keep up.

Update 10/07/2009

"This has worked tremendously well on cutting down on spam, but I have to say, some spam comments are still getting through and they seem to be getting sneakier.  I suspect that the spammers are keeping a list of blogs that they have trouble automatically spamming and then send real people to read a little of the blog entries and manually submit comments.  I say this because, not only am I still getting spam comments from time-to-time, but the comments are actually related to details, usually in the first couple of sentences, of the blog entry.  Pretty sneaky.  Naturally, neither this solution nor the recaptcha will work against actual humans posting comments.  Perhaps the next thing I will work on is a common source for comment spammer data including the spammer's e-mail and the url they include in the comment."

Here is the technical part.

I should note, I'm using BlogEngine.NET version 1.4.5

More...

I needed to find some data quickly in a database that had around 300 tables in it.  I knew the tables I was looking for would have a lot of rows in them so I wrote the following script to return a list of all of the tables with their row counts.  I found it useful, so I thought I would share.

-- Create variables to hold table info
DECLARE @tableName varchar(255),
        @rowCount int;

-- Create a temp table to hold the results
CREATE TABLE #TableCounts
    (TableName VARCHAR(255),
    TableRowCount int)

-- Step through each table using a cursor
DECLARE tableName_Cursor CURSOR FOR
    SELECT table_name FROM information_schema.tables ORDER BY table_name;

OPEN tableName_Cursor;

FETCH NEXT FROM tableName_Cursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

   -- Create a dynamic SQL statement to insert a
   -- record into the temp table
   EXEC ('INSERT INTO #TableCounts (TableName, TableRowCount) (SELECT ''' + @tableName + ''' TableName, Count(*) TableRowCount FROM ' +  @tableName + ')');
   FETCH NEXT FROM tableName_Cursor INTO @tableName
END

CLOSE TableName_Cursor;
DEALLOCATE TableName_Cursor;

-- Display the contents of the temp table
SELECT * FROM #TableCounts ORDER BY TableRowCount DESC;
DROP TABLE #TableCounts

I ran into an issue trying to restore an MS SQL Server 2005 database on a different server from the one that was originally backed-up.  Using the Microsoft SQL Server Manager (MSSM), I just kept getting an error when I would try.  So I used the following script:

--Note, replace MyDatabase with the database name.  If you don't know it, try restoring using the MSSM, it will tell you the databases in the back-up, it just won't let you restore it

USE master

GO

RESTORE DATABASE MyDatabase
    FROM DISK = 'c:\Temp\MyDatabase_Backup.bak' WITH
    MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase.mdf',
    MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase_log.ldf',
    REPLACE

GO

I was sent a webpage from a coworker who was stumped as to why the page displayed correctly in IE 7+ and FireFox but not in IE6.  After looking around I noticed the problem was around a FORM tag.  I found the following solution on Snipplr.com.

The FORM tag seems to have a bug in IE6 where it doesn't automatically resize to fit the parent container.  So I added a style tag to force the display to be inline:

<FORM style="display:inline;">

Hope this helps.

I just had a request to do this and it required me to look up a couple of things, so I thought I would share.

There are two ways to conditionally change the background color in an Excel spreadsheet (possibly more but I can only think of two).  One is to use a VBA macro, which I like but it makes it difficult to share with non-techie folks.  The other is using built-in Excel functions which makes everybody happy, so I went that route. 

The following is a way to change the cell background color based on the value of a different cell:

I was asked to evaluate a cell based on its value being 0 to 2.5 is "Low Risk", 2.5 to 3.5 is "Medium Risk", and 3.5 to 5 is "High Risk".  The Low Risk cell should have a yellow background, the Medium Risk should be green, and the High Risk should be red.

I accomplished this in two parts.  First, figure out the string to display based on the value (which was stored in the A1):

=IF(A1 <= 2.5, "Low Risk", IF(A1 > 3.5, "High Risk", "Medium Risk"))

Next, the background color.  Excel has a built-in conditional formatting for changing this, the only problem is it is limited to three conditions.  It just so happens three is all I need.  If I had more than three, I would have had to go the VBA macro route. 

Using the Format > Conditional Formatting wizard I setup three conditions where the value of the cell containing the formula was either "Low Risk", "Medium Risk" or "High Risk", each one having the respective background color.

Recently at work I was given an MS Access 2003 database and was asked if I could extract a list of table names and field names from it.  So I wrote the following script in a module and called it from the immediate window and was able to get the list.  It was a rather simple script, but I thought it might help somebody else. 

This also demonstrates how to print to a file from MS Access.

So here it is:

Public Sub GetTablesAndFields(Optional OutputFile As String)
    Dim TableIndex As Integer
    Dim FieldIndex As Integer
    Dim TableName As String
    Dim FieldName As String
    Dim bOutputToFile As Boolean
    Dim fnum As Integer
       
    If Not IsEmpty(OutputFile) And OutputFile <> "" Then bOutputToFile = True
   
    If bOutputToFile Then
        'Setup the output file
        fnum = FreeFile
       
        Open OutputFile For Output As #fnum
    End If
   
    For TableIndex = 0 To (CurrentDb.TableDefs.Count - 1)
        'Get the table name
        TableName = CurrentDb.TableDefs(TableIndex).Name & vbCrLf & "======================="
       
        If bOutputToFile Then
            Print #fnum, TableName
        Else
            Debug.Print TableName
        End If
       
        'Display the fieldnames
        For FieldIndex = 0 To (CurrentDb.TableDefs(TableIndex).Fields.Count - 1)
            FieldName = CurrentDb.TableDefs(TableIndex).Fields(FieldIndex).Name
           
            If bOutputToFile Then
                Print #fnum, FieldName
            Else
                Debug.Print FieldName
            End If
        Next
       
        'Place space between tables
        If bOutputToFile Then
            Print #fnum, vbCrLf
        Else
            Debug.Print vbCrLf
        End If
    Next
   
    MsgBox "Done"
   
CleanUp:
   
    If bOutputToFile Then
        Close #fnum
    End If
End Sub