Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
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 -- Gary''s Student - gsnu200858 "Ron Rosenfeld" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Sat, 20 Jun 2009 06:29:01 -0700, Gary''s Student
wrote: 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 -- Gary''s Student - gsnu200858 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Sat, 20 Jun 2009 10:31:31 -0500, "Don Guillett"
wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Sat, 20 Jun 2009 13:21:38 -0500, "Don Guillett"
wrote: For archival purposes, please post your code -- Don Guillett Microsoft MVP Excel SalesAid Software 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Wed, 24 Jun 2009 16:47:33 -0500, "Don Guillett" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Wed, 24 Jun 2009 16:47:33 -0500, "Don Guillett" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
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??? -- Don Guillett Microsoft MVP Excel SalesAid Software "Ron Rosenfeld" wrote in message ... On Wed, 24 Jun 2009 16:47:33 -0500, "Don Guillett" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unspecified Automation Error
On Wed, 1 Jul 2009 06:54:37 -0500, "Don Guillett" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"automation error" "unspecified error" | Excel Discussion (Misc queries) | |||
System Error &H80004005 (-2147467259). Unspecified error | Excel Programming | |||
Unspecified Automation Error | Excel Discussion (Misc queries) | |||
Unspecified Automation Error | Excel Discussion (Misc queries) | |||
Unspecified Automation Error | Excel Discussion (Misc queries) |