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:
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
About JP
18 Comments:
The correct link for the ShipTrackUI Add-in is:
http://www.jpsoftwaretech.com/wkbks/ShipTrackUI.xla
Thanks from Spain for share your knowledge.
Looks like I botched the URL, thank you!
I keep having a message stating that it can't find VBA project or library. Then it asks for a password. Any help?
Make sure you've installed the ShipTrack add-in first before installing this one. The error message you received should have told you that.
I do have the ShipTrack add-in installed. But I still get the same error. I have now removed the bulk add-in and am just using the ShipTrack add-in by itself. But it will only work one day and quit until I remove it and re-install it using a new spreadsheet.
I fixed the file, it should work now. Remove both add ins, then reinstall ShipTrack first and then the ShipTrack UI add-in second.
http://www.jpsoftwaretech.com/wkbks/ShipTrackUI.xla
how do i use this is excel 07?
Right now, you can't. You'd need to either wait for me to release the '07 version, or hack the XLA and create an XLAM.
Any update on the 07 version of UI? I couldn't figure out how to hack the xla.
Thanks.
There's no '07 version in the near future. The Random Data Generator tool needs work, so that is where my focus will be for now.
So I was able to get a copy of 03 and was able to do the add-ins, but when I submit the file, it says "compile error in hidden module: frmShipTrack" I don't think anybody else mentioned this error, so I wasn't able to figure it out. Also, I used the locked version of ShipTrack. For the UI, I used the link in your response to Eric. I know your working on other stuff, but since I got farther than I did last time, perhaps it's a quick fix. Thanks.
Well, I guess I should have read your post a little more carefully, I have 22 digit Fedex tracking numbers. So, how do I "pass to the function directly as an argument (not as a cell reference)?" Thanks.
The argument for the function has to be a string literal instead of a cell reference. So you cannot put the tracking number into a cell and then reference that cell, i.e.
=ShipTrack(A1)
where A1 contains the tracking number. You need to pass it to the function directly, because you can't put 22 digits into a cell in Excel (assuming the tracking number is only numeric). So you need to do this:
=ShipTrack("1234567890123456789012")
Actually, I am using a scanner to input all 22 fedex tracking number. I formatted the cell as text, so it shows. Then I am using an IF formula to extract the 12 digit airbill number used as reference for ShipTrack. IT WORKS!!!!!
Here is the detail as how I set it up
Cell B1 : 22 digit fedex number
cell C1: "fedex" (w/o quots)
cell D1 reserved for my internal tracking
cell E1 : =IF(C1="fedex",MID(B1,17,12),B1) This is the usefull tracking number
cell F1 reserved for my data
cell G1: =ShipTrack(E1,C1)
JP, thanks again, AMAZING job…
great idea!
Do you have an update for Excel 2010?
Sorry, not until I update Random Data Generator.
Hi JP,
I got run time error "53" while running ShipTrackUI.xla (date: 10/18/2010)
double clicking the xla file, it runs excel and excel asks if I want to enable the macro or not, the error window pop up after i click enable macro.
any idea?
thanks in advance!
Ken
Unfortunately at this time the UI portion is not compatible with the current ShipTrack addin. I am working on integrating them into one addin, but it will take some time. For now you will need to use the ShipTrack addin and track numbers one at a time.