Creating Screen Capture Animated GIFs for Mac OSX

I regularly create short, animated GIFs of my computer screen to illustrate tutorial blog posts that I write. An animated GIF can be such a simple yet effective tool for web based tutorials but they can be a complete pain to create.

Creating Screen Capture Animated GIFs

I’ve recently come across a new and FREE piece of software that has made the process so incredibly easy. Whereas I have historically had to record a video of my screen actions (with a video screen capture tool) and then use another piece of software to convert the video to GIF, usually with an horrific loss in quality, I can now complete the action in one go.

LICEcap is so easy to use and creates impressive quality output, quickly. You simply open the application, which reveals a transparent window frame that you can move and resize. You then press the record button on the bottom right of the window frame and perform your action. Press stop when you’ve finished and your GIF is ready to go.

NB – My mac is set to open GIFs in preview by default but in order to view the animation in its full glory you will need to open in a web browser such as Safari.

Here is an example of a short GIF I created using LICEcap to demonstrate how to create a pivot table using Microsoft Excel for Mac OSX.

Animated GIF from screen capture

I am very happy with the quality of this GIF and the ease of production was impressive.

Image Compression

As I’ve created quite a large GIF I have chosen to compress it using an equally simple program called ImageOptim. Download and run this program and then drag and drop your GIF (or indeed any image file) to immediately compress without any apparent loss in quality. I achieved a 10% reduction in size with Pivot Table tutorial but it ImageOptim claim that reductions up to 50% are achievable.

Here it is in action.

image_optin

I also run Windows 10 on my Mac so that I can run a decent version of excel. Happily LICEcap also has an excellent windows version.

ImageOptim doesn’t so you might find this in depth review of cross platform image compressors to be useful.

Controlling the Raspberry Pi from an iPad

After the frustrations of the first few days I am now coming on in leaps and bounds with the Raspberry Pi. Today I managed to set up my iPad so it can take control of the RPi. This means that the Raspberry Pi can be tucked away in corner, running headless while the iPad seizes control so I can make use of its keypad and monitor.

This means that I can now log on to RPi from anywhere that I have internet access – my main desire for doing this is so that I can run python and start learning to program from the mobile convenience of my iPad.

Before you can connect remotely to your Raspberry Pi you need to have connected it to the internet. You can do this by plugging it into an ethernet cable or as I have done by using the Edimax Wireless Nano USB Adapter, which is a fantastic piece of kit, its tiny and was a doddle to install.

[GARD]

Apps Required to Connect to your Raspberry Pi from an iPad

Apps to connect iPad to Raspberry PiThese are the apps that I’ve used to connect and all have proved effortless to setup and have so far served me well. Note the VNC Viewer is quite expensive so you might want to play around with some free versions before deciding to part with this much cash.

Fing (free) (Fing – Network Scanner)
WebSSH (free) (WebSSH)
VNC Viewer (£6.99) (VNC Viewer)

Using SSH to connect to your Raspberry Pi

Enable SSH
SSH (Secure SHell) provides access to the Pi’s command line interface. Before you can use it you need to enable SSH from the RPi config.txt file.

Open up the terminal and type

sudo raspi-config

From here you should select Advanced Options and then enable SSH. Now save and exit.

In order to connect to your Raspberry Pi you need to know the IP address that it is using. You can find this from the command (see instructions here)

ifconfig

but I have found it convenient to install the free iPhone or iPad app Fing which enables me to scan the home network to see all connections (will prove useful later).

Connect via SSH
Setting up SSH Connection with Raspberry PiDownload WebSSH (or similar) and add a new SSH connection.

The Host is the IP address used by your Raspberry Pi and identified using Fing. User and password are as set up in raspi-config. The default is pi raspberry

iPhone connected to Raspberry Pi via SSHIf you hit connect now you will be rewarded with access to the command line.

I find this extremely satisfying. One thing to bear in mind is that your Raspberry Pi will be using a dynamic IP address so every time you reboot it is likely to select a different address, which means you would need to amend your SSH connection details in the app.

You can get around that by forcing your RPi to use a static IP address.

How to Use a Static IP Address with your Raspberry Pi

[GARD]

There are detailed instructions on both Raspberry Shake and tuts+ but this is what I did:

From the terminal, enter the following code to open the nano text editor in order to change the connection details

$sudo nano /etc/network/interfaces

You will now be in the editor and can overwrite and add additional instructions.

