Can Excel command another application?

[bubububub]

Hello,

Using VBA to write an excel macro, is it possible to command another application, in my case Internet Explorer?

If so, Could you please help me design a code to complete these tasks:

Select a number in a column (from Excel 2007)
Switch to internet explorer
paste the number (The box on the website should already be selected. In case it matters, the link is http://www.upcdatabase.com/itemform.asp)
hit enter
copy the url
hit back on the browser (This should also reselect the box for entering the next number, or at least it does when I use the backspace key after clicking on the page.)
paste the url in the column next to the number
and repeat the process again with the next number in the column

Is this even possible?

Thank you immensely for your help,
Bub

 

[Norie]

Bub

What you want to do sounds possible but perhaps not in the way you describe.

The first thing you probably want to do is look into automating IE from VBA which is pretty straightforward.

A couple of questions though:

1 Do you need to use an existing instance of IE or do you need/want to use one already in existence?

2 How would you be selecting the number from Excel? Randomly? User input?

3 Do you really need to hit back on the browser? Can't you just repeat the previous steps?

4 Is it only the URL for the entered UPC you want to get?

If you can post some valid UPC I can post some code but here's something to get you started.

Sub Test()
Dim IE As Object
Dim doc As Object
Dim frm As Object
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.upcdatabase.com/itemform.asp"
        Do Until .ReadyState = 4: DoEvents: Loop
        Set doc = IE.Document
        Set frm = doc.forms("upcform")
        doc.all("upc").Value = "123456789"
        frm.submit
        Do While IE.Busy: DoEvents: Loop
        Do While IE.ReadyState <> 4: DoEvents: Loop
    End With
    MsgBox doc.url
    IE.Quit
    Set IE = Nothing
End Sub

 

[bubububub]

Thanks Norie,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Perhaps another method of attack exists. To answer your first question, I had planned to have an IE window open with the given link but only because writing a code that opened IE seemed unnecessarily complicated. <o></o>
<o></o>
Second, no, I would not be selecting a random number. Simply put, I have a column of several thousand numbers and a blank column to said column’s right. I would like to copy a number into the site, run the search, and paste the resulting url of the search results page into the blank column. Then repeat moving down the column. In case you would like a more complete explanation, though more confusing and less relevant, I have a list of over 7500 UPCs to identify. Most are unidentifiable. The search results page url of a hit is different from the url of a miss. If I am able to match each number with its resulting url, I can then do a data sort in exel and then retrieve the information I need from the hits. <o></o>
<o></o>
Third, the whole point of this backspace step is a selection issue. I am only trying to find a way to ensure that when I repeat the process, the search box is once again selected.<o></o>
<o></o>
Forth, I am concerned with the url because of reasons stated in my longer description. Please see my answer to your second question.<o></o>
<o></o>
UPC no. 012000031892 is an example of a hit.<o></o>
<o></o>
I hope you found the above information illuminating. Once again, thank you for your help. <o></o>
<o> </o>
Cheers,
Bub

Bub

What you want to do sounds possible but perhaps not in the way you describe.

The first thing you probably want to do is look into automating IE from VBA which is pretty straightforward.

A couple of questions though:

1 Do you need to use an existing instance of IE or do you need/want to use one already in existence?

2 How would you be selecting the number from Excel? Randomly? User input?

3 Do you really need to hit back on the browser? Can't you just repeat the previous steps?

4 Is it only the URL for the entered UPC you want to get?

If you can post some valid UPC I can post some code but here's something to get you started.

Sub Test()
Dim IE As Object
Dim doc As Object
Dim frm As Object
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.upcdatabase.com/itemform.asp"
        Do Until .ReadyState = 4: DoEvents: Loop
        Set doc = IE.Document
        Set frm = doc.forms("upcform")
        doc.all("upc").Value = "123456789"
        frm.submit
        Do While IE.Busy: DoEvents: Loop
        Do While IE.ReadyState <> 4: DoEvents: Loop
    End With
    MsgBox doc.url
    IE.Quit
    Set IE = Nothing
End Sub

 

[Norie]

Bub

Did you try the code I posted at all?

It automates IE, which to start with is a pretty straightforward one-liner.

It then enters a value, note enters not pastes, on the form.

Then the form is submitted and the resultant URL is displayed in a messagebox.

In the code the UPC is hardcoded but it could easily be taken from a worksheet.

Also the result of the search could be easily transferred to the worksheet next to the UPC rather than be displayed in a message box.

It would also be easy to set up a loop to go through all the UPCs on the worksheet.

I can see no need for the search box to be selected at any point and no need for backspace.

 

[bubububub]

Norie,

I did try the code you wrote, but excel went into break mode and said,
"Complie error:
Expected End Sub"

Any ideas?

Thanks,
Bub

 

[Norie]

Bub

Well it worked for me, and I just refined it a little.

Sub Test()
Dim IE As Object
Dim doc As Object
Dim frm As Object
Dim rngUPC As Range

    Set IE = CreateObject("InternetExplorer.Application")
 
    Set rngUPC = Range("A1")
 
    With IE
 
        While rngUPC.Value <> ""

            .Navigate "http://www.upcdatabase.com/itemform.asp"

            Do Until .ReadyState = 4: DoEvents: Loop
 
            Set doc = IE.Document
 
            Set frm = doc.forms("upcform")
 
            doc.all("upc").Value = rngUPC.Text
 
            frm.submit
 
            Do While IE.Busy: DoEvents: Loop
            Do While IE.ReadyState <> 4: DoEvents: Loop
 
            rngUPC.Offset(, 1) = doc.url
 
            Set rngUPC = rngUPC.Offset(1)
 
        Wend
 
        .Quit

    End With
 
    Set IE = Nothing
 
End Sub

This will work with a UPCs starting in A1.

 

[bubububub]

Norie,

I think we are almost there.

I tried the following code, which is what you gave me only set up for a loop. It filled in the first box, but then gave me the message,

Run-time error '91':
Object variable or With block variable not set

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

Can you please help me fix this?

Thanks again,
Bub

 

[Norie]

That isn't the code I gave you.

The last code I posted already had a loop in it so didn't need one added.

All I can see the loop you added doing is repeating the same code 3 times.

Where are you getting the error?

 

[bubububub]

Norie,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The error pops up in my Microsoft Visual Basic window. When I try to run your code exactly, the same error comes up, and, again, only the first url gets posted.<o></o>
<o></o>
Is your loop set to run infinitely? I was intending to make a loop that repeats for the number of UPCs I have, but I guess I don't know how to do that with the code you have written.<o></o>
<o></o>
Thank you,<o></o>
Bub<o></o>
<o></o>
<o></o>

That isn't the code I gave you.<o></o>

<o></o>
The last code I posted already had a loop in it so didn't need one added.<o></o>
<o></o>
All I can see the loop you added doing is repeating the same code 3 times.<o></o>
<o></o>
Where are you getting the error?

<o></o>
<o></o>
<o> </o>

 

[Norie]

Bub

But what line of code is the error on?

The code I posted starts in A1 and loops down until there is an empty cell.

I can't see it going into an infinite loop unless something was changed in the code or there is a problem with the data.

By the way you do realise that the URL all seem to be in this form http://www.upcdatabase.com/item/012000031892.

If all you actually want to do is create a list of hyperlinks for the UPCs then you could do it without code, perhaps using the HYPERLINK worksheet function.