ExecWB occasional runtime error

[Gino_Vernisci]

Hi all

I wrote some VBA code that navigates to a website, fills in search fields with data from a spreadsheet then takes relevent data from the website search results and places them back in the spreadsheet. The code includes the Internet Explorer "find" tool 'ExecWB 32, 0. Sometimes the code works flawlessly but sometimes I encounter the following error:

runtime error '-2147221248(80040100)'
method 'ExecWB' of object 'Iwebbrowser2' failed

The code stops and Debug flags the ExecWB 32, 0 line. No changes to the code are made, it just stops working.

I'm not so much interested in a fix to the problem as I am an explanation of why its functionality varies.

Thanks Guys
Full code below:

Sub AppActivate_Test()
Dim j As Integer
Dim fname As String
Dim lname As String
Dim i As Integer
i = Range("E2").Value
For j = 1 To i
If j / 25 = Int(j / 25) Then 'Saves every 25 items
ActiveWorkbook.Save
Application.Wait Now + TimeValue("00:00:03")
Else
End If
fname = Cells(j, 1)
lname = Cells(j, 2)
 
'Search Relationship Dbase
Set IE = CreateObject("InternetExplorer.Application")
Application.Wait Now + TimeValue("00:00:01")
IE.Visible = True
IE.Navigate "dbase URL"
Application.Wait Now + TimeValue("00:00:04")
SendKeys "{TAB}"
Application.Wait Now + TimeValue("00:00:01")
SendKeys "{TAB}"
Application.Wait Now + TimeValue("00:00:01")
SendKeys lname
Application.Wait Now + TimeValue("00:00:01")
SendKeys "{TAB}"
Application.Wait Now + TimeValue("00:00:01")
SendKeys fname
Application.Wait Now + TimeValue("00:00:01")
SendKeys "~"
Application.Wait Now + TimeValue("00:00:02")
 
[COLOR=red]IE.ExecWB 32, 0  'Find[/COLOR]
 
Application.Wait Now + TimeValue("00:00:01")
SendKeys lname + ", " + fname
Application.Wait Now + TimeValue("00:00:01")
SendKeys "~"
Application.Wait Now + TimeValue("00:00:01")

IE.ExecWB 12, 2 'Copy Selection
IE.Quit
'Paste Findings
On Error Resume Next 'Ignores "failed find" in IE
AppActivate "AllClientsProspects(1)"
Cells(j, 3).PasteSpecial
Next
End Sub

 

[Gino_Vernisci]

Sorry I failed to mention that I am running a Windows XP machine with Excel 2007

 

[Norie]

Probably because the Find method isn't available when you run the code.

That could be for any no of reasons, probably connected to the use of SendKeys.

It should actually be possible to do whatever you want without SendKeys, including getting the data and probably the Find.

 

[Gino_Vernisci]

Thanks Norie that helps a lot.

Cheers

 

[Norie]

No problem, if you want to know how it could be done without SendKeys post back.

 

[Gino_Vernisci]

If you don't mind taking the time, I would love to see how it's done. I suspect that, as is, my code is somewhat barbaric.

 

[Norie]

Can you post more details about what you are trying to do?

 

[Gino_Vernisci]

Sure thing

I have a spreadsheet of first and last names (roughly 10,000). I have to check if these names are in an online database. The online database only allows users to search one name at a time.

My macro presently copies a name, opens an IE window, navigates to the database, hits tab to move the cursor to the first and last name fields and then hits enter to run the search. After giving the page a few seconds to load, the macro uses "find" in IE to select and copy the occurrance of the name in the search results. The macro goes back to the spreadsheet and pastes next to the name it used to search. If "find" does not find a match in the search results than the macro moves on to the next name in the spreadsheet.

With the amount of waiting my macro has to do, the full list of names would take some 35 hours to complete. Also as I mentioned in the first post, my method is kind of 'tempremental'. I really just need a macro that takes the names from the spreadsheet, puts them into the web database and checks if the search returns a match.

I hope I didn't over do it with details. If you need more clarity just post. Ill check regularly.

Thanks again

 

[Norie]

Gino

There's actually an important part of the detail missing - a URL.

It really is hard to give specific advice for this sort of thing without it.

There is no generic code that you can just point at a page, each page needs to be treated differently.

I understand if you can't post a URL if it's a secure site or intranet, perhaps if you posted a link to a similar public site we can at least give you some pointers.

I'll mention the basic idea anyway:

1 Automate to webpage.

2 Grab a reference to the page's document.

3 Using various methods, eg getElementByID, enter any input on page.

4 Submit the data input to the page.

5 Wait for page to reload.

6 Use some of the methods mentioned in 3 to get data from the page, ie the results from 4.

Obviously if you have the data to lookup/input on a worksheet and you want the results on a worksheet then Excel will need to be involved at stages 3 and 6.

That's the general idea anyway.

 

[Gino_Vernisci]

I will look into this (especially the "GetElementByID"). This is good stuff Norie thanks for pointing me in the right direction.

Cheers