ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Open Webpage and Extrat data based on list in excel (https://www.excelbanter.com/excel-programming/416624-open-webpage-extrat-data-based-list-excel.html)

TXDalessandros
Open Webpage and Extrat data based on list in excel
 
I have a need to open the following website
http://webadmin.airborne.com/ecs/tools/research.asp
submit from a list of airbill numbers in my excel file and extract the data
on the page based on the airbill results. example airbill number would be
28530849756. Then I would need to bring either the whole page results back or
certain fields which ever is easire Can you help?

ron
Open Webpage and Extrat data based on list in excel
 
On Sep 5, 2:04*pm, TXDalessandros
wrote:
I have a need to open the following websitehttp://webadmin.airborne.com/ecs/tools/research.asp
submit from a list of airbill numbers in my excel file and extract the data
on the page based on the airbill results. example airbill number would be
28530849756. Then I would need to bring either the whole page results back or
certain fields which ever is easire *Can you help?

The link didn't work for me...Ron

TXDalessandros
Open Webpage and Extrat data based on list in excel
 
It may be internal....Can you do this based on another webpage...
the basis is to access a webpage based on a list and then search a field
based on that and extract either the whole page or certain fields


"ron" wrote:

On Sep 5, 2:04 pm, TXDalessandros
wrote:
I have a need to open the following websitehttp://webadmin.airborne.com/ecs/tools/research.asp
submit from a list of airbill numbers in my excel file and extract the data
on the page based on the airbill results. example airbill number would be
28530849756. Then I would need to bring either the whole page results back or
certain fields which ever is easire Can you help?

The link didn't work for me...Ron

ron
Open Webpage and Extrat data based on list in excel
 
On Sep 5, 4:49*pm, TXDalessandros
wrote:
It may be internal....Can you do this based on another webpage...
the basis is to access a webpage based on a list and then search a field
based on that and extract either the whole page or certain fields

No, I need to see the source code behind the specific webpage in order
to see how to submit an airbill number. However, if you can figure
that part out, then you can use

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

to select, copy and paste to A1 of the activesheet, all of the webpage
that is in the webpage opened by your macro...Ron

TXDalessandros
Open Webpage and Extrat data based on list in excel
 
I think this is what you need?

<html
<head
<link rel="styleSheet" href="../CSS/windows.css" type="text/css"
<script

function GetRelatedAirbills()
{
frmAirbill.hdnAction.value = "GET_RELATED_AIRBILLS";
frmAirbill.submit();
}


function GetAirbillInfo(strAirbill)
{
frmAirbill.txtAirbill.value = strAirbill;
frmAirbill.submit();
}


</script
</head
<body bgcolor="#8D9A91" leftmargin="0" topmargin="0" marginheight="0"
marginwidth="0"
<form name="frmAirbill" method="POST"


<table border="0" cellpadding="0" cellspacing="0" width="100%"
bgcolor="#2B3948"
<tr
<td align="center" bgcolor="#CECEB7"
<table border="0" cellpadding="4" cellspacing="0" width="700"
<tr
<td colspan="3" height="20"</td
</tr
<tr
<td colspan="3" class="title"View Shipment - 28530849756</td
</tr
<tr
<td colspan="3" height="1" bgcolor="black"</td
</tr
<tr
<td colspan="3" height="20"</td
</tr
<tr
<td class="bodyboldtxt"Airbill Number</td
<td<input type="text" name="txtAirbill" value="28530849756"
size="20" maxlength="15" /</td
<td<input type="submit" value="Get Shipment Info" name="btnSubmit"
style="width:200px;" /</td
</tr
<tr
<td class="bodyboldtxt"Status</td
<td class="bodytxt"<font color="blue"Printed </font</td
<td</td
</tr
<tr
<td colspan="3" height="10"</td
</tr
</table
</td
</tr
<tr<td height="2" bgcolor="black"</td</tr
<tr
<td align="center" bgcolor="#8D9A91"
<table border="0" cellpadding="4" cellspacing="0" width="700"
<tr
<td width="280" height="10"</td
<td width="20" height="10"</td
<td width="200" height="10"</td
</tr
<tr
<td colspan="3" class="bodyboldtxt"Ship To</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Country</td
<td width="20" </td
<td class="bodytxt" valign="top"UNITED STATES</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Company Name</td
<td width="20" </td
<td class="bodytxt" valign="top"Frances Sewell</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Address</td
<td width="20" </td
<td class="bodytxt" valign="top"307 Young Street<br
/NOCONA, TX, 76255</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Attention To</td
<td width="20" </td
<td class="bodytxt" valign="top"Frances Sewell<br /940825-4134<br
/</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Receiver Notes</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Receiver ID</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Senders Information</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Sent By</td
<td width="20" </td
<td class="bodytxt" valign="top"Shelley Rynd</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Phone Number</td
<td width="20" </td
<td class="bodytxt" valign="top"817-560-4177</td
</tr

<tr
<td class="bodytxt" valign="top" align="right"Sender Name</td
<td width="20" </td
<td class="bodytxt" valign="top"CARBURANTE COMPANIES</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Address</td
<td width="20" </td
<td class="bodytxt" valign="top"5810 SAN BERNARDO AVE</td
</tr

<tr
<td class="bodytxt" valign="top" align="right"</td
<td width="20" </td
<td class="bodytxt" valign="top"LAREDO,TX 78041</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"User ID</td
<td width="20" </td
<td class="bodytxt" valign="top"API_806078504_670297718</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Sender's Account
Number</td
<td width="20" </td
<td class="bodytxt" valign="top"806078504</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Sender's Account
Type</td
<td width="20" </td
<td class="bodytxt" valign="top"N</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Out of Origin ? </td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr

<!-- <tr
<td class="bodytxt" valign="top" align="right"Shipment Status</td
<td width="20" </td
<td class="bodytxt" valign="top" bgcolor="yellow" P</td
</tr --

<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Shipment Information</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Shipment Date</td
<td width="20" </td
<td class="bodytxt" valign="top"8/11/2008</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Service Type</td
<td width="20" </td
<td class="bodytxt" valign="top"Next Day 12:00</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Rate Scale ID / IDC</td
<td width="20" </td
<td class="bodytxt" valign="top" </td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Scale Type / Region</td
<td width="20" </td
<td class="bodytxt" valign="top" </td
</tr
<!--<tr
<td class="bodytxt" valign="top" align="right"Shipment Type</td
<td width="20" </td
<td class="bodytxt" valign="top" bgcolor="yellow" DHL Express
Envelope (max 8 oz.)</td
</tr --
<tr
<td class="bodytxt" valign="top" align="right"Shipment Type /
Weight</td
<td width="20" </td
<td class="bodytxt" valign="top"DHL Express Envelope (max 8
oz.) / 1</td
</tr


<tr
<td class="bodytxt" valign="top" align="right"Dimensions</td
<td width="20" </td
<td class="bodytxt" valign="top"0 x 0 x 0</td
</tr



<tr
<td class="bodytxt" valign="top" align="right"Additional
Protection</td
<td width="20" </td
<td class="bodytxt" valign="top"Not Required</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Protected Value</td
<td width="20" </td
<td class="bodytxt" valign="top"0</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Description</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Shipment Reference</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Pickup Confirmation
Number</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Is Rate Suppressed?</td
<td width="20" </td
<td class="bodytxt" valign="top"Yes</td
</tr


<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Billing Notification</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Bill To</td
<td width="20" </td
<td class="bodytxt" valign="top"<bSender / 806078504</b </td

</tr



<tr
<td class="bodytxt" valign="top" align="right"Billing System</td
<td width="20" </td
<td class="bodytxt" valign="top"FOCUS </td
</tr

<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Notification</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Notify Receiver</td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Notify Other</td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Other Email Address</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Notification
Message</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr

<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Options and Special Services</td
</tr

<tr
<td class="bodytxt" valign="top" align="right"COD Value</td
<td width="20" </td
<td class="bodytxt" valign="top"$0.00</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Hazardous Materials</td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Hold at Airborne</td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr


<tr
<td class="bodytxt" valign="top" align="right"Sender's Receipt</td
<td width="20" </td
<td class="bodytxt" valign="top"No</td
</tr


<tr<td colspan="3" height="1" bgcolor="black"</td</tr
<tr
<td class="bodytxt" valign="top" align="right"Estimated Freight
Charges</td
<td width="20" </td
<td class="bodytxt" valign="top"$0</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Estimated Other
Charges</td
<td width="20" </td
<td class="bodytxt" valign="top"$0.00</td
</tr
<tr<td colspan="3" height="1" bgcolor="black"</td</tr
<tr
<td class="bodytxt" valign="top" align="right"Estimated Total</td
<td width="20" </td
<td class="bodytxt" valign="top"$0.00</td
</tr
<tr<td colspan="3" </td</tr

<tr
<td colspan="3" class="bodyboldtxt"Shipment Batch Information</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Focus Transmission
Date</td
<td width="20" </td
<td class="bodytxt" valign="top"8/11/2008 7:30:08 PM</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Invoice Number
(Master Airbill Number)</td
<td width="20" </td
<td class="bodytxt" valign="top"</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Number of Airbills in
Invoice Number</td
<td width="20" </td
<td class="bodytxt" valign="top"6</td
</tr
<tr
<td class="bodytxt" valign="top" align="right"Batch Transmission
Status</td
<td width="20" </td
<td class="bodytxt" valign="top"Confirmation Received from Focus</td
</tr
<tr<td colspan="3" </td</tr
<tr<td colspan="3" </td</tr

</table
</td
</tr
</table

<input type="hidden" name="hdnAction" value="" /
</form
</body
</html


"ron" wrote:

On Sep 5, 4:49 pm, TXDalessandros
wrote:
It may be internal....Can you do this based on another webpage...
the basis is to access a webpage based on a list and then search a field
based on that and extract either the whole page or certain fields

No, I need to see the source code behind the specific webpage in order
to see how to submit an airbill number. However, if you can figure
that part out, then you can use

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

to select, copy and paste to A1 of the activesheet, all of the webpage
that is in the webpage opened by your macro...Ron

TXDalessandros
Open Webpage and Extrat data based on list in excel
 
Also I need to loop this against potentially 3k in airbills and have the data
reside beside the airbills in columns to the right

"ron" wrote:

On Sep 5, 4:49 pm, TXDalessandros
wrote:
It may be internal....Can you do this based on another webpage...
the basis is to access a webpage based on a list and then search a field
based on that and extract either the whole page or certain fields

No, I need to see the source code behind the specific webpage in order
to see how to submit an airbill number. However, if you can figure
that part out, then you can use

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
Range("A1").Select

to select, copy and paste to A1 of the activesheet, all of the webpage
that is in the webpage opened by your macro...Ron


All times are GMT +1. The time now is 12:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com