How to download KML data from Google My Maps

I had put a lot of time into a particular custom map under Google "My Maps" and I wanted to make an offline backup of the data. I clicked on the "View in Google Earth" button when viewing my Google "My Maps" map and it downloaded a KML file which I could open in Google Earth. However, when I opened the KML file in a text editor I saw that it didn't contain any actual map data but instead just had a http URL pointing back to Google Maps. After some experimentation I discovered that I could download the actual map data by taking the URL from inside the downloaded KML file and changing each instance of "&" into plain old & and then pasting the edited URL into my browser address bar. This returned a real KML file with real data.

Incidentally, you can display a KML file on Google Maps by uploading the KML file to any webserver and then putting the http address of the file in the Google Maps search box and hitting enter. Google maps will then display the KML file placemarkers and paths on Google Maps.

How to convert Microsoft Word documents to clean HTML

I often want to take a MS Word document that someone has sent me and convert it to HTML so that I can distribute it to others via the web rather than emailing it around to people. MS Word has built-in HTML conversion, but the resulting HTML is so ugly, confusing, and bloated, that it is very hard to work with if I want to do edits or tweak formatting.

Here is my current favorite method for converting Microsoft Word documents to clean HTML using Gmail:

1. I email the document to my Gmail account.
2. When I view the email in Gmail there is a "View as HTML" link provided for the attachment, which I click to see the Word document displayed in my web browser.
3. At the next screen click the View link in the upper left, which brings up a menu of viewing options. Choose Plain HTML.
   
4. Then with the document showing in my browser I go to view page source (Firefox for Mac = View -> Page Source) on my browser, which shows me the underlying HTML, which I block and copy into a new file in a test editor.

