I have a VB script that opens an XML document and pulls some information from it.
Function fnReadXMLByTags(address As String) As String ' Part of of adapted from http://excel-macro.tutorialhorizon.com/vba-excel-read-data-from-xml-file/ Dim mainWorkBook As Workbook Dim mainWS As Worksheet Dim addressArray() Dim addressArrayLen As Integer Dim tempi As Integer Dim latitude As Double, longitude As Double Dim county As String, altAddress As String 'Let's add the addresses to look up into our array: lastRow = Cells(50000, 1).End(xlUp).row If lastRow > 1 Then addressArray() = Range(Cells(1, 1), Cells(lastRow, 1)) Else addressArray() = Range(Cells(1, 1), Cells(2, 1)) End If addressArrayLen = UBound(addressArray) - LBound(addressArray) + 1 Set mainWorkBook = ActiveWorkbook Set mainWS = Sheets("Sheet1") mainWS.Range("A:A").Clear Set oXMLFile = CreateObject("Microsoft.XMLDOM") Dim startRow As Integer, adrRow As Integer startRow = Cells(50000, 1).End(xlUp).row For k = LBound(addressArray) To UBound(addressArray) If addressArray(k, 1) = "" Then Exit For strAddress = addressArray(k, 1) ' URLEncode(Address) 'Assemble the query string strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?" strQuery = strQuery & "address=" & strAddress strQuery = strQuery & "&sensor=false" Debug.Print strQuery XMLFileName = strQuery oXMLFile.async = False oXMLFile.Load (XMLFileName) Set latitudenodes = oXMLFile.SelectNodes("/GeocodeResponse/result/geometry/location/lat/text()") Set LongitudeNodes = oXMLFile.SelectNodes("/GeocodeResponse/result/geometry/location/lng/text()") Set addressNodes = oXMLFile.SelectNodes("/GeocodeResponse/result/formatted_address/text()") Set countyNodes = oXMLFile.SelectNodes("/GeocodeResponse/result/address_component[type='administrative_area_level_2']/long_name/text()") Dim totalCoords As Integer With mainWS .Range(.Cells(startRow, 1), .Cells(startRow, 4)).Interior.ColorIndex = 40 .Range(.Cells(startRow, 1), .Cells(startRow, 4)).Borders.Value = 1 .Range("A" & startRow).Value = "Lookup Address: " & strAddress .Range("B" & startRow).Value = "Latitude" .Range("C" & startRow).Value = "Longitude" .Range("D" & startRow).Value = "Total Coordinates: " & latitudenodes.Length If latitudenodes.Length = 0 And addressNodes.Length = 0 And countyNodes.Length = 0 Then mainWS.Cells(startRow + 1, 1).Value = "No such address found for '" & strAddress & "'" End If For i = 0 To (latitudenodes.Length - 1) tempi = i latitude = latitudenodes(i).NodeValue longitude = LongitudeNodes(i).NodeValue altAddress = addressNodes(i).NodeValue county = countyNodes(i).NodeValue ' THIS IS THE LINE THAT THROWS THE ERROR!!!!!!----------- i = .Cells(40000, 1).End(xlUp).row + 1 .Range(.Cells(i, 2), .Cells(i, 3)).Borders.Value = 1 .Range("B" & i).Value = latitude .Range("C" & i).Value = longitude .Range("A" & i).Value = altAddress .Range("E" & i).Value = county i = tempi Next i lastRow = .Cells(1, 1).End(xlDown).row If lastRow = 1048576 Then lastRow = 2 End With 'End the With mainWS startRow = lastRow + 1 Next k lastRow = mainWS.UsedRange.Rows.Count For i = 2 To lastRow If IsEmpty(mainWS.Cells(i, 4)) Then mainWS.Cells(i, 4).FormulaR1C1 = "=HYPERLINK(""https://www.google.com/maps/place//@""&RC[-2]&"",""&RC[-1]&"",16z/data=!3m1!4b1!4m2!3m1!1s0x0:0x0"",""Link"")" End If Next i End Function If I have a list of addresses in column A, this macro runs fine for many of the addresses...however, after some point (seems to be when i >= 28), when trying to set county = countyNodes(i).NodeValue I get the error "Object variable or With block not set".
How come? It doesn't do this for any of the other things (latitude,longitude,altAddress). Methinks it's to do with the actual XML...
Thanks for any ideas!
Edit: Here's an XML page that, when loaded, contributes to the error: Link
There are administrative_area_level_2 tags in there, for each location. (Ugh! Now I notice that the county isn't pulling the right county..hmm)
No comments:
Post a Comment