Look for the line that reads

iface eth0 inet dhcp

and change the dhcp to static

iface eth0 inet static

Beneath this add the following lines of instructions:

address 192.168.0.11 <—-this should be your IP address as identified by Fing
netmask 255.255.255.0
network 192.168.0.1 <—you can also identify this in Fing, for me it was the address of my router
broadcast 192.168.100.255
gateway 192.168.100.254

In order to save this file you need to press CTRL O and then enter. CTRL X will close the nano editor. It is then worth opening a web browser to check that you are still connected to the internet and you haven’t messed everything up but entering the wrong addresses.

If you have messed something up, just retrace your steps and alter the details in the nano editor.

When you reboot, your IP address will be fixed and you won’t have to amend the SSH connection details.

Using VNC to Connect your Raspberry Pi from an iPad

The SSH protocol has just enabled us to connect to the command line of your Raspberry Pi but if you want to replicate the graphical desktop you will need to use VNC (Virtual Network Computing).

Install the VNC Server on the Raspberry Pi

To use this we need to install a VNCserver on the RPi. Follow these instructions from the terminal:

sudo apt-get install tightvncserver

follow the instructions and enter a username and password as instructed and then run the server by entering

tightvncserver

the VNC server won’t automatically run after you reboot (unlike SSH which will always be enabled). This could be a nuisance if you reboot regularly and if so you might want to follow the instructions below that force the VNC server to run at start up.

Connecting via VNC

Raspberry Pi running on an iPadDownload a VNC Viewer to your iPad, I’ve used VNC Viewer but there are cheaper and free versions available.

