Broken Code Importing data from Internet Explorer

[bubububub]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
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 next to its matching UPC. The end result should be a column of UPC numbers and a column of urls.<o></o>
<o> </o>
But every time I run code, I receive the error:<o></o>

<o></o>

Run-time error '91':<o></o>
Object variable or With block variable not set<o></o>

<o></o>
<o></o>
When I choose the debug option, this line is highlighted:<o></o>

<o></o>

doc.all("upc").Value = rngUPC.Text<o></o>

<o></o>
<o></o>
Any ideas to fix my broken code?<o></o>
<o></o>
Thanks for your help,<o></o>
<o> </o>
Bub
P.S. Thanks, Norie, for helping me thus far.

Sub Test()<o:p></o:p>
Dim IE As Object<o:p></o:p>
Dim doc As Object<o:p></o:p>
Dim frm As Object<o:p></o:p>
Dim rngUPC As Range<o:p></o:p>
<o:p> </o:p>
    Set IE = CreateObject("InternetExplorer.Application")<o:p></o:p>
 <o:p></o:p>
    Set rngUPC = Range("A1")<o:p></o:p>
 <o:p></o:p>
    With IE<o:p></o:p>
 <o:p></o:p>
        While rngUPC.Value <> ""<o:p></o:p>
<o:p> </o:p>
            .Navigate "http://www.upcdatabase.com/itemform.asp"<o:p></o:p>
<o:p> </o:p>
            Do Until .ReadyState = 4: DoEvents: Loop<o:p></o:p>
 <o:p></o:p>
            Set doc = IE.Document<o:p></o:p>
 <o:p></o:p>
            Set frm = doc.forms("upcform")<o:p></o:p>
 <o:p></o:p>
            doc.all("upc").Value = rngUPC.Text<o:p></o:p>
 <o:p></o:p>
            frm.submit<o:p></o:p>
 <o:p></o:p>
            Do While IE.Busy: DoEvents: Loop<o:p></o:p>
            Do While IE.ReadyState <> 4: DoEvents: Loop<o:p></o:p>
 <o:p></o:p>
            rngUPC.Offset(, 1) = doc.url<o:p></o:p>
 <o:p></o:p>
            Set rngUPC = rngUPC.Offset(1)<o:p></o:p>
 <o:p></o:p>
        Wend<o:p></o:p>
 <o:p></o:p>
        .Quit<o:p></o:p>
<o:p> </o:p>
    End With<o:p></o:p>
 <o:p></o:p>
    Set IE = Nothing<o:p></o:p>
 <o:p></o:p>
End Sub<o:p></o:p>

<o></o>

 

[bobsan42]

well - this works fine for me.

doc.all("upc").Value = rngUPC.Text
This line fills the textbox to search by. Here is the HTML:

<p>
<form name="upcform" method="get" action="item.asp" onsubmit="**************** = 'http://www.upcdatabase.com/item/' + this.elements.upc.value; return false;">
<input type=text name=upc size=16 maxlength=13 stiscan stisubmit>
<input type=submit value=" Look Up UPC ">
</form>
</p>

if the all("upc") reference doesn't work for you - try to find the right way to address this control (textbox, control, input ... who knows )

 

[bubububub]

bobsan42,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm not very technical, so I don't understand most of what you said. However, when you said it worked fine for you, did you try a column of more than 1 number? Because it is only when that column is longer than one that I receive the error.<o></o>
<o></o>
Any specific changes to my code you might suggest?<o></o>
<o></o>
Thanks,<o></o>
Bub******** type=text/javascript> vbmenu_register("postmenu_2389518", true); *********> <o></o>
<o> </o>

 

[Norie]

Bub

Are those definitely valid UPCs?

I ran the code and it worked with no errors but every URL returned was http://www.upcdatabase.com/upcerror.asp.

That indicates a problem with the UPCs rather than the code.

One thing you could try is adding .Visible = True after With IE.

Then run through the code using F8 and flicking between IE and VBE.

That should mean that you'll see IE being opened, the search page navigated to, the number being entered and then the 'Look Up UPC' button clicked.

Mind you with all the nos above you'll end up being told the UPC is incorrect or invalid.

PS This is a response to your last post that had sample data - I'm getting a little confused as to where to post on this topic.

That's one of the reasons you shouldn't create duplicate threads but stick to the original as Joe suggested.

 

[bubububub]

Norie,

That is the point. Most of my 7500+ UPCs I expect to be invlaid. This is my way of weeding out the bad UPCs.

Sorry, I didn't read the rules prior to posting.

What does F8 do?

Thanks,
Bub

 

[Norie]

Bub

You use F8 to step through the code line by line.

That should make it possible for you to see what is happening.

As for the UPCs being invalid, that's kind of irrelevant.

Like I said I ran the code, it worked fine and gave me a result for each UPC searched for.

The result was the invalid/incorrect message but it was a result.

 

[bubububub]

Norie,

I don't understand. When I used F8 to run through it, it worked! But when I just hit run, it gives me that error. What is going on?

Very confused,
Bub

 

[Norie]

Bub

That sounds like a problem with your browser, connection, the internet the web etc

Does any part of the process seem to take longer than you would expect?

For example pages loading in the browser?

Try adding this after .Navigate.

Do While .Busy: DoEvents: Loop

That should make the code wait until the search page is loaded.

I don't know if it'll help but it's all I can think of right now.

 

[bubububub]

Norie,

I'll try that, but I'll probably end up just holding something on the F8 key and walking away.

Thank you for all your help and patience.

Cheers,

Bub

 

[John_w]

As well as Norie's suggestion, try replacing:

Set doc = IE.Document

With:

While .Document.readystate <> "complete": DoEvents: Wend
Set doc = .Document