Shipment Tracking Add-In for Excel

Have you ever wanted to incorporate shipment tracking into your spreadsheet application?

I've just completed another add-in for Excel — this one is a UDF that lets you track shipments you've sent via DHL, UPS or FedEx.

To install:

Visit Download ShipTrack and download the archive. Run the executable and ShipTrack will be installed for all versions of Excel from 2003 to 2010 present on the hosting machine.

Update 2/6/2009: The add-in has been updated to work with Conway and ABF (thanks to site visitor Mike), however the ABF tracking requires the InternetExplorer.Application Object.

Usage:

=ShipTrack(Tracking_Number,Carrier)

Where Tracking_Number is a string literal in quotes, or a cell reference which contains a UPS or DHL tracking number, and Carrier is a string literal or cell reference contains the name of the carrier (either "UPS" or "DHL" or "FedEx"). These parameters can also be entered directly into the function, instead of as a cell reference.

FedEx tracking numbers are too long to fit in a cell (they get truncated after 15 digits) so you'll need to enter them directly into the formula as such:

=ShipTrack("093974897039389749","FedEx")

The following conditions will cause an error message:

• If you (accidently) try to pass a FedEx number to the function as a cell reference;
• If you (accidently) enter blanks or leave out any of the arguments
• If you pass an unknown or misspelled carrier name;
• If the tracking number cannot be found by the carrier, for example if it is too old or invalid;
• If one of the three carriers changes their website and the functionality breaks.
• If something else happens that I didn't plan for

If you do get an error message, you'll need to check which of the above conditions occurred. You might have to manually track your package on the carrier's website and see what happens. Please contact me if you have a valid tracking number and the addin doesn't return the correct status.

The carrier name is not case sensitive, so you can use all lower case, CamelCase, aNy CaSe YoU wANT.

How it works:

The function uses a late-bound XMLHTTP object to perform a web query of the given tracking number. It is much faster than using the Shdocvw.InternetExplorer object, but there is still a slight delay because it is late-bound.

Requires MSXML 6.0 as follows: c:\windows\system32\msxml6.dll

It is volatile so it will recalculate repeatedly as the worksheet is used. I don't recommend setting calculation to manual, so you should delete the formula after using it, or you can comment it out in the worksheet by editing the cell (press F2, then Home) and pre-pending an apostrophe, so the cell would look like '=ShipTrack(A1,B1). Just remove the apostrophe to re-enter the formula.

I had a limited number of tracking numbers to use to test out the functionality, please test it out and let me know if you find any bugs, tracking numbers errors where you go to the site and the tracking number was found, etc.

I'm also planning a userform version, perhaps with a batch upload feature, so stay tuned!

[Update 11/12/2008]: I made it to Pointy Haired Dilbert's Excel Links of the Week, again! Check out Chandoo's post here. Also I should mention that DHL is ceasing U.S. domestic express service on January 30, 2009. So if you or your company is using DHL (and you didn't know by now), you probably want to start looking at other options.

And I would also like to add that FedEx tracking numbers also have a 12 digit format which will work in the formula as described for UPS and DHL. If you use that number instead, you can put it in a cell and pass the cell reference to the ShipTrack function.

[Update 2/14/2009]: I am actively seeking to build this application with more carriers; so far there are five, but I know there are more. If you would like your carrier added, please send me the carrier name, URL and some sample tracking numbers. I need multiple tracking numbers that show packages in different states of delivery, i.e. delivered, in transit, returned.