Unspecified Automation Error

[Ron Rosenfeld]

I am trying to develop a VBA function that will do a reverse zip code lookup at
the USPS site. I have a sub that steps through a range of zip codes, and
retrieves the appropriate city or cities in that zip code.

The code works fine initially, but after a while, it returns an error on the

set IE = New InternetExplorer

line. The error is an "Unknown Automation Error" with text:
-2147467259 (80004005) Unspecified error.

Changing to late binding (at least after the error messages start) do not make
a difference.

Closing and re-opening Excel doesn't help.

The only way I can resume normal operations, that I've found so far, is to
reboot my computer.

Excel 2007
XP Pro SP3
IE8

Any help appreciated. There is code that calls this function, but I don't
think it is relevant to the error message.

============================
'*** IMPORTANT NOTE ***
'MUST SET REFERENCE (TOOLS/REFERENCES IN MAIN MENU BAR)
'TO:
' Microsoft Internet Controls
' In Excel 2007, this is called "Microsoft Browser Helpers"
Option Explicit
Private Function RevZip(sZip5 As String) As Variant
Dim IE As InternetExplorer
Const sURL As String = "http://zip4.usps.com/zip4/citytown_zip.jsp"
Dim sHTML As String
Dim sTemp() As String
Dim i As Long

' Group2 = City Group3=State IGNORE CASE
Const rePattern As String = "headers=pre>(<b>)?([^,]+),\s([^<]+)"
Dim lNumCities As Long

'next line is highlighted when automation error occurs
Set IE = New InternetExplorer
IE.Navigate sURL
IE.Visible = False
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop

IE.Document.all("zip5").Value = sZip5
IE.Document.all("Submit").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
sHTML = IE.Document.body.innerhtml
Set IE = Nothing

lNumCities = RegexCount(sHTML, rePattern)
ReDim sTemp(0 To 1, 0 To lNumCities - 1)
For i = 0 To lNumCities - 1
sTemp(0, i) = RegexMid(sHTML, rePattern, i + 1, 2)
sTemp(1, i) = RegexMid(sHTML, rePattern, i + 1, 3)
Next i
RevZip = sTemp
End Function
==============================

--ron

 

[Gary''s Student]

If the code works initially, perhaps:

1. open an instance of IE OUTSIDE the function.
2. use the function in whatever loops you have
3. close IE

This should avoid the problem because it avoids constanly opening and
closing IE

 

[Ron Rosenfeld]

If the code works initially, perhaps:

1. open an instance of IE OUTSIDE the function.
2. use the function in whatever loops you have
3. close IE

This should avoid the problem because it avoids constanly opening and
closing IE

Thanks for your suggestion, but I've discovered something else.

Each time I would run through a series of zipcodes, two new ieexplore.exe
processes would appear, and would persist. After getting up to sixteen or so,
I would start getting that message.

By adding an ie.quit line to the code, these processes would close, and,
although I have not run it enough times to be sure, I'm hoping this is the
problem and solution.

current code:

=============================
Private Function RevZip(sZip5 As String) As Variant
Dim IE As InternetExplorer
Const sURL As String = "http://zip4.usps.com/zip4/citytown_zip.jsp"
Dim sHTML As String
Dim sTemp() As String
Dim i As Long

' Group2 = City Group3=State IGNORE CASE
Const rePattern As String = "headers=pre>(<b>)?([^,]+),\s([^<]+)"
Dim lNumCities As Long

Set IE = New InternetExplorer
IE.Navigate sURL
IE.Visible = False
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop

IE.Document.all("zip5").Value = sZip5
IE.Document.all("Submit").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
sHTML = IE.Document.body.innerhtml
IE.Quit

lNumCities = RegexCount(sHTML, rePattern)
ReDim sTemp(0 To 1, 0 To lNumCities - 1)
For i = 0 To lNumCities - 1
sTemp(0, i) = RegexMid(sHTML, rePattern, i + 1, 2)
sTemp(1, i) = RegexMid(sHTML, rePattern, i + 1, 3)
Next i
RevZip = sTemp
End Function
================================
--ron

 

[Don Guillett]

How about mapquest. I wrote a file that uses it with an external query
Zip One 92307
Zip Two 78734
Result From Apple Valley, CA 92307 to Austin, TX 78734 Total Estimated
Distance: 1380.74 miles

If you want a copy send a request OFF list and I will email.

 

[Ron Rosenfeld]

How about mapquest. I wrote a file that uses it with an external query
Zip One 92307
Zip Two 78734
Result From Apple Valley, CA 92307 to Austin, TX 78734 Total Estimated
Distance: 1380.74 miles

If you want a copy send a request OFF list and I will email.

Thanks, Don. But I think I've found my problem and have things working now.

