Hello,
I have a large column of UPC numbers I wish to identify. This code is designed to enter a search for UPCs at http://www.upcdatabase.com/itemform.asp. Then it copies the url of the search results page and pastes it in Excel 2007 next to its matching UPC. The end result should be a column of UPC numbers and a column of urls.
But every time I run code, I receive the error:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
I have a large column of UPC numbers I wish to identify. This code is designed to enter a search for UPCs at http://www.upcdatabase.com/itemform.asp. Then it copies the url of the search results page and pastes it in Excel 2007 next to its matching UPC. The end result should be a column of UPC numbers and a column of urls.
But every time I run code, I receive the error:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com




<o></o
>
<TABLE class=MsoNormalTable style="WIDTH: 100%; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt; mso-cellspacing: 0in; mso-yfti-tbllook: 1184" cellSpacing=0 cellPadding=0 width="100%" border=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8 1pt inset; PADDING-RIGHT: 4.5pt; BORDER-TOP: #ece9d8 1pt inset; PADDING-LEFT: 4.5pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 4.5pt; BORDER-LEFT: #ece9d8 1pt inset; PADDING-TOP: 4.5pt; BORDER-BOTTOM: #ece9d8 1pt inset"></TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8 1pt inset; PADDING-RIGHT: 4.5pt; BORDER-TOP: #ece9d8 1pt inset; PADDING-LEFT: 4.5pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 4.5pt; BORDER-LEFT: #ece9d8 1pt inset; PADDING-TOP: 4.5pt; BORDER-BOTTOM: #ece9d8 1pt inset">Run-time error '91':
Object variable or With block variable not set <o></o
>
</TD></TR></TBODY></TABLE>[/Quote]
When I choose the debug option, this line is highlighted:<o></o
>
<o></o
>
<TABLE class=MsoNormalTable style="WIDTH: 100%; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt; mso-cellspacing: 0in; mso-yfti-tbllook: 1184" cellSpacing=0 cellPadding=0 width="100%" border=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8 1pt inset; PADDING-RIGHT: 4.5pt; BORDER-TOP: #ece9d8 1pt inset; PADDING-LEFT: 4.5pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 4.5pt; BORDER-LEFT: #ece9d8 1pt inset; PADDING-TOP: 4.5pt; BORDER-BOTTOM: #ece9d8 1pt inset">doc.all("upc").Value = rngUPC.Text <o></o
>
</TD></TR></TBODY></TABLE>
Any ideas to fix my broken code?
<o></o
>
Please note: This code appears to work if the column has only 1 number. When applied to a column with many numbers, however, the error occurs.<o></o
>
Thanks for your help,
Bub
P.S. Thanks, Norie, for helping me thus far.<o></o
>
Code:<o:p></o:p>[/SIZE][/FONT][/COLOR] [COLOR=black][FONT=Courier New]Sub Test()<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New]Dim IE As Object<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New]Dim doc As Object<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New]Dim frm As Object<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New]Dim rngUPC As Range<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set IE = CreateObject("InternetExplorer.Application")<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set rngUPC = Range("A1")<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] With IE<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] While rngUPC.Value <> ""<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] .Navigate "[URL="http://www.upcdatabase.com/itemform.asp"][COLOR=blue]http://www.upcdatabase.com/itemform.asp[/COLOR][/URL]"<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Do Until .ReadyState = 4: DoEvents: Loop<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set doc = IE.Document<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set frm = doc.forms("upcform")<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] doc.all("upc").Value = rngUPC.Text<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] frm.submit<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Do While IE.Busy: DoEvents: Loop<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Do While IE.ReadyState <> 4: DoEvents: Loop<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] rngUPC.Offset(, 1) = doc.url<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set rngUPC = rngUPC.Offset(1)<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Wend<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] .Quit<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] End With<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] Set IE = Nothing<o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New] <o:p></o:p>[/FONT][/COLOR] [COLOR=black][FONT=Courier New]End Sub<o:p></o:p>[/FONT][/COLOR] [FONT=Calibri][SIZE=3]