Sparkline

So today at work I came across a really cool feature in Microsoft Excel 2013. Enter the Sparkline.

Probably more of a gimmick but they look cool. They are a little chart that is just inside a cell. It can be a line,column or win/loss. They are easy to create and could be used if you don't the all of the features and hassle of a full chart.

Here's how to create a sparkline in excel:
Start by putting you data into one column. There aren't any labels so they don't really matter for this.

Then in the Insert tab on the ribbon find the Sparkline section and choose the style you want. This time we will create a win/loss style.

This will open the Sparkline dialog.

Select the data range. Make sure you don't select any labels as this will give an error.

When the Sparkline is created it will be small, the size of your cell so you will probably want to make the cell bigger.

And there you have it.

Probably just a gimmick but might be useful eventually.

Cheers,
Rex

Code Today: Create a kml file with VBA

Hi, another nerdy post today. The other day I came across some code I had written that generates a .kml file that points to a street address. It is a little kludgy but it has worked for the last few years. First I will show the code, explain it, then I'll talk about what I'd do differently if I were to do it now.

Sub Create_kml()
       
    Dim Name, Details, Address As String
    'Get job details into RAM
    Name = Range("Q3") & Range("R3")
    Details = Range("M3")
    Address = Range("K4")
   
    'Open kml file
    Dim fso, tf
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set tf = fso.CreateTextFile("File Location" & Name & ".kml", True)
    'Write actual kml file
    tf.Writeline ("<?xml version=""1.0"" encoding=""UTF-8""?>")
    tf.Writeline ("<kml xmlns=""http://www.opengis.net/kml/2.2"" xmlns:gx=""http://www.google.com/kml/ext/2.2"" xmlns:kml=""http://www.opengis.net/kml/2.2"" xmlns:atom=""http://www.w3.org/2005/Atom"">")
    tf.Writeline ("<Document>")
    tf.Writeline ("    <Placemark>")
    tf.Writeline ("        <name>" & Name & "</name>")
    tf.Writeline ("        <description>" & Details & "</description>")
    tf.Writeline ("        <address>" & Address & "</address>")
    tf.Writeline ("        <styleUrl>#m_ylw-pushpin</styleUrl>")
    tf.Writeline ("    </Placemark>")
    tf.Writeline ("</Document>")
    tf.Writeline ("</kml>")
    'Clear buffer and finalise file
    tf.Close
End Sub

So I may have to look into better ways of displaying code in Blogger. But I think you get the gist of this. The Name variable is used for the name of the waypoint in the kml. The Details variable is used for the description of the waypoint and the address is the street address for the pin to get put on. Looking at it you can probably omit the fso object as it is only used once so you would just have a longer command to set the text file object.

The tf object is created with a specified file. Then writing it is just a case of pushing lines to it. The only way I know of to use a template for writing a file like this is by coding it in place like this.

The biggest thing I would do differently knowing what I know now is that rather than hard coding the locations to get the variables (Name, Details & Address) from. I would pass them to the sub(or write it as a function) so I can copy the code and re-use it without any modification.

Any questions or comments are appreciated.

I hope this helps someone with a project somewhere along the line.

Cheers,
Rex

Disable Animation in Microsoft Excel 2013

A bit of a nerdy post today.

So I recently got a new computer at work. Along with the new hardware and the performance that comes with it I got a copy of Microsoft office 2013. This is taking some getting used to. However there are a few little niggles that have been bothering me.

One of these that I have been able to fix is the animation within Excel. I'm not quite sure why this has been set up but it is easy enough to fix. Continue past the break to see how to fix this "feature"

Bypass ".docx" Password Protection

If you're just here for the how to, skip down to the pictures after the break.

Now I know that bypassing passwords is something that not everyone likes to do. However sometimes you can forget the password for a critical file or someone has sent you a file and forgotten to tell you the password to edit it.

Microsoft office's new (can I still call it that) xml based document formats have capacity for accidental write protection. Some of these include password protection. While this is great, particularly if sending spreadsheets to a customer; it isn't so great when HR send a file that needs to be edited and even they can't remember the password. That was the problem and thanks to Horatio and this Superuser answer. I was able to save the day and remove the password protection.