Add a new connection. The address will be the static IP address that you set above but should also include the port number. So mine reads 192.168.0.11:1 (I believe 1 is the default port so should work for you as well.

The password will be whatever you set up when installing tightVNCserver.

Hit connect and you should be rewarded with a beautiful raspberry on your iPad screen.

Running VNC when your Raspberry Pi Starts Up

The details for this task are can be found at adfruit who have provided a very clear tutorial for running VNC at StartUp.

Resources

I’ve used some really useful tutorials to help me complete the task – here are the ones I found the most useful:

Update

I have just added a second tutorial that explains how to go further with the setup and allows you to access the Raspberry Pi from an external network for true remote control of a Raspberry Pi.

Waterfall Charts and the X-Axis

Waterfall charts have become all the rage in the office of late. They are popping up all over. I suppose it is no surprise, it’s springtime and we are well into the planning process and a waterfall chart is the perfect way to demonstrate how a starting position either increases or decreases through a series of changes.

So in our case, when planning for next year’s acute healthcare budget, we may start with the forecast outturn for the current year, throw our hands up in despair and then work through a series of savings initiatives that would hopefully bring us back to a position of affordability.

This can be easily demonstrated by the use of a simple waterfall chart which is a version of a stacked bar chart with some elements formatted so that they are not visible.

Here you can see the driving table and the series clearly visible before formatting.

Life in the waterfall chart world becomes a little more difficult if your movements are going to take you either above or below the x-axis.

You can imagine this happening if instead of starting with forecast outturn we model the affect on the planned deficit/surplas. The chart below shows the starting position before QIPP or savings programs and the incremental affect of appyling each projected saving.

The table required to generate this chart is quite complex but it does at least illustrate the position accurately if the data crosses the x-axis. The simple model shown above would display the data incorrectly.

You can download the spreadsheet I used to draw up these examples which will show all the relevant formulas Waterfall Chart.xlsx but I would also recommend that you take a look at the excellent Peltier website which explains the creation of both simple and complex waterfall charts in more detail and describes the formatting process step by step – I used this site to learn the techniques.

Managing Version Control in Excel using Macros

If you work with spreadsheets that are regularly being updated and amended by multiple users then you will know something about version control. If you then add to this mix, the need to periodically distribute the updated spreadsheet for public viewing you will be more than well aware that Microsoft Excel isn’t really designed to handle version control very well.

I’m currently working with NHS contract proposals for the new financial year. The models are complex and the figures involved are high. We are up to version 20 already and the first cut hasn’t even been published. Between each issued version I know I will be inundated with emails asking what has changed and why.

I’m getting twitchy just thinking about it and I’m already dreaming about version control.

I’ve put together a few macros to enforce some rules for version control and to maintain an audit trail of changes and versions.

Methodology for Controlling Versions in Microsoft Excel
My spreadsheet includes tabs of data related to patient care, but it will work for any model that you wish to maintain a version trail for.

You need a tab for documenting the changes between each version. Mine is called Version Control but then I’m an accountant and don’t really do imagination.
On this sheet are two named ranges, VERSION and FILENAME (in B7 and B9 respectively) and two macro buttons for running the save options.

When the document is saved I want the user to overwrite the master document so that it always shows the latest version and I want them to save the file to a version folder and increment the filename to the next version number. So each save process creates one new file and overwrites another.

To ensure this happens just as I want, I need to prevent users from saving the spreadsheet in their own way. I do this by writing a procedure in the workbook object that will run whenever they attempt to save the document.

1
2
3
4
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "When you are happy with the changes, please go to the VERSION CONTROL tab and freeze the document to save"
End Sub

This directs the user back to the Version Control tab where they can either perform a FREEZE save or a temporary save. The temporary save is for convenience in case large changes are being made, it will save a single version with a temporary filename. The FREEZE save creates a new version and overwrites the master document, it also requires the user to enter some notes relating to the changes made.

Here’s the code for the FREEZE save:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Sub FreezeWorkbook()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim wsVC As Worksheet
Dim Destwb As Workbook
Dim VersionFilePath As String
Dim VersionFileName As String
Dim MasterFilePath As String
Dim MasterFileName As String
Dim VersionDate As String
Dim Version As String
Dim CurrentVersion As String
Dim iLastRowVC As Long 'LAST ROW IN VERSION CONTROL SHEET
Dim Author As String
Dim Changes As String
Dim AmendRef As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'UPDATE THE VERSION CONTROL DETAILS
ActiveSheet.Unprotect
Set wsVC = Worksheets("VERSION CONTROL")
iLastRowVC = wsVC.Cells(Rows.Count, "B").End(xlUp).Row

'Current Version
CurrentVersion = wsVC.Range("a" &amp; iLastRowVC).Value

'Insert version
Version = InputBox("Please enter the next incremental Version Number (Current Version = " &amp; CurrentVersion &amp; ")")
wsVC.Range("a" &amp; iLastRowVC + 1).Value = Version

'Insert version date
VersionDate = Format(Now, "dd-mm-yy hh-mm-ss")
wsVC.Range("B" &amp; iLastRowVC + 1).Value = VersionDate

Author = InputBox("Please enter your name")
wsVC.Range("c" &amp; iLastRowVC + 1) = Author

Changes = InputBox("Please enter a brief description of changes made")
wsVC.Range("d" &amp; iLastRowVC + 1) = Changes

AmendRef = InputBox("Enter the ref to the Amendments Document or N/A if none available")
wsVC.Range("e" &amp; iLastRowVC + 1) = AmendRef

wsVC.Range("F" &amp; iLastRowVC + 1).Value = Range("HLACTIVITY") 'Returns a the total activity value from a named range within the spreadsheet
wsVC.Range("G" &amp; iLastRowVC + 1).Value = Range("HLVALUE") 'Returns a the total financial value from a named range within the spreadsheet

'Save the master workbook
Application.DisplayAlerts = False
Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Overwrite the master report or create it if it doesn't yet exist
MasterFilePath = "c:\My Documents\2010-11 A&amp;F Contract Templates\Masters" 'Amend to suit your filing system
MasterFileName = Range("FILENAME") &amp; " Master"

Range("VERSION").FormulaR1C1 = Version 'Overwrite the version number on the version control sheet

With Sourcewb
.SaveAs MasterFilePath &amp; MasterFileName, FileFormat:=FileFormatNum, WriteResPassword:="" 'This ensures that the master is not password protected otherwise I get problems overwriting the file
End With
Application.DisplayAlerts = True

'save the version workbook
Set Sourcewb = ActiveWorkbook
'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

'Save the new workbook and close it
VersionFilePath = "c:\My Documents\A2010-11 A&amp;F Contract Templates\Versions\RAP"

VersionFileName = Range("FILENAME") &amp; " " &amp; Version
ActiveSheet.Protect

With Sourcewb
.SaveAs VersionFilePath &amp; VersionFileName &amp; FileExtStr, FileFormat:=FileFormatNum, WriteResPassword:="password" 'this sets password protection in place for the version spreadsheets
End With

MsgBox "New version saved as " &amp; VersionFilePath &amp; VersionFileName &amp; " and Master Updated"

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

and the Temporary Save code which saves the file in the versions folder with a “Temp” suffix:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub TempSave()
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim TempVersion As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Force the file extension to remain as Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

ActiveSheet.Unprotect
TempFilePath = "c:\My Documents\2010-11 A&amp;F Contract Templates\Versions"
TempVersion = Format(Now, "yy-mm-dd hh-mm-ss")
TempFileName = "Temp " &amp; Range("FILENAME") &amp; " " &amp; TempVersion
Range("VERSION").FormulaR1C1 = TempVersion
ActiveSheet.Protect

With Sourcewb
.SaveAs TempFilePath &amp; TempFileName &amp; FileExtStr, FileFormat:=FileFormatNum
End With

MsgBox "New version saved as " &amp; TempFilePath &amp; TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

I’ve used the plugin Syntax Higlighter to display the VBA code. It looks great but unfortunately it doesn’t copy and paste well. You can download a working spreadsheet that includes the modules above if you want to copy them – Excel Version Control Example V1 the password to open is NCL.

As usual I have botched this macro together from many hints and tips found elsewhere on the interweb, this page from Ron de Bruin was particularly useful.

I think I need to add some loops into the input box code because users can choose not to enter any comments or an appropriate version number at the moment and I have no means of controlling the errors.

I’ve also had to run this without any password protection on the master file. If I password protect it the macro fails with a write protection error. I’m not sure if I ought to be able to get around this or whether it is not actually possible to save over a write protected file. Dunno.

Excel Pivot Table or CrossTab to Flat List

Excel to CrossTabI often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across the columns. Sometimes they have achieved this by putting the data into a pivot table but then have pasted the table as values and removed all links to the underlying data. Very helpful.

[GARD align=”center”]

Yesterday I had to deal with a data file that looked a little like this only  it spread out across 455 columns and was frankly useless.

 

I wrote a post a while back that demonstrated how to convert a simple cross tab back to a manageable data list but I wanted to expand this with a slightly more complex example which had more field headings.

So starting with the table above which was probably a pasted copy from a pivot table I applied a little bit of formatting to achieve the following starting table.

What I have done is remove the grouping that the pivot table applies and ensured that I have the relevant  week number, project and department details against each name

I often wish to convert pivot table outputs back into a data file and the way I fill in the blanks caused by grouping data is to prepare a sheet with formulas that copy values from above (or the side as appropriate):

I then copy the pivot table data and then use the paste special command selecting paste values and skip blanks as the options.

This results in the following output, which I then copy and paste over itself as values, to give me the desired structure.

 

Having spent a bit of time on the structure of my starting file I can run the macro that will work through each row and transpose the data from a columnar layout to one based on rows.

The advantage of this format is that I can now create my own pivot tables and cut the data as I see fit to produce multiple summary formats according to my audience.

 

Here’s the macro I use:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim ROW As Long

Set wsCrossTab = Worksheets("Sheet1") 'AMEND TO SHOW SHEET NUMBER WITH THE CROSS TAB
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).ROW

