Code For Excel And Outlook Blog

Shipment Tracking Userform for batch processing

November 12, 2008 – 8:39 pm by JP

---

    In my previous post Shipment Tracking Add-In for Excel I introduced an add-in UDF for tracking package shipments from UPS, DHL and FedEx. A few more caveats about that function:

• It appears to only work for domestic (U.S.) shipments.
• It does work for FedEx tracking numbers, but only the 12-digit version. There is a 22-digit version of the tracking number which you need to pass to the function directly as an argument (not as a cell reference).

    If anyone would like me to include international shipments, I will certainly check out that functionality and see if it is possible. For more information in general about the function, check out the post from the link above.

    I’ve completed a userform-based add-in that uses the functionality from the ShipTrack add-in. It employs a batch feature which allows you to track packages in bulk.

    In order to use the UI, you will need to install the ShipTrack add-in from my previous post, in order for the new add-in to access the previous add-in’s functions. Here is a link to the add-in: ShipTrack Add-in.

You must install the ShipTrack add-in before installing the new UI add-in. Also, both add-ins must be in the same folder.

    And of course here is a link to the new add-in: ShipTrackUI Add-in

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

    Just like the ShipTrack add-in, you’ll need to install this one in the traditional way (go to Tools>Add-Ins and browse for the XLA file). After installing the ShipTrackUI add-in, you will see an addition to your Tools Menu: Batch Shipment Tracking. Here is a screenshot of the form that appears when you select that option:

    Click ‘Open File’ to select a file for processing. Your target worksheet must be formatted with two columns: A list of tracking numbers in column A (with header in A1) and a list of the carriers in column B (with header in B1). There cannot be any blank rows or missing data. Here is a sample:

    Click ‘Submit’ to begin processing. There is a label control on the form which updates to show you progress. After it is finished, the form looks like this:

    And your spreadsheet will be updated in column C with the information provided by the appropriate website:

    The form is very basic, so please let me know if you run into any runtime errors or other problems. Your feedback is critical!

Enjoy,
JP

 Print This Post  |   Email This Post  |   Subscribe to Posts Feed  |   Subscribe to Comments

Filed Under: AddIns, Excel
Tags: add-in, shipment tracking, ShipTrack, userform