The HTML produced by Gmail is very old school (it doesn't use CSS and probably half the tags it uses are deprecated), but it is very compact, clean, and consitent, so you can quickly convert it to more modern HTML using search and replace.

Transact SQL for including concatenated text fields in WHERE clause

I am working with a database on an SQL Server that carries forward a legacy table structure where the description field for a Purchase Order line item is spread across multiple text columns in the table.  In other words, instead of having one 250 character text field for the item description it has a total of 7 text fields of (I think) about 25 characters each.  I have a web application where users can search for purchase orders using the description fields.  My original solution was to check for the user's search term across each of the seven text fields using OR clauses.  However, this meant that a user's search term would not be found if it happened to straddle the border between two of the seven fields.

To solve this problem I tried concatenating all of the description fields and then testing the concatenated string using LIKE, but that resulted in some POs not being inlcuded in the results when they should have been.  I finally puzzled out that what was happening was that database treated empty description fields as null and Transact SQL gives a null value for the whole concatenation if you try to concatenate a number of valid string values with one null value.  In other words if you concatenate Field1 + Field2 + Field3, and the first two fields have text in them, but Field3 does not, then Transact SQL will give you a null result.  So the solution was to use ISNULL to convert null description fields to zero length strings when concatenating them for the WHERE clause, so that one null value doesn't ruin the whole concatenation, like so:

WHERE Field_1 + ISNULL(Field_2,"") + ISNULL(Field_3,"") + ISNULL(Field_3,"") LIKE %search-term%

Easy Find is a great free alternative to Spotlight for finding files

I usually give my files long and meaningful names, so to find stuff I just need a tool that will quickly and easily let me do a wildcard search on just file names.  I tried a few times, but I was never able to figure out how to do that kind of search using Spotlight.  I finally came across a free application called Easy Find from Devon Technologies that is optimized for the kind of searching I need.  Its pretty bare bones, but it does exactly what I want to be able to do.

Applescript to give a primative command line interface to MS Word 2008

One of the things I hate about MS Word is having to use the mouse to click through menus to do simple things. Of course you can create keyboard shortcuts for stuff, but I find it hard to remember really complicated keyboard combos. What I prefer is the old XyWrite approach, which is to hit a command key, then enter a two or three letter code for an action, and then hit enter to have it execute.

Back when I was using Word 2000 for Windows I found a simple solution: Create a keyboard shortcut for the Run Macro command (Ctrl-M), and then write macros with 2-3 character names. For example: I wrote a macro to accept all revisions in a document, then named it 'aa' (for accept all). Then to accept all revisions all I had to do was hit Ctrl-M, type aa, hit enter, and boom, all the revisions were accepted.

When I switched to Mac OS X and Word 2008 for Mac I couldn't find any obvious way to recreate this functionality, so I suffered through clicking through menus for a lot of commands. However, one day I just reached my limit with click-click-click-click and decided to find a solution using Applescript. Here is what I ended up with:

-- Open a dialog box for user input
set var_input to display dialog "Word command: " default answer ""
-- For some reason you need to do this to get the user input into a string variable
set var_input to (text returned of var_input) as string
-- Applescript apparently doesn't have SELECT CASE statements so use If Else to take different
-- actions based on user input
if var_input = "st" then
    -- Convert selected text to strikethrough font
    tell application "Microsoft Word"
        set strike through of font object of selection to true
    end tell
else if var_input = "aa" then
    -- Accept all changes in the document
    tell application "Microsoft Word"
        accept all revisions active document
    end tell
else if var_input = "kt" then
    -- Toggle keep text together (so text doesn't break over page break)
    tell application "Microsoft Word"
        tell selection
            if ((keep with next of paragraph 1 as boolean) is false) then
                set keep with next of every paragraph to true
            else
                set keep with next of every paragraph to false
            end if
        end tell
    end tell
else if var_input = "uc" then
    -- Convert the selected text to all UPPERCASE
    tell application "Microsoft Word"
        set case of characters of selection to upper case
    end tell
end if

What this does is open up a dialog box where you can type in a string of characters, and then based on what you entered it tells MS Word to do some command or other.

Then I saved the Applescript in the folder:
Users/myusername/Documents/Microsoft User Data/Word Script Menu Items

With the name:
Command Line\cM.scpt

Although its not at all obvious, the \cM.scpt at the end creates a Ctrl-M as a keyboard shortcut for the Applescript. For more on naming Applescripts in this folder so they have keyboard shortcuts see:

http://www.entourage.mvps.org/script/add_shortcuts.html

Its for Entourage, but the script naming conventions appear to be the same.

How to make keyboard shortcuts for Accept Revision and Next Revision in Microsoft Word 2008 for Mac

I find the native interface for working with tracked changes in MS Word 2008 to be unbearably clumsy. You have to turn on the Reviewing toolbar and then click an icon to get to the next tracked change, then click another icon to accept the change, and then repeat, which gets to be quite a clickapalooza with a long document with lots of changes.

I prefer to hit one keyboard shortcut to move to the next tracked change, then hit another keyboard shortcut to accept the change. For some reason Microsoft didn't bother to include these keyboard shortcuts. To set them up:

• Choose View -> Customize Toolbars and Menus
• Click the Keyboard button
• In the Categories box select Tools
• In the Commands box select AcceptChangesSelected
• Click in the box for "Press new keyboard shortcut" and then hit your preferred key combo. Be sure not to use a combo that is already assigned to something because it just won't work.
• Click the Assign button.

To make a keyboard shortcut to jump to the next tracked change just repeat, but this time select NextChangeOrComment in the Commands box.

I also like to have a keyboard shortcut for toggling track changes off and on. For that select ToolsRevisionMarksToggle in the Commands box.

Batch file with ampersand in its name won't run under Windows Task Scheduler

I wrote a batch file to run a simple script and then tried to schedule it using the Task Scheduler in Windows Server 2003, but for some reason it just wouldn't run a scheduled, even though I could run it with no problem by double-clicking on the batch file name.

After banging my head against the wall for a while, I finally tried taking the ampersand out of the name of the batch file, and suddenly Task Scheduler was able to run it.

Copying all the files that match a certain pattern in a directory structure over to a single directory

For a long time I have wanted to collect all the files matching a certain pattern (Inv*.pdf) from a directory structure and copy them all to a single directory.

A Windows utility, xxcopy, has a command line switch to do exactly this.

I also found a forum discussion on how to do it in both Windows and *nix. And another one just about Windows.

With xxcopy the command line syntax to do what I wanted is:

xxcopy d:\contract\inv*.pdf d:\procurement\invoices\ /SG /BI

The /SG switch tells it to recursively (i.e. including from subdirectories) copy all of the files matching the wildcard pattern to the single target directory (i.e. without recreating the subdirectories under the target).

The /BI switch only copies files that either do not exist in the target directory or are newer versions of existing files.

How to use Windows Task Scheduler to copy files from a server in another domain

My problem was that I wanted to do a nightly transfer of files froma Windows machine in another domain to my Windows Server 2003 machine.The script to do the file transfer was easy, but I couldn't find a wayto keep the remote server mapped as a network drive. If I mapped thedrive through the GUI and clicked "Reconnect at Logon" it wouldn'treconnect after the server rebooted. Possible part of what made itdifficult was that the remote server was in a different domain and Ihad a different credentials on the remote server.

I figured out a way to permanently map a network drive to the remote server in a different domain, but then I discovered that batch files run using the Task Scheduler do not see any mapped network drives. Apparently network drives are only mapped to drive letters for real interactive sessions, and a user's network drives are not mapped when you run run a Task Scheduler job as that user.

After poking around a bit I found that the solution was to enter my credentials for the remote server in the Stored User Names and Passwords (Start button -> Control Panel -> Stored User Names and Passwords) utility on the local server. Apparently if you do that, whenever the local machine tries to access a resource on the remote machine Windows automatically uses the stored credentials to log into the remote machine.

So, once I entered my credentials to the remote machine in Stored User Names and Passwords I then mapped a drive letter to that machine in the first line of the batch file like so:

net use n: \\servername\volumename /persistent:no

And then used that drive letter for the copy command in the batch file.

UPDATE 2009-09-04. Somehow my entries in Stored User Names and Passwords on a Windows Server 2003 machine vanished overnight. When I went into the Stored User Names and Passwords GUI application from the Control Panel and re-entered my credentials using the format "\\10.10.10.1\sharename" for the server name the stored credentials wouldn't work when I tried to access shares on remote servers. I then opened a DOS prompt and tried this:

net use \\10.10.10.1\sharename /savecred

which resulted in prompts for me to enter my user name and password, which I did. After that new entries showed up in the Stored User Names and Passwords panel access through the Control Panel, and the stored credentials worked when I tried connecting to remote servers. I did notice that the new entries created through the/savecred method used just 10.10.10.1 as the server name, with no slashes and no share name, so maybe that was the problem all along.

How to permanently map a network drive to a machine on another domain in Windows Server 2003

After a lot of trial and error trying different combinations of switches on the net use command, here is the network drive mapping method that resulted in a drive mapped to a remote machine in a different domain using different credentials that will survive a reboot of the local machine.

At a command prompt I ran:
net use h: \\server\folder /savecred /persistent:yes

I was then prompted to enter my username and password for the remote server, which I did. The drive was then successfully mapped, and the drive was still mapped after the machine was rebooted.

How to create a Mac OS X keyboard shortcut to print to PDF

I was getting sick and tired of having to go to the mouse to click the PDF button from the Mac OS X print dialog every time I print a web page to PDF (which is really the only "printing" I do anymore). I googled around and found this awesome tutorial about how to set up a keyboard shortcut for this that I never would have figured out on my own in a million years:

Keyboard Shortcut for “Save as PDF…” in OS X at macsparky.com

This only gotcha I ran into is that you have to enter "Save as PDF..." with "as" in all lowercase in order for it to work. Entering "Save As PDF..." doesn't work.

Backing up Google calendar using wget for Mac OS X

Back when I was on Windows I used wget to download the iCal file of my Google calendar as part of my nightly backup.  I am finally getting around to adding it to my Mac backup script.

First step; Does Mac OS X have wget built in? Doesn't appear to.

I found wget for Mac OS X Leopard on Quentin Stafford-Fraser's blog and followed the installation instructions in the readme file.

The readme file says you will need to add the folder where you put wget to the PATH, so I googled and found these instructions on how to do that:

• Create the file /etc/paths.d/wget like this: sudo touch /etc/paths.d/wget
• Edit the file: sudo nano /etc/paths.d/wget
• Put the path inside the file: /usr/local/bin

Then I looked up the http address of my Google Calendar iCal file by going to Calendar Settings and clicking on the iCal button in the Private Address area near the bottom.

Then I went to Wget manual at gnu.org and figured out the syntax for what I wanted to do:

wget -P /Users/myusername/Documents/Google_Calendar_Backups http://www.google.com/calendar/ical/mygoogleusername@gmail.com/private-randomlettersandnumbers/basic.ics

And then I tested it in a bash script, and when I verified it worked I added it to my regular nightly backup bash script.

Finally found a way to create a browseable archive of Thunderbird folders

For literally years I have been looking for a way to create an archive of a Thunderbird folder that is easy to access without having to import it back into Thunderbird.  In other words, I want to be able to share a whole email with someone without having to print each email, etc.  I finally found a Thunderbird Add-on that allows me to highlight all the emails in a folder and then with one click output all of the emails as individual HTML files along with an index html file which lists the Subject, To, From, and Date for each email and has a link to each individual email file.


ImportExportTools (MboxImport enhanced)

I have probably spent a total of 10-15 hours over the years looking for ways to do this, so it is quite a relief to find something that appears to work!

Where Cisco VPN Client for Mac hides profiles

My connection profile (pcf file) for my Cisco VPN Client somehow got corrupted (it magically lost my group authentication password) so I had to copy over a backup copy of my profile.  It took me a while to puzzle out where the profiles are stored on a Mac, but its:

/etc/CiscoSystemsVPNClient/Profiles