Automate Excel

Just a quick note that this site is now publishing partial rss and atom feeds. So instead of seeing the entire contents in your feed reader, you will only see about the first paragraph. You will have to click through to read the full post here.

Why am I doing this? People are republishing my feed on their sites, and in it’s entirety because that’s what I publish in my rss.

Basically anybody with a free afternoon can figure out how to republish rss feeds. In the past I dealt with the couple people who did this individually, but it’s becoming harder to do and a much more frequent occurance.

For an example here’s a site I noticed doing this today (the second this week): http://headlinedepot.com/feedview.php?feedid=1850 (screenshot)

You’ll notice only partial feeds show up there now, however earlier today they were displaying the entire content of each of my posts, reading every word of my rss feed and republishing.

So after doing some research I found multiple opinions that the only way to truly protect your feed from getting hijacked is to not publish the full feed, and hence my partial feeds.

Solution #670 on how to export a range to a delimited text file.

This is a handy alternative to using Excel’s default way to save the worksheet as a Text file or CSV delimited file by choosing “save as” when:

1. You use a template and only want to export the data excluding headers and other misc stuff
2. Similarly, You may only want to export part of a dataset
3. You want to save the file with a custom delimiter that may be unique to your application.

If you have a need to do 1,2, or 3 a function like the following may help. It accepts a range to export, location to save the file, and the delimiter you’d like your data separated with, then saves your data as specified.

Here’s how to call the function ExportRange:

Sub CallExport()
'ExportRange(range,where,delimiter)

Call ExportRange(Sheet1.Range("A1:C20"), _
"C:\mark.txt", ",")
End Sub

First you tell the function the range you want to export, then where to export it, than the delimeter to use. You’ll also need the function ExportRange, here it is:

Function ExportRange(WhatRange As Range, _
         Where As String, Delimiter As String) As String

Dim HoldRow As Long    'test for new row variable
    HoldRow = WhatRange.Row

Dim c As Range    'loop through range variable

For Each c In WhatRange
    If HoldRow <> c.Row Then
        'add linebreak and remove extra delimeter
ExportRange = Left(ExportRange, Len(ExportRange) - 1) _
                          & vbCrLf & c.Text & Delimiter
        HoldRow = c.Row
    Else
        ExportRange = ExportRange & c.Text & Delimiter
    End If
Next c

'Trim extra delimiter
ExportRange = Left(ExportRange, Len(ExportRange) - 1)

'Kill the file if it already exists
If Len(Dir(Where)) > 0 Then
    Kill Where
End If

Open Where For Append As #1    'write the new file
Print #1, ExportRange
Close #1

End Function

I’m a newbie to Last.fm (my profile here), the site that collects your music data and creates charts for your listening behavior, and also lists your musical neighbors to help you find new music.

I’m liking the service so far, so exploring I came across the last.fm web services which give you access to your musical data, such things as your top artists, top albums, top tracks, and more.

Note: One thing I wish the api gave me is access to a complete data dump of my tracks played since day one.

Moving along, if you’ve read this far you probably are wondering how to get the data into Excel.. It’s quite easy really.

If you visit the last.fm web services page again you will see an XML link next to each one of the webservices available. Clicking on that link will give you a URL something like this:

ws.audioscrobbler.com/1.0/user/RJ/topartists.xml

To display your data simply replace the user RJ with your username and your top artists will be displayed.

So now you’ve got a unique URL for your username, that returns XML for your top artists. Getting that data into Excel is as easy as adding an XML Map and refreshing the data.

A quick review:
1. Goto your Last.Fm URL and copy the URL
1. In Excel: On the main menu goto Data->XML->XML Source
2. Click the XML Maps Button
3. In the newly opened dialog box click the Add button
4. In the File Name box press CTRL-V to Paste your URL (click ok,ok)
5. Back in your spreadsheet click and drag the fields you want to see onto your spreadsheet
6. Right click your new headers, click XML->Refresh XML Data

That’s it, you’ll now have your last.fm data in Excel:

Louis was wondering if it’s “possible to make a hyperlink in Excel that will always link to the folder in which the file is presently saved”? I had a free minute to give it a whirl and it looks like it’s indeed possible:

To get the location of the folder your spreadsheet is in try this:

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

Then just add a basic hyperlink, substituting the location with the previous formula:

=HYPERLINK(MID(CELL("filename"),1,
FIND("[",CELL("filename"))-1),"Current Folder")

Viola! You now have a hyperlink in your worksheet, that when clicked will open the current folder (reminds me of the vba open dialog box).

Note: The second example has a line break in it. If you are copying and pasting, make sure that formula is all on 1 line.