Extracting data from imported Webpage VBA

P

pnaquin

New Member
Joined
Oct 25, 2011
Messages
4
I've been given a project and am having an issue with the following task

"Create a
macro that will go through the data in column A and extract the date of the report, the
low and high price for 1500-2000 lbs slaughter bulls, calculate the average price, and
place these values in the appropriate cells in the Cattle Prices sheet"

Basically i have one sheet named cattle price summary that contains data i've extracted from a website and I need to extra certain data from it and paste it into another sheet.

The data I need to extract is the prices 71 and then 75. The problem is this data could be in different lines in excel when the webpage is updated. How do I go about creating a macro that will search for the info directly after 1500-2000 lbs and grab the 1st price as the low price and the 2nd as the high price? We were told to use InStr, Len, Mid, or Val only do this.


<TABLE style="WIDTH: 649pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=865 border=0 x:str><COLGROUP><COL style="WIDTH: 649pt; mso-width-source: userset; mso-width-alt: 31634" width=865><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 649pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=865 height=17 x:str=" SLAUGHTER BULLS: Yield grade 1-2 1000-1500 lbs 0.00-0.00; "> SLAUGHTER BULLS: Yield grade 1-2 1000-1500 lbs 0.00-0.00; </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1500-2000 lbs 71.00-75.00.</TD></TR></TBODY></TABLE>

I hope someone can help, it was a little hard to explain without being able to attach my worksheet.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Just hoping someone on today would have an answer to my problem. Not sure if I explained it very well but basically I'm just trying to extract prices from one sheet which contains info I improted from a web site and bring it to cells in another sheet. The issue is that the location of the data I need could change cells when the info is refreshed so I need to the VBA code to tell excel what to look for i.e. the prices will always follow the same words.
 
Upvote 0
it always helps to post an example using HTML of what your talking about. Fill it in with meaningless values but the same format then post
 
Upvote 0
I really have no clue how to do that but I'll give it a shot:


xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">


****** http-equiv=Content-Type content="text/html; charset=windows-1252">
****** name=ProgId content=Excel.Sheet>
****** name=Generator content="Microsoft Excel 11">
<LINK id=Main-File href="../Acct%20Project.htm" rel=Main-File>
<LINK href="filelist.xml" rel=File-List>
<LINK href="editdata.mso" rel=Edit-Time-Data>
<LINK href="stylesheet.css" rel=Stylesheet>
<STYLE>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:.75in .7in .75in .7in;
mso-header-margin:.3in;
mso-footer-margin:.3in;}
-->
</STYLE>
******** language="JavaScript">
<!--
function fnUpdateTabs()
{
if (parent.window.g_iIEVer>=4) {
if (parent.document.readyState=="complete"
&& parent.frames['frTabs'].document.readyState=="complete")
parent.fnSetActiveSheet(6);
else
window.setTimeout("fnUpdateTabs();",150);
}
}

if (window.name!="frSheet")
****************.replace("../Acct%20Project.htm");
else
fnUpdateTabs();
//-->
*********>






<TABLE style="TABLE-LAYOUT: fixed; WIDTH: 649pt" cellSpacing=0 cellPadding=0 width=865 border=0 x:str>
<COLGROUP><COL style="WIDTH: 649pt; mso-width-source: userset; mso-width-alt: 31634" width=865></COLGROUP>

<TBODY><TR style="HEIGHT: 12.75pt" height=17>

