View Full Version : Get Data From Web Page
Hello All,
I have attached a spreadsheet that was used to track FedEx package status, such as delivery dates and estimated delivery dates, and want I would like to convert it to UPS.
A lot of the VBA is done with regards to where the tracking information is located on the spreadsheet and where the new data is to be populated on the spreadsheet and other useful options.
Where I'm at now is, I can open the website but I'm not sure how to get the tracking number inserted into the correct location and then retrieve the data.
In the vba code of the spreadsheet I have noted areas that I would like the vba to do.
There are two examples of "If statements" that were used for the FedEx website just for reference.
I hope this is enough detail to get started.
Thank you very much in advance.
Line numbers, gosub, and the use of "a" as an incrementing variable!
This is a real old sub you are trying to update.
There are better algorithms now and anyway we need to see the actual source code for the four tracking numbers of the items you need. Save the resultant pages as "html only." Change the extension to .txt and copy the text to a post here. Do not put HTML Code tags around it. Use Quote tags or none at all.
One problem is that there is probably some characters missing after the "html" in this line
UPS.Navigate ("http://www.ups.com/tracking/tracking.html" & trackingNo) look at the actual addresses when you do the above.
Other than that, it's not too hard to see what the Procedure is doing.
Hi SamT,
It looks like the actual address is ...http://wwwapps.ups.com/WebTracking/track?loc=en_US
Do not put HTML Code tags around it. Use Quote tags or none at all.
I do not know much about HTML Code tags so I'm hoping that I'm doing this correctly and not breaking posting rules.
I would like to get the word Exception in column J and the package weight in column K.
12244
Here is the txt file information.
The following Salient Points of the source code added by SamT to really, really shorten the post.
h3 #2 or Body Header #8
<h3>
Exception
</h3>
Your shipment .... View <STRONG>Shipment Progress </STRONG>for specific details.
</div>
DL #3
<dl>
<!-- TRACKING VERSION -->
<dt><label>Scheduled Delivery:</label></dt>
<dt>Scheduled delivery information is not available at this time. Please check back later.</dt>
</dl>
DL #4
<dl>
<dt><label for="">Last Location:</label></dt>
<dd>
Def Text (with various CrLf's
</dd>
</dl>
DL #5
<dl class="outHozFixed1 clearfix">
<dt><label for="">Type:</label></dt>
<dd>Package</dd>
<dt><label for="">Weight:</label></dt>
<dd>15.00 lbs</dd>
</dl>
DL #6
dl class="outHozFlex clearfix">
<dd><label>To:</label><br>
Def Text with various CrLf's
</dd>
</dl>
DL #10
<dl>
<!-- TRACKING VERSION -->
<dt><label>Scheduled Delivery:</label></dt>
<dt>Scheduled delivery information is not available at this time. Please check back later.</dt>
</dl>
DL #11
<dl>
<dt><label for="">Last Location:</label></dt>
<dd>
Def Text with various CrLf's
</dd>
</dl>
DL #12
<dl class="outHozFixed1 clearfix">
<dt><label for="">Type:</label></dt>
<dd>Package</dd>
<dt><label for="">Weight:</label></dt>
<dd>15.00 lbs</dd>
</dl>
Next is "First Attempt Made"
I would like to get the words "First Attempt Made" in column J and the package weight in column K.
UPS tracking number 1Z91E27A9998309415
12245
Here is the txt file information.
I'm working on editing the txt file...be back soon.
Third is Delivered On:
I would like to get the the date in column J, the package weight in column K and Signed By: in column L
Date: Please don't include the day of the week. Simply the date. 07/17/2014
UPS tracking number 1Z91E27A9998309415
12246
Here is the txt file information.
I'm working on editing the txt file...be back soon.
Last is Returned On:
I would like to get the the date in column J, the package weight in column K and Signed By: in column L
Date: Please don't include the day of the week. Simply the date. 07/17/2014
UPS tracking number 11Z91E27A9994017489
12247
Here is the txt file information.
I'm working on editing the txt file...be back soon.
I have to go, but here are the salient sections of the first web page above. "Exception".
You should do the same for the rest of the pages. Just use Notepad and always search from the top for <h3> and <h (<h will return Body headers,) then for <dl>
IE maintains collections of those elements, so that is what to look for.
Then, PLEASE, edit your posts above to only show the Salient Points. I have done that for the Exceptions Post for you.
h3 #2 or Body Header #8
<h3>
Exception
</h3>
Your shipment .... View <STRONG>Shipment Progress </STRONG>for specific details.
</div>
DL #3
<dl>
<!-- TRACKING VERSION -->
<dt><label>Scheduled Delivery:</label></dt>
<dt>Scheduled delivery information is not available at this time. Please check back later.</dt>
</dl>
DL #4
<dl>
<dt><label for="">Last Location:</label></dt>
<dd>
Def Text (with various CrLf's
</dd>
</dl>
DL #5
<dl class="outHozFixed1 clearfix">
<dt><label for="">Type:</label></dt>
<dd>Package</dd>
<dt><label for="">Weight:</label></dt>
<dd>15.00 lbs</dd>
</dl>
DL #6
dl class="outHozFlex clearfix">
<dd><label>To:</label><br>
Def Text with various CrLf's
</dd>
</dl>
DL #10
<dl>
<!-- TRACKING VERSION -->
<dt><label>Scheduled Delivery:</label></dt>
<dt>Scheduled delivery information is not available at this time. Please check back later.</dt>
</dl>
DL #11
<dl>
<dt><label for="">Last Location:</label></dt>
<dd>
Def Text with various CrLf's
</dd>
</dl>
DL #12
<dl class="outHozFixed1 clearfix">
<dt><label for="">Type:</label></dt>
<dd>Package</dd>
<dt><label for="">Weight:</label></dt>
<dd>15.00 lbs</dd>
</dl>
H3 #2 or Body header # 8
<!-- cms: id="st_exc_en_us" actiontype="0" -->
<h3>
Exception
</h3>
Your shipment is currently within the UPS network; however, an unforeseen event has occurred which could result in a change to the scheduled delivery date. View <STRONG>Shipment Progress </STRONG>for specific details.
Today it looks like I can't edit the individual posts above, so I am inputting my research here.
# = the number of instances of the search
Exception
************************************
Search on "<h3>"
h3 #2
<h3> Exception </h3>
____________________________________
************************************
Search on "<h"
found nothing of relavance that wasn't in the "<h3>" search
************************************
Search on "<dl>"
found nothing of relavance
____________________________________
************************************
Search on "<dt" This is where I found the word weight but not 8.00 lbs
dl #5
<dt><label for="">Weight:</label></dt>
************************************
Search on "<dd" this is where I found the "15.00 lbs"
dl #10
<dd>Package</dd>
____________________________________
dl #11
<dd>15.00 lbs</dd>
First Attempt Made
************************************
Search on "<h3>"
h3 #2
<h3> First Attempt Made </h3>
____________________________________
************************************
Search on "<h"
found nothing of relavance that wasn't in the "<h3> search
************************************
Search on "<dl>" Note: this seach did bring up the date, however, it's surrounded by <dd> and </dd> which I seached "<dd" below
dl #4
<dl> <dt><label for="">Last Location:</label></dt> <dd> Lachine, QC, Canada, Friday, 05/09/2014 </dd></dl>
____________________________________
************************************
Search on "<dt" This is where I found the word weight but not 8.00 lbs
dt #5
<dt><label for="">Weight:</label></dt>
************************************
Search on "<dd" this is where I found the "8.00 lbs"
dd #9
<dd>Package</dd>
____________________________________
dd #10
<dd>8.00 lbs</dd>
Delivered On
************************************
Search on "<h3>" I found the word "Delivered"
h3 #2
<h3> Delivered </h3>
************************************
Search on "<h"
found nothing of relavance that wasn't in the "<h3> search
************************************
Search on "<dl>" Note: this seach did bring up the date, however, it's surrounded by <dd> and </dd> which I seached "<dd" below
dl#3
<dl> <dt><label>Delivered On:</label></dt> <dd> Thursday, 07/17/2014 at 3:40 P.M. </dd></dl>
____________________________________
dl #5
<dl> <dt><label for="">Signed By:</label></dt> <dt>JUN </dt> </dl>
************************************
Search on "<dt" This is where I found the words "Signed by" and the Initials"JUN" and the word weight but not 8.00 lbs
dt #1
<dt><label>Delivered On:</label></dt>
____________________________________
dt #3
<dt><label for="">Signed By:</label></dt>
____________________________________
dt #4
<dt>JUN </dt>
____________________________________
dt #6
<dt><label for="">Weight:</label></dt>
************************************
Search on "<dd" this is where I found the "8.00" lbs
dd #8
<dd> Thursday, 07/17/2014 at 3:40 P.M. </dd></dl>
<dl>
<dt><label for="">Left At:</label></dt>
____________________________________
dd #10
<dd>Package</dd>
____________________________________
dd #11
<dd>8.00 lbs</dd>
Returned On
************************************
Search on "<h3>"
h3 #2
<h3>
Returned to Sender
</h3>
____________________________________
************************************
Search on "<h"
found nothing of relavance that wasn't in the "<h3> search
************************************
Search on "<dl>" Note: this seach did bring up the date, however, it's surrounded by <dd> and </dd> which I seached "<dd" below
dl #3
<dl> <dt><label>Returned On:</label></dt> <dd> Friday, 07/11/2014 at 1:13 P.M. </dd></dl>
____________________________________
************************************
Search on "<dt" This is where I found the Signed by and Initials "AJIT" and the word "Weight" but not "8.00 lbs"
dt #4
<dt>AJIT </dt>
____________________________________
dt #6
<dt><label for="">Weight:</label></dt>
____________________________________
************************************
Search on ",<dd" this is where I found the "8.00 lbs"
dd #7
<dd> Friday, 07/11/2014 at 1:13 P.M. </dd>
____________________________________
dd #8
<dd>Receiver</dd>
____________________________________
dd #9
<dd>Package</dd>
____________________________________
dd #10
<dd>8.00 lbs</dd>
@ All:
I have reached the limit of my knowledge inre IE. Can you help the OP? It loopks like he has done the hard tedious parts.
@ Ike
Look at this site: http://www.jpsoftwaretech.com/shipment-tracking-add-in-for-excel/
Thanks for the link guys. I have that add-on and it works well with UPS tracking that has delivered because it gives a date, unfortunately it doesn't provide any of the shipment weights. I was hoping to convert the FedEx tracker to UPS. Thanks for looking.
Don't give up on all of us too quick.
Thank you, I will leave this thread open. :)
drLecter
09-23-2015, 02:33 PM
Ike or anyone else,
is anyone having version of this for fedex ?
I woul like to have a column full of tracking numbers and have a script or code acquire status of the shipment and paste it in the cell next to respective tracking number.
Column would have up to a 1000 tracking numbers
Hi drLecter,
Well, my job has changed since my last post and I don't need that any longer. I never did get to work and had to get the info the hard way. Good luck to you. And thanks everyone for looking. Have a good one.
Ike
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.