Flickr FREE account - making it work

Flickr is a great service for storing online photographs. First of all, it's FREE and allows unlimited storage and you can always download the original sized images to boot. Of course, despite these fantastic features, the FREE service is limited to 300MB uploads a month and also only being able to see the most recent 500 uploads on your photostream; the old uploads are still there but are lost on the photostream and Flickr hopes you'll cough up the $24 per year to get them back on your photostream again.

If you wanted to be REALLY cheap (which I can be :)) you could track every URL of every picture you've ever uploaded and never need to become a paying customer (but that really is kind of cheap, especially since Flickr isn't all too pricey anyway). I won't go on about all the alternative photo-sharing sites available in this post, however, because there ARE alternatives, but since I am a fan of FLICKR (and have been since well before Flickr took over YAHOO Photo's), I am going to focus on a few tricks I've developed to keep my free account alive and working for me.

The basic concept is simple: you upload your pictures in batches every month (and if you take more than 300MB of decent photo's in a month this won't really work out for you and you'll need to buy into Flickr's service, which I think is what they expect to be the case) and then you copy and paste the picture URLs into a spreadsheet for easy referral later on. I've set up a GOOGLE SPREADSHEET for this and I have categories and sub-categories so it's easy to search for what I want, and then just hit the URL to load up the picture (if it's lost on my photostream).

The problem comes in when you have to manually copy and paste one hundred shortcuts; this can be REALLY time consuming, so instead I've set up a basic EXCEL MACRO to do most of the work for me. It's still a bit laborious, but to save $2 a month is worth the satisfaction for me, although truth be told I am only moments away from becoming a paying subscriber because I really don't like the monthly upload limitations for the FREE account.

Anyway, I digress: the point is, you can keep your FREE account alive and kicking using the following tricks that I've hacked together.

1. Create an EXCEL spreadsheet
2. Create 4 sheets inside it
3. Create a  MACRO and paste the following VBA code into it (feel free to tweak it... it's OpenSource :)). Just remember if you rename the Sheets you'll have to update the code.

Sub GetFlickrURLs()

'

' GetFlickrURLs Macro

'

' Keyboard Shortcut: Ctrl+q

'

    Cells.Find(What:="class=""activity", After:=ActiveCell, LookIn:= _

        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    ActiveCell.Offset(2, 0).Range("A1").Select

    Selection.Copy

    Sheets("Sheet2").Select

    ActiveSheet.Paste

    ActiveSheet.Paste

    Application.CutCopyMode = False

    ActiveCell.Offset(1, 0).Range("A1").Select

    Sheets("Sheet1").Select

End Sub

What this macro does is look for the string class="activity" in the page source for the FLICKR photostream page because the next line in the source is going to be the URL for your image. Once it's found the string, it moves one cell down and copies that line and pastes it into Sheet2 and returns to Sheet1 on the following line. Repeat this CTRL+Q shortcut for each picture. There are eighteen (18) per page by default so don't stress about doing it for hours, and it's advisable to set up a conditional format to highlight duplicates of Column C in Sheet2 just to be sure you aren't repeating yourself  :)

• Once you're done with the initial macro, you will have eighteen URLs in Sheet2 but they won't be very useful right now...

                <a href="/photos/kateyjohnston/5485956280/" class="Plain">0 comments</a>

• What you need to do now is pull out the meaningful bit of the Hyperlink Reference (HREF). Since each user is going to be a variable length, before we can do this we need to first establish the length of the root URL (the /photos/kateyjohnston/ part above). 
  
  
  Use =LEN("/photos/username/") to find the length. In this case with user KateYJohnston it's 22 characters, but if your user name is BobSmith then the result of =LEN("/photos/BobSmith/") would be 17. Another example is if the username is Jack then the result would be 13. Hopefully you understand where I'm going with this...
  
  
• Armed with the length information of your root URL (the /photos/username/ above) you can now extract it from the <a href=...> in Sheet2. If you want to be quick and follow my set up, I've got all the untouched URLs pasted in Column A of Sheet2, so I just insert a formula in Column B to pull out the bits I need. It's a nested formula but I'll include the broken down parts too for your information.
  We are going to be working on A1 in my examples but you can substitute this as a relative cell reference for the 17 other pictures on the photostream page.
  
  
• =RIGHT(LEFT(A1,(LEN("/photos/kateyjohnston/")+11+10+15)),(LEN("/photos/kateyjohnston/")+11))
  
  
• Which is a nested formula of these two
• =LEFT(A1,(LEN("/photos/kateyjohnston/")+11+10+15))
• Pulls the left piece of the string, but includes leading spaces
• e.g.                 <a href="/photos/kateyjohnston/5485956280/
• =RIGHT(B1,(LEN("/photos/kateyjohnston/")+11))
• Pulls the right piece of the string above and removes the leading spaces and "<a href=" part
• e.g. the end result is: /photos/kateyjohnston/5485956280/
  
  
• Please remember to substitute kateyjohnston for your FLICKR username
  
  
• From this point you can copy the formula to each row and you'll end up with column B (if you used the nested formula) with the root URL and unique page number. To then make this into a final product that is a workable URL, in column C I have a concatenation of the base URL for flickr ("http://www.flickr.com/") with the URL in column B:
  
  
• ="http://www.flickr.com"&B1
  
  
• The resulting values in column C can now simply be copied and pasted into the GOOGLE SPREADSHEET for easy reference of all the URLs in FLICKR once the PHOTOSTREAM has exceeded 500 images.

And with that, I have now shown how much of a cheap skate I could choose to be if I don't sign up for FLICKR :)

* If you're a novice to VBA, or just plain lazy, you can grab my template file here and modify it to suit your needs...