<TD style="WIDTH: 649pt; HEIGHT: 12.75pt" width=865 height=17 p <>x:str="JK_LS140 ">JK_LS140<SPAN< p> style='mso-spacerun:yes'>*******</SPAN>
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>Jackson, MS<SPAN< p> style='mso-spacerun:yes'>*** </SPAN>Friday, October 7, 2011<SPAN< p> style='mso-spacerun:yes'>*** </SPAN>USDA-MS Dept of Ag Market News
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>SUMMARY OF 1 MISSISSIPPI CATTLE AUCTIONS
HELD IN SENATOBIA (590) THURSDAY,
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD class=xl144 style="HEIGHT: 12.75pt" align=right height=17 x:num="40822">06-Oct-11</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17>Receipts:<SPAN< p> style='mso-spacerun:yes'>* </SPAN>590***
Last Week:* 510<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>Last Year:<SPAN< p> style='mso-spacerun:yes'>* </SPAN>821
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 p <>x:str=" Compared to last Thursday, slaughter cows and bulls sold steady. "><SPAN< p> style='mso-spacerun:yes'>** </SPAN>Compared to last Thursday, slaughter cows
and bulls sold steady.*
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 p <>x:str="Feeder steers sold 4.00 to 6.00 higher and heifers sold 4.00 to 7.00 higher. ">Feeder
steers sold 4.00 to 6.00 higher and heifers sold 4.00 to 7.00 higher.<SPAN< p> style='mso-spacerun:yes'>****</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>The supply included 9 percent slaughter
cows and 2 percent pairs/replacements.
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>The feeder supply included 53 percent
steers and 47 percent heifers, with
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>an estimated 15 percent of the feeder
supply weighing over 600 lbs.
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>**
Slaughter Cows:
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17>*********
Weight (lbs)* Pct Lean<SPAN< p> style='mso-spacerun:yes'>** </SPAN>Avg Dressing<SPAN< p> style='mso-spacerun:yes'>** </SPAN>Hi Dressing
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>Breakers<SPAN< p> style='mso-spacerun:yes'>*** </SPAN>850-1200<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>70-80***
0.00 -0.00**** -----------
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>Boning<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>850-1200<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>80-85***
56.00-62.00*** 64.00-68.00
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>Lean<SPAN< p> style='mso-spacerun:yes'>******* </SPAN>850-1200<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>85-90***
0.00 -0.00**** -----------
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17>Lean<SPAN< p>
style='mso-spacerun:yes'>******* </SPAN>750-850<SPAN< p> style='mso-spacerun:yes'>****** </SPAN>85-90<SPAN< p> style='mso-spacerun:yes'>*** </SPAN>0.00 -0.00<SPAN< p> style='mso-spacerun:yes'>**** </SPAN>-----------
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17 p <>
x:str=" SLAUGHTER BULLS: Yield grade 1-2 1000-1500 lbs 0.00-0.00; "><SPAN< p>
style='mso-spacerun:yes'>** </SPAN>SLAUGHTER BULLS:<SPAN< p> style='mso-spacerun:yes'>* </SPAN>Yield grade 1-2<SPAN< p> style='mso-spacerun:yes'>* </SPAN>1000-1500 lbs 0.00-0.00;<SPAN< p> style='mso-spacerun:yes'>*</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>1500-2000 lbs 71.00-75.00.</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17 p <>
x:str=" FEEDER STEERS: Medium & Large 1-2 200-300 lbs 135.00-149.00; "><SPAN< p>
style='mso-spacerun:yes'>** </SPAN>FEEDER STEERS:<SPAN< p> style='mso-spacerun:yes'>* </SPAN>Medium & Large 1-2<SPAN< p> style='mso-spacerun:yes'>* </SPAN>200-300 lbs 135.00-149.00;<SPAN< p> style='mso-spacerun:yes'>*</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>300-400 lbs 130.00-144.00; 400-500 lbs
118.00-130.00;
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>500-600 lbs 115.00-127.00; 600-700 lbs
112.00-125.00.
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>

<TD style="HEIGHT: 12.75pt" height=17 p <>
x:str=" FEEDER HEIFERS: Medium & Large 1-2 300-400 lbs 125.00-137.00; "><SPAN< p>
style='mso-spacerun:yes'>** </SPAN>FEEDER HEIFERS:<SPAN< p> style='mso-spacerun:yes'>* </SPAN>Medium & Large 1-2<SPAN< p> style='mso-spacerun:yes'>* </SPAN>300-400 lbs 125.00-137.00;<SPAN< p> style='mso-spacerun:yes'>*</SPAN>
</TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 p <>x:str="400-500 lbs 115.00-125.00; 500-600 lbs 111.00-119.00; ">400-500 lbs
115.00-125.00; 500-600 lbs 111.00-119.00;<SPAN< p> style='mso-spacerun:yes'>*</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>600-700 lbs 100.00-112.00.</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 p <>x:str=" Cow/Calf Pairs: Medium and Large 1-2 4-8 years old, 850-1250 lbs "><SPAN< p> style='mso-spacerun:yes'>** </SPAN>Cow/Calf Pairs: Medium and Large 1-2 4-8
years old, 850-1250 lbs*
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>with 100-300 lbs calves 950.00-1250.00
per pair.
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>

<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 x:str=" Replacements: "><SPAN< p>style='mso-spacerun:yes'>** </SPAN>Replacements:<SPAN< p> style='mso-spacerun:yes'>*</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 25.5pt; mso-xlrowspan: 2" height=34>
<TD style="HEIGHT: 25.5pt" height=34></TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>Source:*
USDA-MS Dept of Ag Market News
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>********
Michael Lasseter, 601-359-1159 begin_of_the_skype_highlighting<SPAN< p> style='mso-spacerun:yes'>************* </SPAN>601-359-1159<SPAN< p> style='mso-spacerun:yes'>***** </SPAN>end_of_the_skype_highlighting, Jackson,
MS
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>********
www.ams.usda.gov/mnreports/JK_LS140.txt
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>********
For all livestock market news reports:
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>********
www.ams.usda.gov/LSMarketNews
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17></TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17 x:str="0900c ">0900c<SPAN< p> style='mso-spacerun:yes'>****</SPAN>
</TD>

</TR>
<TR style="HEIGHT: 12.75pt" height=17>
<TD style="HEIGHT: 12.75pt" height=17>*</TD>

</TR>



</TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
375
Jagadeshrao
J
S
  • Question Question
Extract Specific Data from one sheet to another
Replies
4
Views
440
JEC
JEC
A
  • Question Question
Using ADODB way t populate data under header from file to another
Replies
0
Views
67
abdo meghari
A
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
362
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,396
Messages
6,178,388
Members
452,844
Latest member
Shebl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top