Get Data From Web Page

**Ike** · 09-05-2014, 04:40 PM

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.

**SamT** · 09-05-2014, 06:40 PM

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.

**Ike** · 09-08-2014, 08:39 AM

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.

Exception.jpg

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>

<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>

<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>

**Ike** · 09-08-2014, 08:45 AM

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

First Attempt Made.jpg

Here is the txt file information.

I'm working on editing the txt file...be back soon.

**Ike** · 09-08-2014, 08:59 AM

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

Delivered On.jpg

Here is the txt file information.

I'm working on editing the txt file...be back soon.

**Ike** · 09-08-2014, 09:08 AM

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

Returned On.jpg

Here is the txt file information.

I'm working on editing the txt file...be back soon.

**SamT** · 09-08-2014, 09:54 AM

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>

<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>

<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

<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.

**Ike** · 09-09-2014, 09:54 AM

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>

**SamT** · 09-09-2014, 12:20 PM

@ 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/shipme...-in-for-excel/

**Ike** · 09-09-2014, 01:43 PM

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.

**SamT** · 09-09-2014, 02:18 PM

Don't give up on all of us too quick.

**Ike** · 09-10-2014, 07:29 AM

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

**SamT** · 09-24-2015, 07:15 AM

Bump to new thread title

**Ike** · 09-24-2015, 04:38 PM

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

Thread: Get Data From Web Page

Thread Tools

Display

Get Data From Web Page

Posting Permissions