Set the initial value for the row in the destination worksheet
iLastRowList = 2

Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A2").End(xlToRight).Column

'SET THE HEADING TITLES IN THE LIST SHEET
wsList.Range("A1:F1") = Array("NAME", "PROJECT", "TYPE", "PLAN/ACTUAL", "WEEK", "HOURS")

'Start looping through the cross tab data

For ROW = 3 To iLastRow 'START AT ROW 3 AS THIS IS WHERE DATA BEGINS
Set rngCTab = wsCrossTab.Range("A" &amp; ROW, "C" &amp; ROW)'initial value A3 SETS THE RANGE TO

'INCLUDE ALL STATIC DATA - IN THIS CASE NAME, PROJECT, TYPE
Set rngList = wsList.Range("A" &amp; iLastRowList) 'initial value A3

'Copy individual names in Col A (A3 initially) into as many rows as there are data columns
'in the cross tab (less 3 for Col A-C).
rngCTab.Copy rngList.Resize(iLastCol - 3)

'SELECT THE HEADING ROW WITH FORECAST/ACTUAL
'Move up ROW (INITIALLY 3) rows less TWO and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 2), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 3).PasteSpecial Transpose:=True

'SELECT THE ROW WITH THE WEEK NUMBERS
'Move up ROW (INITIALLY 3) rows less ONE and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 1), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 4).PasteSpecial Transpose:=True

