Once there was a hacker who needed to rescue a beautiful princess from her prison on a creaking ship moored in the middle of the sky.  He made it onto the noisy old ship, slipped past the guards and tiptoed down the swaying halls to the room where she wept, chained to an excel spreadsheet.
“Hello there Princess! I’ve come to whisk you away!”
“My Hero! I can’t leave this spreadsheet till I know that every URL in this column is correct. I’m stuck clicking on each link and it’s terrible! Sometimes Excel does things I don’t want, it is confusing and I fear I’ll be here till I die…”
The hacker thought for a moment and drew from his bag of tricks a magical Ruby that could cut her chains.
# You need to install 2 things to use this tool
# 1. Ruby - http://www.ruby-lang.org/en/downloads/
# 2. WxRuby - once you've installed Ruby, go to Start, Program Files, Ruby, RubyGems, Ruby Gems Package Manager
# Now type gem install wxruby
# 3. you should be ready to run this
require 'net/http'
require 'uri'
require 'win32ole'
require 'wx'
include Wx
class MyApp < App
path_to_wbook = 'C:\temp\file_to_check.xls'
worksheet_with_urls = 'Generic Content'
column_with_urls = 9
file_types = "Excel files (*.xls)|*.xls|New Excel Files (*.xlsx)|*.xlsx"
def get_worksheet(sheet_name, wbook_path)
wbook = open_workbook(wbook_path)
sheet = wbook.Worksheets(sheet_name)
return sheet
end
def open_workbook(wbook_path)
excel = WIN32OLE.new('Excel.Application')
workbook = excel.Workbooks.Open(wbook_path)
return workbook
end
def url_is_good(url)
uri = URI.parse(url)
response = nil
Net::HTTP.start(uri.host, uri.port) { |http|
response = http.head(uri.path.size > 0 ? uri.path : "/")
}
if response.code != "200"
puts "Response code was " + response.code
end
return response.code == "200"
end
def on_init()
file = Wx::FileDialog.new( nil, "Choose a file", "", "", file_types, Wx::OPEN|Wx::CHANGE_DIR|Wx::FILE_MUST_EXIST )
case file.show_modal()
when Wx::ID_OK
puts "File: %s, Directory: %s" % [ file.get_filename, file.get_directory ]
path_to_wbook = file.get_path
when Wx::ID_CANCEL
puts "Starting up with the default workbook"
end
position = Wx::SingleChoiceDialog.new(nil, "Which column are the URLS in?", "URL Column", "ABCDEFGHIJKLMNOPQRSTUVWXYZ".split(//))
position.set_selection(8)
case position.show_modal()
when Wx::ID_OK
puts "Chose column " + position.get_string_selection
column_with_urls = position.get_selection + 1
puts column_with_urls
when Wx::ID_CANCEL
puts "Canceled, going with default"
end
puts "Opening up " + path_to_wbook
sheet = get_worksheet(worksheet_with_urls, path_to_wbook)
for row in sheet.UsedRange.Rows
cell = row.Cells(column_with_urls)
val = cell.text
next if val.nil?
#we need to pull any whitespace from the front or the back of the string
val = val.strip
#if the url doesn't contain % then we should try to escape it.
val = URI.escape(val) unless val.include?('%')
URI.extract(val) do |url|
if url_is_good( url)
#let's clear the cell color
cell.Interior['ColorIndex'] = ExcelConst::XlColorIndexNone
#puts "good: " + url
else
#highlight this cell
cell.Interior['ColorIndex'] = 36
puts "BAD LINK FOUND"
puts "BAD: " + url
end
end
end
puts "Gone through all the urls, saving and closing down now."
sheet.Parent.Close(1)
puts "goodbye!"
end
end
class ExcelConst
end
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, ExcelConst)
MyApp.new.main_loop()
The princess used the script and it slipped her chains. She took his hand, gave him a kiss, and they escaped down to the city streets. There they went to a nice dinner.
Much thanks to David Mullet’s “Ruby on Windows” blog, where I learned about using win32ole and wxRuby.
But wait, there's more
This work by MattK is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International