0

Ok, so I'm trying to make a complex geocoding script in VBA. I have written the following code and for some reason it returns an error ("Run-time error 91: Object variable or With block variable not set"). An example of a link that I use can be: "https://maps.googleapis.com/maps/api/geocode/xml?address=1+Infinite+Loop,+Cupertino,+Santa+Clara,+California+95014&sensor=false".

Sub readXML(link As String)
Dim odc As DOMDocument
Dim lat As IXMLDOMElement
Dim lng As IXMLDOMElement

Set odc = New MSXML2.DOMDocument
odc.async = False
odc.Load (link)

lat = odc.SelectSingleNode("GeocodeResponse/result/geometry[location_type='ROOFTOP']/location/lat").Text
lng = odc.SelectSingleNode("GeocodeResponse/result/geometry[location_type='ROOFTOP']/location/lng").Text

Debug.Print lat & "; " & lng
End Sub

Can anyone tell me what I'm doing wrong?

Improve this question
1
  • 1
    Always helpful if you state exactly where your error is. Most likely one of your xpaths is not resulting in any selected node. Commented Nov 8, 2012 at 15:52

3 Answers 3

Reset to default
1

SelectSingleNode() may return Nothing.

Never call a property (like .Text) on a function if the result of that function can be Nothing.

Do something like this to avoid this error:

Dim location As IXMLDOMElement
Dim locationPath As String

locationPath = "GeocodeResponse/result/geometry[location_type='ROOFTOP']/location"
Set location = odc.SelectSingleNode(locationPath)

lat = GetTextValue(location, "./lat")
lng = GetTextValue(location, "./lng")


' ------------------------------------------------------------------------

Function GetTextValue(node As IXMLDOMElement, Optional xpath As String = "") As String
  Dim selectedNode As IXMLDOMElement

  If xpath <> "" And Not node Is Nothing Then
    Set selectedNode = node.SelectSingleNode(xpath)
  Else
    Set selectedNode = node
  End If

  If selectedNode Is Nothing Then
    GetTextValue = ""
  Else
    GetTextValue = Trim(selectedNode.Text)
  End If
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Same error, but in line "Set selectedNode = node.SelectSingleNode("./" & xpath)". Also, I got a syntax error in line "If selectedNode Is Nothing", but I added "Then" and I'm not getting that syntax error anymore.
@MartinUKPL oops, there was the Then missing. I also moved the ./. Try again.
Same error, but now on lat = GetTextValue(location, "./lat")
@MartinUKPL Impossible, especially not on this line. My code works, I've tested it. The issue must be something else.
@Brad But he tries to assign a string. See the call to .Text he uses. But you're right, that's the immediate cause of his trouble. I overlooked that, but my point stands. He uses a function result without prior check if it is Null.
|
0

Why the spaces before /location/lat, and .Text on lng but not lat?

Improve this answer

2 Comments

Sorry, I've made those mistakes while copying my code to Firefox.
Ok I'd suggesting finding the line that throws this error to explore further which and why the object isn't set. I notice you are not specifying MSXML2 on Dim as well as Set.
0

This always happens to me when I try to assign a value to an object without using set. Try this:

Set lat = odc.SelectSingleNode("GeocodeResponse/result/geometry[location_type='ROOFTOP']/location/lat").Text
Set lng = odc.SelectSingleNode("GeocodeResponse/result/geometry[location_type='ROOFTOP']/location/lng").Text
Improve this answer

2 Comments

A node's .text property returns a string, not an object.
Ahh, missed the old "need to read to the end of the line" chestnut. Whoops! Thanks for the note.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.