'Staying on same row (3 initially) copy the data from the cross tab
rngCTab.Offset(, 3).Resize(, iLastCol - 3).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 5).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 3)

'increment ROW by 1
Next ROW

End Sub

I’m afraid the VBA code isn’t rendering very well – you’ll have to replace all the ” and ‘ marks before the macro editor will recognise the code.
Here is a copy of file – you could copy the macro code from here: Excel to CrossTab.xls

Outlook 2007 – Creating Search Folders for Combined Categories

I’ve recently gone down the route of setting up my Outlook email system for use with the getting things done (GTD) methodology. This means I work to a zero inbox and have limited context based folders such as @ACTION, @REFERENCE, @READ. I have been religiously categorising my mail before assigning to the reference pile and using Search folders in place of my previous system of dragging the email into a sub-sub-folder that never proved to be the right one when I came to look for it again.

The beauty of the categorise and Search Folder functionality is that I can apply multiple categories to a single email and I no longer have to decide whether to file my email in the “Wandsworth” folder or the “SLA” folder. If I assign it both categories, it will appear in my search folder under both categories.

I wanted to set up a few search folders that would enable me to view emails that were categorised in more than one specific folder using the logical “AND” rather than “OR” that the system defaults to. For example, it is the commissioning period at the moment and it would be handy for me to have a few search folders set up for the next month or so that group together emails categorised as 2010 SLA and a specific borough.

My initial attempts at using the custom search folder dialog box and entering the instruction “1a. Wandsworth AND 3a. SLA 1011” in the “More Choices” tab did not work as it was presumably looking for a single category. I did a bit of a google search and didn’t find anything very promising so having found a solution I thought it might be useful to write it up.

  1. Create a new Search Folder by right clicking on Search Folder and selecting New.
  2. Scroll down to the bottom and select Create a Custom Search Folder and then hit Choose
  3. Give it a name and then hit Criteria
  4. Within the Advanced tab set the field to equal Categories (it’s in the frequently used drop down)
  5. Leave the condition as contains
  6. Write your category names separated by “AND” in the Value field.
  7. Hit “Add to list”
  8. OK your way out of the dialog box and you should have a functioning multiple category search folder.

Instructions for Setting up Outlook 2007 for GTD

I’m moving to a new job soon and wanted to take the opportunity to set up the new email system in line with GTD (Getting Things Done) methodology.

Here’s my checklist for setting up the new system on Outlook 2007.
Most tips and code are taken from Simon Guest over at Simon Says but I’ve amended the code (very slightly) to suit my particular needs and naming conventions. For further details and descriptions please follow the links to his blog where I’ve listed them.

1. Create Folders at same level as Inbox

  • @ACTION REQD
  • @MEETINGS
  • @READ
  • @REFERENCE
  • @PERSONAL
  • @WAITING FOR

2. Create Macro for categorising and assigning email as task:

To create a new task from an email, select the email, run the NewTask macro, choose categories and at least one @CATEGORY, in my case @ACTION. Then you can choose to rename the subject title – this will become the new task.

    Function FileFolderEntryId() As StringDim myolApp As Outlook.Application
    Dim myNamespace As Outlook.NameSpace
    Dim myInbox As Outlook.Folder
    Dim rootFolder As Outlook.Folder
    Dim subFolders As Outlook.Folders
    Dim subFolder As Outlook.Folder
    Dim fileFolder As Outlook.Folder
    Dim fileEntryID As String
    Dim fileFolderName As String

    ‘Set the folder name – must be at the same level as the inbox
    fileFolderName = “@ACTION REQD”

    ‘ Move the the file folder
    Set myolApp = CreateObject(“Outlook.Application”)
    Set myNamespace = myolApp.GetNamespace(“MAPI”)
    Set myInbox = myNamespace.GetDefaultFolder(olFolderInbox)
    Set rootFolder = myInbox.Parent
    Set subFolders = rootFolder.Folders

    Set subFolder = subFolders.GetFirst
    Do While Not subFolder Is Nothing
    If subFolder.Name = fileFolderName Then
    fileEntryID = subFolder.EntryID
    Exit Do
    End If
    Set subFolder = subFolders.GetNext
    Loop

    ‘ return the entry ID for the file folder
    FileFolderEntryId = fileEntryID

    End Function

    Sub NewTask()

    Dim item As MailItem
    Dim myolApp As Outlook.Application
    Dim myNamespace As Outlook.NameSpace
    Dim fileFolder As Outlook.Folder
    Dim newName As String

    ‘ Pick the category
    Set item = Outlook.Application.ActiveExplorer.Selection.item(1)

    ‘ Mark as unread
    item.UnRead = False
    item.Save
    item.ShowCategoriesDialog

    ‘validate to see whether two categories exist, including an action
    If (item.Categories <> “”) Then
    If (InStr(item.Categories, “@”) > 0) Then
    If (InStr(item.Categories, “,”) > 0) Then

    ‘ Set the follow up flag
    item.MarkAsTask (olMarkNoDate)

    ‘ Move the item to the file folder
    Set myolApp = CreateObject(“Outlook.Application”)
    Set myNamespace = myolApp.GetNamespace(“MAPI”)
    Set fileFolder = myNamespace.GetFolderFromID(FileFolderEntryId())

    ‘ Ask for a different name if required
    newName = InputBox(“Please enter a subject for the task:”, “Task Subject”, item.TaskSubject)
    item.TaskSubject = newName
    item.Save

    item.Move fileFolder
    End If
    End If
    End If
    End Sub

