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.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 79 Comment(s) on Shipment Tracking Add-In for Excel:

  1. Anand M. Bohra writes:

    Hi I downloaded this file 4-5 times from different different PC but every time it says unable to read file.

  2. Where are you getting this error message?

  3. I am getting the error also. When loading the Add-in is when the error happens. I am using Excel 2008.

  4. I zipped the XLA file and uploaded it. I tested it and it should work now. I don't know why I can't just link to a standalone XLA file :?

  5. Worked. Installed and ran a UPS tracking number on it.

  6. Great news! Does it work well? Any comments so far?

  7. Anand M. Bohra writes:

    thanks buddy now zip xla work great

  8. JP,

    This is a great function! Can I get the password so I can add some more features to your code?

  9. I uploaded the source code.

  10. This is great! Would it be possible to incorporate this into an Access database, so that when I'm looking at a shipment and its invoice I can see its status?

    Thanks so much!

    Marc

  11. Marc,
    The function takes a tracking number and carrier name as arguments, and returns shipping status as a string. If you have a form already set up, I can't imagine it would be difficult to tie the macro to write to a text form field, or have it run on demand when a tracking number is entered into a textbox on a form (or when a button on the form is clicked).

    You could also try some of the techniques found here:
    http://support.microsoft.com/kb/q177760/

    If you need help with the specific implementation, write back.

  12. JP,

    Great add-in! As you mentioned though it's volatile. I've got a spreadsheet that I export our UPS shipment data to at the end of everyday. Close to 5500 shipments for Jun-Dec '08. So you can imagine the creep I slow to when it's calculating.

    I was wondering is there a way you could make it not run until I click a button?

    Or perhaps once it's retrieved a "Delivered" value it can auto-delete the formula and leave the value.

    Our volume and manpower does not allow us time to sit and watch our return packages. What I'm trying to accomplish is an easy way to see which packages we need to go looking for. I'm using your add-in to tell me a label has not been used yet, or delivered.

    The add-in is working great, but our data grows daily and I'm looking to automate things as much as possible. Thanks in advance.

  13. Mike,

    Have you tried the batch version? It will process tracking numbers in bulk. Check out this post:

    http://www.jpsoftwaretech.com/shipment-tracking-userform-for-batch-processing/

    Just put the add-in in the same folder as the ShipTrack.xla file, and install it the usual way. Let me know if that works for you. Unfortunately you still have to wait for each tracking number to be looked up and returned to the function — no way around that. Usually I just go for coffee, come back and it's done :)

  14. I get "Run-time error '53': File not found" as soon as I hit 'Ok' to adding the UI and anytime I launch Excel thereafter. Unchecking the UI add-in stops the error. This occurs in both Excel '03 and '07. First I install the ShipTrak add-in and it completes successfully in '03 and '07. I then exit Excel and comeback in to make sure it's still there and it is. Then I install ShipTrak UI and the error occurs. I also tried only having the UI checked and that produces a "Can't find project or library" error in '03. The same run-time error in '07.

    • Try removing both add-ins and temporarily moving the .xla files to another folder. Then close and restart Excel. Go to Tools » Add-Ins and click on both names, it should say "Cannot find add-in, remove from list?" Click Yes to remove each one (They should be completely gone, as if they had never been part of your copy of Excel). You may also want to check your VBIDE to make sure both xla workbooks are gone.

      Then move the add-ins back to their original folder and browse for the ShipTrack add-in and add it. Then browse for the ShipTrackUI add-in and add it next.

      I tried this with my copy of Excel (2003) and it worked. Unfortunately I don't have Excel 2007 for a test.

  15. JP,

    Great solution! Worked first time (downloaded the ZIP) If you have nothing else to do any chance you could add tracking for CONWAY freight? https://www.con-way.com/en/freight/

    Thanks!

  16. If you had a sample tracking number, I'll check it out.

  17. JP,

    I use Conway to ship a lot of packages. As you probably know XLA security is not very secure. so instead of bothering you, i just opened up the XLA file and altered it myself. I added Conway as one of the sipper options and also made some alterations to the code that searches the FedEx site. it appears that FedEx changed their tracking site over the weekend. Since the Function/Code is really yours (all i did was add some stuff) i left your name on their as the author. I don't know what is appropriate in an instance like this. If you are interested, email me and i would be happy to send you the updated XLA. If this was totally inappropriate then i apologize. I think your code is great and I was just trying to alter it for my needs. Thanks for the great code!!

    This post doesn't need to be posted on your site. I just wanted to make contact with you to thank you and offer you the alterations i made.

    Mike

    • Mike,
      It's entirely appropriate, that's why the add-in is available. I realize Excel isn't the proper development platform, if code security is a concern, and I encourage anyone out there to take the code and adapt it for their needs.

      You might have missed one of the above comments where I posted an unlocked version of the XLA, so there was no need to crack the original XLA ;)

      Feel free to email me the updated code and I'll integrate it into both of the existing XLA files.

  18. JP, awesome add-in!

    For the shipments that are in transit or have delivery exceptions, it would be very helpful to know what the scheduled delivery date is.

    How would one go about changing this so that it returns the scheduled delivery date also.

    Thanks,

    -gh

  19. You'd need to parse the result page, check if the delivery status is anything but "delivered" (according to the specifics of that vendor) and then look for the scheduled delivery date on that page, or if it's somewhere else, go there and parse it.

  20. Aaron Rehm writes:

    Any chance someone can send a link to an updated version that works with fedex still?

    Also, how hard would it be to get the delivery details as well, not just Delivered but Delivered 1-12-09 to J Smith or similar?

  21. Aaron,

    I'll be uploading an updated version that works with FedEx in the next day or two.

    I'll see what I can do about making the information the function returns a bit more meaningful.

  22. Aaron Rehm writes:

    Thanks, I would find this SO useful I would be using it every week for 20-60 packages so it would save me a ton of time.

    Thanks in advance for the assistance.

  23. Carl Hancock writes:

    I keep getting a stop in the Macro code at

    If Carrier = "ABF" Then

    Dim IESite As InternetExplorer<——–
    Set IESite = New InternetExplorer

    And is this working with FedEx yet? This is an amazing tool and I'm trying to integrate this into a huge project for my Business emphasis.

    • My mistake, I forgot to mention that you need to set a reference to the Internet Explorer Object. Go to Tools » References in Excel's VBIDE and look for Microsoft Internet Controls. If you don't see it, click Browse and go to C:\Windows\system32\shdocvw.dll. Make sure the checkbox next to the name is checked, and the add-in should work.

      ps- it should work in FedEx.

  24. Terrence writes:

    This add-in is not working for me. I am always getting the #NAME? error, and I have asked our company's IT expert to help.

    • Terrence,
      You'll need to make sure you have msxml.dll on your computer. It should be in the system32 directory. You'll also need to set a reference to the Internet Controls library, per my comment above. If neither of those work, do let me know.

  25. Awesome addin. Is there a way to also retrieve the senders information of the tracking #?

    We are trying to set up a check in spreadsheet. We currently hand write the tracking #, who the sender was, date rcvd and who rcvd it (along with what was rcdv box, envelope, etc.).

    We have scanner pdas that can scam the barcode and put the tracking number into an excel sheet. the pda is wireless, so with this function I can scan the tracking number and I get most of the info back, just need the sender info.

    Thanks.

    • Unfortunately, that information isn't displayed on the results page for most of the carriers. I assume that they assume you are the sender.

  26. JP – I'm using excel 2002. I'm trying to get the batch version working, but I'm getting same errors as Mike. Can't find libray, run time error. I followed your instructions to fix it, but still no luck. Any suggestions?

  27. Kelly,

    The add-in was updated in February to work with a few other carriers. You'll need to go to Tools » References in Excel's VBIDE and look for "Microsoft Internet Controls." If you don't see it, click Browse and go to C:\Windows\system32\shdocvw.dll (or wherever your system32 folder is). Make sure the checkbox next to the name is checked, and the add-in should work.

    Let me know if that works.

  28. Kelly writes:

    JP – I checked the Microsoft Internet Controls in VBIDE. Still no luck. Now I get run-time error 53. File not found. Thanks.

  29. Kelly,

    Are you sure both files are in the same folder, and the ShipTrack add-in is already installed before you add the UI version?

    Are you installing the add-ins by double clicking the xla file, or by going to Tools > Add-Ins and browsing for the files?

    What version of Internet Explorer do you have? Can you manually locate the file "C:\Windows\system32\shdocvw.dll"

    My only other suggestion is the same as I gave above to Mike:

    Try removing both add-ins and temporarily moving the .xla files to another folder. Then close and restart Excel. Go to Tools » Add-Ins and click on both names, it should say "Cannot find add-in, remove from list?" Click Yes to remove each one (They should be completely gone, as if they had never been part of your copy of Excel). You may also want to check your VBIDE to make sure both xla workbooks are gone.

    Then move the add-ins back to their original folder and browse for the ShipTrack add-in and add it. Then browse for the ShipTrackUI add-in and add it next.

    If that still doesn't work, I can send you the original version of ShipTrack that doesn't require any external library references. You won't be able to track ABF shipments, however.

  30. Kelly writes:

    I'm using IE 6.0. Both files are on the desktop and I am using the Tools > Add-ins menu to install the add-ins. The Ship Track add-in works fine – it's just the ShipTrackUI that is giving me problems.

    I have uninstalled and reinstalled several times using the steps you outlined. I'm not really sure what the problem could be. I'm only tracking UPS packages. Thanks again.

    Kelly

  31. I sent you an updated copy of the add-in. Turns out it's looking for ShipTrack.xla, when the filename is actually ShipTrack2003.xla. I'll fix that and post it to the site shortly.

  32. Barry writes:

    Would it be possible to create a script for DB Schenker shipments?
    Their web site is http://www.dbschenkerusa.com/

  33. Donna writes:

    I have found this packtracker.co.cc, excel add-in which tracks your shipment, very useful.

  34. Kashinath writes:

    Hi I downloaded the XLA & was not successfull in getting the results. Can some one help me out in using this addin.

  35. Gallion writes:

    I love this tool… helps me hold everyone accountable, employees and clients alike!

    But then our offices moved, I started getting the follwing message and wondered if anyone could help me figure out what is going on!

    "Delivered on DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN>

    Thanks in advance!

    • Just FYI, I'm aware of the issue with ShipTrack, and I'm still working on it. It seems that UPS changed their website, which is causing the trouble.

  36. I'd suggest using UPS Online Tools to improve the UPS functionality. You need to register for a myUPS.com account and then register for the tools. You will get detailed tracking information back as XML. This avoids having to readjust your code if the website changes its UI.

    Read all about it at:

    http://www.ups.com/e_comm_access/toolintro

    Joemsie

    • Tried that. In order to use their API, you need to build and pass a XML document, then parse the response. It's quite complicated, and too much trouble for a free Excel add-in. It's easier to just scrape the page and search the response for the package status.

  37. Hi,

    First – what a great tool – it's very helpful, and has made my life a lot easier.

    Would it be possible to modify the intransit status for fedex packages to show expected delivery data?

    Thanks,

    Matt

  38. Hi,

    thanks for the quick response – this number should work.

    935742227051 (fedex) – should be in transit until the 13th.

    Thanks,

    Matt

    • Download the unlocked version of the add-in and add the following code inside the Fedex section:

      [cclang='vb'] Else ' not delivered yet
      FindStatus = InStr(tempString, "Estimated delivery")
      lFirstFedexChar = InStr(FindStatus, tempString, "fielddata") + 12
      lLastFedexChar = InStr(lFirstFedexChar, tempString, "")
      ShipTrack = "Estimated delivery: " & Mid$(tempString, lFirstFedexChar + 1, lLastFedexChar – lFirstFedexChar – 1)[/vb]

      It should be part of the following If statement:

          ' if package was delivered, add details to the string
          If InStr(UCase$(ShipTrack), "DELIVERED") > 0 Then

      You may have to tweak some of the numbers a bit, but it should work.

  39. Syl Harris writes:

    Great tool!

    I understand that the regular DHL tracking is no longer in service, but I continue to use DHL Same Day delivery.

    Here's the tracking site:
    http://polaris.skycourier.com/SkyTracking/?nav=Tracking');

    Here's a valid ticket:
    9378114
    and my customer number:
    263489

    Could you please provide a suggestion to the correct search URL so I could track DHL Same Day deliveries?

    Thank you,
    Syl

    • I think you'll need to use IE automation for this one. You have the correct URL. If you go there and look for the input box with id="txtTicket", that is where you put the ticket number. The input box with id="txtSearch" is where you put the customer number, and the Submit button is src="/images/btn_Submit.gif". After waiting for the page to load (Do Until IE.ReadyState = 4) you can parse the result page for the delivery status.

  40. The "On Schedule" result for FedEx should really be replaced in the standard code with "On Schedule – Estimated Arrival Date ??-??-????". The Estimated Arrival Date is very important.

    "On Schedule" doesn't really tell us much.

    Other than that, this add-in is tha JIZZUNK!

    Thanks,

    WallyJ

    • Do you have a tracking number to test with? I can't write any code unless I can see a real tracking results page from the Fedex website.

      Thx

  41. Can you please include CEVA logistics
    http://etracking.cevalogistics.com/eTrackResults.aspx

  42. 16826752 CEVA

    • Try this function in the meantime. I'll fold it into the larger add-in sometime next week.

      Function TrackCEVA(trackingNumber As Long) As String
      
      Dim xml As Object
      Dim startingSpot As Long
      Dim endingSpot As Long
      
        Set xml = CreateObject("MSXML2.XMLHTTP")
      
        xml.Open "POST", "http://etracking.cevalogistics.com/eTrackResultsMulti.aspx?sv=" & trackingNumber, False
        xml.Send
      
      startingSpot = InStr(xml.responsetext, "Estimated Arrival Date:") + 92
      
      If startingSpot > 92 Then
        endingSpot = InStr(startingSpot, xml.responsetext, "</td>")
        TrackCEVA = "Estimated Arrival Date: " & Mid$(xml.responsetext, startingSpot, endingSpot - startingSpot)
      Else
        TrackCEVA = "Shipment Not Found"
      End If
      
      End Function
  43. Hello JP,

    Great add-in.
    Thanks.

    Regards, Peter.

  44. @JP: Thanks!! I'll look for a comment here when it is available, if you could leave one please… Thanks so much!!!

  45. @WallyJ: It's updated, you can download it again from the links in this post. I'll make a more formal announcement in a few days.

  46. Is it possible to provide an outline of what the code now pulls from each of the respective shipping sites?

    I also suggest that the code return where the product was shipped (city and state). This would help us identify packages that might be returned to warehouses or refused deliveries.

    Quite the life saver… thank you so much for your hard work!!

  47. @JP – YOU DA MAN!!!

    Fedex "On Schedule" packages now show the Estimated Delivery Date, which is Awesome!!

    It is thrown off is the status becomes "Delivery Exception", like if there is no one there to sign for the package.

    And UPS does not show the Estimated Delivery Date, but I think you mentioned something about working on that in another comment.

    Thanks again!!!

    WJ

  48. EDIT -

    The "In Transit" status for Fedex just did not have the Estimated Date. I added it, but noticed that for those, the "Activity" field seems to be important, as well as the "Details" field, showing that someone was unable to sign for the package.

    I tried to add the "Activity" field to the "In Transit" section I created, but am not doing something right. I may have the field name wrong.

    Ideas JP?

  49. By the way… Here is how I changed the code to account for the "In Transit" to show the estimated date of arrival as well for Fedex:

    ' if package still in transit, get estimated delivery date
    ElseIf InStr(UCase$(ShipTrack), "ON SCHEDULE") > 0 Then
    FindStatus = InStr(tempString, ">Estimated delivery")
    lFirstFedexChar = InStr(FindStatus, tempString, "fielddata") + 11
    lLastFedexChar = InStr(lFirstFedexChar, tempString, "")
    ShipTrack = "On schedule, Estimated delivery: " & Mid$(tempString, lFirstFedexChar, lLastFedexChar - lFirstFedexChar)

    ' if package still in transit, get estimated delivery date
    ElseIf InStr(UCase$(ShipTrack), "IN TRANSIT") > 0 Then
    FindStatus = InStr(tempString, ">Estimated delivery")
    lFirstFedexChar = InStr(FindStatus, tempString, "fielddata") + 11
    lLastFedexChar = InStr(lFirstFedexChar, tempString, "")
    ShipTrack = "In Transit, Estimated delivery: " & Mid$(tempString, lFirstFedexChar, lLastFedexChar - lFirstFedexChar)
    End If

    Else ' bad number, too old, something else
    ShipTrack = ERROR_MSG
    End If

  50. Works Great! If you could update it for the future to display statuses such as "In Transit – South Boston" , that would be phenonmenal. This has been a great help, thanks

  51. Thanks for this addin, it's exactly what I was looking for to track our ~8000 UPS shipments/month.

    I modified the code for my own use (looping through rows containing tracking numbers and calling ShipTrack() to print the delivery status, rather than an in-cell function).

    Unfortunately, I may need to get the results in XML and parse that, in case the UPS site layout changes. But this addin is great until then.

    Awesome work!

This article is closed to any future comments.
Excel School