Apparently, by not explicitly QUITting IE, multiple processes get spawned and,
after a certain number of these, error messages are generated.

I looked at the mapquest site briefly. But it appears to me that entering a
zipcode only returns the major city in that zip code and, if there are multiple
cities within a zip code, the others get missed.

For example, now that it seems to be working, I get all the legitimate cities
returned. Here are some examples with multiple cities per one zip code:

Zip Code: 04667
PERRY ME
PLEASANT POINT ME
PLEASANT PT ME

Zip Code: 12345
SCHENECTADY NY
GENERAL ELECTRIC NY
SCHDY NY
--ron

 

[Don Guillett]

For archival purposes, please post your code

 

[Ron Rosenfeld]

For archival purposes, please post your code

Here's the current code.

I'm really new at doing anything in VBA with the InternetExplorer object, and I
suspect there are better ways of doing things. In particular, I have read that
the Document.All is not the best way of filling out the form, but it seems to
work OK now.

Any suggestions would be appreciated.

==============================
Function RevZip(sZip5 As String) As Variant
'returns 2D array of each city/state pair
'in the zip code
'set reference to Microsoft VBScript Regular Expressions 5.5
'Set reference to Microsoft Internet Controls
' In Excel 2007, this is called "Microsoft Browser Helpers"
' but will change to Microsoft Internet Controls after
' being set
Dim IE As InternetExplorer
Const sURL As String = "http://zip4.usps.com/zip4/citytown_zip.jsp"
Dim sHTML As String
Dim sTemp() As String
Dim i As Long

' Group2 = City Group3=State IGNORE CASE
Const rePattern As String = "headers=pre>(<b>)?([^,]+),\s([^<]+)"
Dim lNumCities As Long

Application.Cursor = xlWait
Set IE = New InternetExplorer
IE.Navigate sURL
IE.Visible = False
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop

IE.Document.all("zip5").Value = sZip5
IE.Document.all("Submit").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
sHTML = IE.Document.body.innerhtml
IE.Quit
Application.Cursor = xlDefault

'Note that the USPS site can return multiple
'cities for each zip code. So we need to
'return them all
lNumCities = RegexCount(sHTML, rePattern)
ReDim sTemp(0 To 1, 0 To lNumCities - 1)
For i = 0 To lNumCities - 1
sTemp(0, i) = RegexMid(sHTML, rePattern, i + 1, 2)
sTemp(1, i) = RegexMid(sHTML, rePattern, i + 1, 3)
Next i
RevZip = sTemp
End Function
Private Function RegexMid(s As String, sPat As String, _
Optional Index As Long = 1, _
Optional Subindex As Long, _
Optional CaseIgnore As Boolean = True, _
Optional Glbl As Boolean = True, _
Optional Multiline As Boolean = False) As String

Dim re As Object, mc As Object
Dim i As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.IgnoreCase = CaseIgnore
re.Global = Glbl
re.Multiline = Multiline


If re.Test(s) = True Then
Set mc = re.Execute(s)
If Subindex = 0 Then
RegexMid = mc(Index - 1)
ElseIf Subindex <= mc(Index - 1).SubMatches.Count Then
RegexMid = mc(Index - 1).SubMatches(Subindex - 1)
End If
End If
Set re = Nothing
End Function
Private Function RegexCount(s As String, sPat As String) As Long
Dim re As RegExp, mc As MatchCollection
Set re = New RegExp
re.Pattern = sPat
re.Global = True
re.IgnoreCase = True
Set mc = re.Execute(s)
RegexCount = mc.Count
Set re = Nothing
End Function
===========================
--ron

 

[Don Guillett]

I installed these functions in a regular module and entered on the sheet
=revzip(78731) and got Austin
I didn't get any other hits for that zip and didn't get TX


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

For archival purposes, please post your code

Here's the current code.

I'm really new at doing anything in VBA with the InternetExplorer object,
and I
suspect there are better ways of doing things. In particular, I have read
that
the Document.All is not the best way of filling out the form, but it seems
to
work OK now.

Any suggestions would be appreciated.

==============================
Function RevZip(sZip5 As String) As Variant
'returns 2D array of each city/state pair
'in the zip code
'set reference to Microsoft VBScript Regular Expressions 5.5
'Set reference to Microsoft Internet Controls
' In Excel 2007, this is called "Microsoft Browser Helpers"
' but will change to Microsoft Internet Controls after
' being set
Dim IE As InternetExplorer
Const sURL As String = "http://zip4.usps.com/zip4/citytown_zip.jsp"
Dim sHTML As String
Dim sTemp() As String
Dim i As Long

' Group2 = City Group3=State IGNORE CASE
Const rePattern As String = "headers=pre>(<b>)?([^,]+),\s([^<]+)"
Dim lNumCities As Long