3. Create a rule that will send a email from me and cc’d to me as @WAITING FOR category and file

This is a way of keeping track of requests you’ve sent out in the @WAITING FOR folder.


4. Create a macro that will categorise and send mail to the @REFERENCE Folder

    Sub ToReferenceAndCategorise()

    Dim item As MailItem
    Dim myolApp As Outlook.Application
    Dim myNamespace As Outlook.NameSpace
    Dim myInbox As Outlook.Folder
    Dim rootFolder As Outlook.Folder
    Dim subFolders As Outlook.Folders
    Dim subFolder As Outlook.Folder
    Dim fileFolder As Outlook.Folder
    Dim fileEntryID As String
    Dim fileFolderName As String

    ‘Set the folder name – must be at the same level as the inbox
    fileFolderName = “@REFERENCE”

    ‘ Pick the category
    Set item = Outlook.Application.ActiveExplorer.Selection.item(1)
    item.ShowCategoriesDialog

    ‘ Move the the file folder
    Set myolApp = CreateObject(“Outlook.Application”)
    Set myNamespace = myolApp.GetNamespace(“MAPI”)
    Set myInbox = myNamespace.GetDefaultFolder(olFolderInbox)
    Set rootFolder = myInbox.Parent
    Set subFolders = rootFolder.Folders

    Set subFolder = subFolders.GetFirst
    Do While Not subFolder Is Nothing
    If subFolder.Name = fileFolderName Then
    fileEntryID = subFolder.EntryID
    Set fileFolder = myNamespace.GetFolderFromID(fileEntryID)
    item.Move fileFolder

    Exit Do
    End If
    Set subFolder = subFolders.GetNext
    Loop

    End Sub

5. Repeat above for Categorise and send to @Waiting For Folder

6. Create a macro that will categorise and send mail to the @READ Folder

This is the same as above but I want to include it under my task list, which I have sorted by folder, so I also include a routine to add a no date follow up flag.

    Sub ToReadAndCategorise()

    Dim item As MailItem
    Dim myolApp As Outlook.Application
    Dim myNamespace As Outlook.NameSpace
    Dim myInbox As Outlook.Folder
    Dim rootFolder As Outlook.Folder
    Dim subFolders As Outlook.Folders
    Dim subFolder As Outlook.Folder
    Dim fileFolder As Outlook.Folder
    Dim fileEntryID As String
    Dim fileFolderName As String

    ‘Set the folder name – must be at the same level as the inbox
    fileFolderName = “@READ”

    ‘ Pick the category
    Set item = Outlook.Application.ActiveExplorer.Selection.item(1)
    item.ShowCategoriesDialog

    ‘ Set the follow up flag
    item.MarkAsTask (olMarkNoDate)

    ‘ Move the the file folder
    Set myolApp = CreateObject(“Outlook.Application”)
    Set myNamespace = myolApp.GetNamespace(“MAPI”)
    Set myInbox = myNamespace.GetDefaultFolder(olFolderInbox)
    Set rootFolder = myInbox.Parent
    Set subFolders = rootFolder.Folders

    Set subFolder = subFolders.GetFirst
    Do While Not subFolder Is Nothing
    If subFolder.Name = fileFolderName Then
    fileEntryID = subFolder.EntryID
    Set fileFolder = myNamespace.GetFolderFromID(fileEntryID)
    item.Move fileFolder

    Exit Do
    End If
    Set subFolder = subFolders.GetNext
    Loop

    End Sub

7. Create a macro that will create Task and Mail Search Folders

    Sub CreateNewSearchFolder()

    Set MyOutlookApplication = Outlook.Application
    SearchSubFolders = True
    Set MapiNamespace = Application.GetNamespace(“MAPI”)
    Set TasksFolder = MapiNamespace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderTasks).Parent
    strS = “‘” & TasksFolder.FolderPath & “‘”

    Dim folderName As String
    folderName = InputBox(“What category would you like to create a search folder for?:”, “Category”, “”)
    Dim objSch As Search
    Dim categoryFilter As String
    categoryFilter = “(“”urn:schemas-microsoft-com:office:office#Keywords”” LIKE ‘%” & folderName & “%’)”
    Dim taskFilter As String
    taskFilter = “(“”http://schemas.microsoft.com/mapi/proptag/0x0e05001f””= ‘Tasks’ AND “”http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81010003″” <> 2) OR (NOT(“”http://schemas.microsoft.com/mapi/proptag/0x10900003″” IS NULL) AND “”http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81010003″” <> 2)”
    Dim strTag As String
    strTag = “RecurSearch”

    ‘ Create the tasks folder
    Set objSch = Application.AdvancedSearch(Scope:=strS, Filter:=categoryFilter & ” AND (” + taskFilter + “)”, _
    SearchSubFolders:=True, Tag:=strTag)
    objSch.Save (folderName)

    ‘ Create the mail folder
    Set objSch = Application.AdvancedSearch(Scope:=strS, Filter:=categoryFilter, _
    SearchSubFolders:=True, Tag:=strTag)
    objSch.Save (folderName & ” (Mail)”)

    End Sub

8. Create custom toolbars for the above macros

  • Right click on toolbar – Customise
  • Select commands tab the Macro on Left
  • Drag desired macro to toolbar
  • Right click on macro button and rename and assign keyboard shortcut if necessary (you need to do this with the customise box still open)

9. Introduce colouring to organise selected folders

  • Go to Tools> Organize and complete the following steps:
  • select Using Colours
  • Change “Colour messages from [you] in Silver”
  • Apply Colour
  • Turn on the “Show messages sent only to me in Blue”

10. Panic if new job doesn’t use Outlook 2007

Alternatively I could start working through some tips for using Outlook 2003 for GTD, starting here.

I am still a little bemused as to how to deal with SENT items in Outlook, I’ve seen macros directing all sent items back into the inbox to be categorised and filed but I would prefer the category dialog box to pop up when I press send.  I’ve currently set up a lot of rules which apply categories on the basis of who I have sent them to but it is not foolproof.

Conditional Ranking in Excel – RankIF using SumProduct

I was hunting around for a RANKIF function yesterday so I could rank a load of hospitals in terms of their cost per procedure.

RankIF

Unfortunately RANKIF isn’t one of the available functions but I managed to achieve the same effect by utilising the SUMPRODUCT function.

I wrote a SUMPRODUCT tutorial some time ago indicating how the powerful function can act as a multi conditioned SUMIF formula but it seems it can also act as a multi conditioned RANKIF formula as well.

The formula I used to achieve the above ranking was:

=1+SUMPRODUCT(($B$2:$B$12=B2)*($C$2:$C$12>C2))

Copied down the column it will return the number of organisations for a given procedure which have costs higher than the selected row. Adding 1 to the result just ensures that the highest cost organisation starts with a rank of 1 rather than 0.

Excel CrossTab Table to Flat List

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list.

Converting a table like this:

sumproduct

Back to the data sheet like this:

sumproduct

If you didn’t have to do this sort of task very often you could get by with a pivot table solution and a few stages of formatting but it would only work if your data values were numbers that could be summed in the pivot.

If you need to convert Excel data tables (or crosstabs) into flat lists on a regular basis then you will need a macro.

I have prepared a workbook that demonstrates both the pivot table and macro solution and you can download it from here:
ExcelTabletoFlatList.xls

The following macro does the trick and utilises the VBA I introduced in my last post on consolidating worksheets, to determine the last used row and last used column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim I As Long

Set wsCrossTab = Worksheets("Sheet1")
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).Row

'Set the initial value for the row in the destination worksheet
iLastRowList = 2

'Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A1").End(xlToRight).Column

'Create a new sheet and set the heading titles
wsList.Range("A1:C1") = Array("NAME", "GRADE", "VALUE")

'Start looping through the cross tab data

For I = 2 To iLastRow

Set rngCTab = wsCrossTab.Range("A" &amp; I) 'initial value A2
Set rngList = wsList.Range("A" &amp; iLastRowList) 'initial value A2

'Copy individual names in Col A (A2 initially) into as many rows as there are data columns
'in the cross tab (less 1 for Col A).
rngCTab.Copy rngList.Resize(iLastCol - 1)

'Move up a I rows less one and across one column (using offset function) to select heading row. Copy.
rngCTab.Offset(-(I - 1), 1).Resize(, iLastCol - 1).Copy

'Paste transpose to columns in the list sheet alongside the names
rngList.Offset(0, 1).PasteSpecial Transpose:=True

'Staying on same row (2 initially) copy the data from the cross tab
rngCTab.Offset(, 1).Resize(, iLastCol - 1).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 2).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 1)