Application.Cursor = xlWait
Set IE = New InternetExplorer
IE.Navigate sURL
IE.Visible = False
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop

IE.Document.all("zip5").Value = sZip5
IE.Document.all("Submit").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
Do While IE.Busy = True
DoEvents
Loop
sHTML = IE.Document.body.innerhtml
IE.Quit
Application.Cursor = xlDefault

'Note that the USPS site can return multiple
'cities for each zip code. So we need to
'return them all
lNumCities = RegexCount(sHTML, rePattern)
ReDim sTemp(0 To 1, 0 To lNumCities - 1)
For i = 0 To lNumCities - 1
sTemp(0, i) = RegexMid(sHTML, rePattern, i + 1, 2)
sTemp(1, i) = RegexMid(sHTML, rePattern, i + 1, 3)
Next i
RevZip = sTemp
End Function
Private Function RegexMid(s As String, sPat As String, _
Optional Index As Long = 1, _
Optional Subindex As Long, _
Optional CaseIgnore As Boolean = True, _
Optional Glbl As Boolean = True, _
Optional Multiline As Boolean = False) As String

Dim re As Object, mc As Object
Dim i As Long

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.IgnoreCase = CaseIgnore
re.Global = Glbl
re.Multiline = Multiline


If re.Test(s) = True Then
Set mc = re.Execute(s)
If Subindex = 0 Then
RegexMid = mc(Index - 1)
ElseIf Subindex <= mc(Index - 1).SubMatches.Count Then
RegexMid = mc(Index - 1).SubMatches(Subindex - 1)
End If
End If
Set re = Nothing
End Function
Private Function RegexCount(s As String, sPat As String) As Long
Dim re As RegExp, mc As MatchCollection
Set re = New RegExp
re.Pattern = sPat
re.Global = True
re.IgnoreCase = True
Set mc = re.Execute(s)
RegexCount = mc.Count
Set re = Nothing
End Function
===========================
--ron

 

[Ron Rosenfeld]

I installed these functions in a regular module and entered on the sheet
=revzip(78731) and got Austin
I didn't get any other hits for that zip and didn't get TX

You probably overlooked the comment at the beginning of the routine that the
function returns a 2D array of city-state pairs.

If I go to the formula bar, and select <F9>, I see:

{"AUSTIN","CAMP MABRY";"TX","TX"}

So to pull out each city-state pair using this function, you need to use the
INDEX function.

For example,

=INDEX(RevZip($A$1),ROWS($1:1),COLUMNS($A:A))

filled to the right 1 cell and down 1 cell (with 78731 in A1), returns:

AUSTIN CAMP MABRY
TX TX

in four separate cells.

This function is much more efficiently used in a Sub that would populate the
worksheet. In a Sub, you can make a single call to the UDF and then output the
resultant array appropriately.

On the other hand, if you have this formula in four worksheet cells, then there
are four calls to IE, and that will take a considerable amount of time.

--ron

 

[Ron Rosenfeld]

I installed these functions in a regular module and entered on the sheet
=revzip(78731) and got Austin
I didn't get any other hits for that zip and didn't get TX

Don,

Did you see my response to this question of yours?
Were you able to confirm it?

Thanks.

-- Ron

----------------------------
You probably overlooked the comment at the beginning of the routine that the
function returns a 2D array of city-state pairs.

If I go to the formula bar, and select <F9>, I see:

{"AUSTIN","CAMP MABRY";"TX","TX"}

So to pull out each city-state pair using this function, you need to use the
INDEX function.

For example,

=INDEX(RevZip($A$1),ROWS($1:1),COLUMNS($A:A))

filled to the right 1 cell and down 1 cell (with 78731 in A1), returns:

AUSTIN CAMP MABRY
TX TX

in four separate cells.

This function is much more efficiently used in a Sub that would populate the
worksheet. In a Sub, you can make a single call to the UDF and then output the
resultant array appropriately.

On the other hand, if you have this formula in four worksheet cells, then there
are four calls to IE, and that will take a considerable amount of time.

--ron
--ron

 

[Don Guillett]

I haven't had a lot of time to play with this. Maybe you can send me a
sample wb. However, somewhere there must be a better way???

 

[Ron Rosenfeld]

I haven't had a lot of time to play with this. Maybe you can send me a
sample wb. However, somewhere there must be a better way???

Better = fewer IE calls.

Since this was designed to be used in a Sub, one way to make it "better" would
be to purchase a zip code database (or DB subscription). But as used in a Sub,
it does make only one call per address line.

I think to be used as a "worksheet function", one would have to clearly define
the requirements in order to come up with fewer IE calls.
--ron