'increment I by 1
Next I

End Sub

Macros to Consolidate Worksheets

I’ve got a spreadsheet with tabs for each individual directorate. Within the tabs are tables with the same layout but different sizes (number of rows).

I want to group together all the used data on each tab and create a single consolidated sheet of continuous data.

Obviously I need a macro to copy and paste my data from each sheet but the tricky bit is selecting the relevant (or used) area in each sheet.

On my search around the interweb I found some really useful snippets of VBA code on the Ozgrid website which enable you to determine the last used row according to a number of different criteria and assign this to a variable such as “iLastRow”.

1
2
'find row before next blank cell in column A
iLastRow = Range("A1").End(xlDown).Row
1
2
'find last used row in column A
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
1
2
'find last used row on sheet
iLastRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _SearchDirection:=xlPrevious).Row

It’s a simple step then to use this variable to define a range of used cells.

1
Range("A1:P" &amp; iLastRow).Select

I’ve used the above techniques to consolidate my worksheets and here’s a copy of my working code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub ConsolidateSheetsforSubForm()
' ConsolidateSheetsforSubForm Macro
' Copies used subform data in individual sheets and pastes into one sheet as continuous data

Sheets("Ap Table Sub Form").Select

'find last used row column a of Ap Table Sub Form sheet
ilastrowsubf = Cells(Rows.Count, "a").End(xlUp).Row

' Clear the existing data in the subform sheet
'specify location in subform to clear data
Range("a5:k" &amp; ilastrowsubf).ClearContents

' Start selecting the data from Sheet S&amp;M (LY)
Sheets("S&amp;M (LY)").Select

' find last used row in column I
ilastrowsm = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowsm variable to define range to copy in S&amp;M sheet
Range("i4:s" &amp; ilastrowsm).Copy

' specify location in subform to paste data
Sheets("Ap Table Sub Form").Select
Range("a5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("K&amp;R (BV)").Select

' find last used row in column I of K&amp;R sheet
ilastrowkr = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowkr variable to define range to copy in K&amp;R sheet
Range("i4:s" &amp; ilastrowkr).Copy
Sheets("Ap Table Sub Form").Select

' find first blank row in column a of Ap Table Sub Form sheet
ifirstblankrowsubf = (Cells(Rows.Count, "a").End(xlUp).Row) + 1

' specify location in subform to paste data
Range("a" &amp; ifirstblankrowsubf).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("OVERHEADS").Select

' find last used row in column I of OVERHEADS sheet
ilastrowover = Cells(Rows.Count, "i").End(xlUp).Row

' use ilastrowover variable to define range to copy in OVERHEADS sheet
Range("i4:s" &amp; ilastrowover).Copy
Sheets("Ap Table Sub Form").Select

' find first blank row in column a of Ap Table Sub Form sheet
ifirstblankrowsubf = (Cells(Rows.Count, "a").End(xlUp).Row) + 1

' specify location in subform to paste data
Range("a" &amp; ifirstblankrowsubf).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
End Sub

You can do similar things in order to find the last column as well:

1
2
3
4
5
6
7
8
9
'find column before next blank cell in row 1
iLastColumn = Range("A1").End(xlToRight).Column

'find last used column in row 1
iLastColumn = Cells(Columns.Count, "a").End(xlToLeft).Column

'find last used column on sheet
iLastColumn = Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column