none
request.open generates Compile error; Method or data member not found RRS feed

  • Question

  • It works on one machine, which I used to develop this spreadsheet, but fails one two others.  Guess which ones I need it to work on regularly?

    I have verified that all the libraries are selected, Microsoft xml 3.0 seeming to be the relevant one here.

    Any suggestions?

    Phil

    Friday, May 25, 2018 10:01 PM

Answers

  • Code below has been tested and works. See my comments re the API between the hash ("####") lines.

    There are 3 test subs at the bottom and they return the correct co-ordinates for the addresses that I have tested. You will need to insert valid addresses in these subs.

    Occasionally it tells me that I have exceeded my limit. Maybe getting an API Key will alleviate this.

    Function GetCoordinates(strAddress As String) As String 'Credit: https://www.myengineeringworld.net/2014/06/geocoding-using-vba-google-api.html '----------------------------------------------------------------------------------------------------- 'This function returns the latitude and longitude of a given address using the Google Geocoding API. 'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter), 'so, optional parameters such as bounds, language, region and components are NOT used. 'In case of multiple results (for example two cities sharing the same name), the function 'returns the FIRST OCCURRENCE, so be careful in the input address (tip: use the city name and the 'postal code if they are available). 'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500 'requests per day, so be careful not to exceed this limit. For more info check: 'https://developers.google.com/maps/documentation/geocoding/usage-limits 'In order to use this function you must enable the XML, v3.0 library from VBA editor: 'Go to Tools -> References -> check the Microsoft XML, v3.0. '2018 Update: In order to use this function you will now need a valid API key. 'Check the next link that guides you on how to acquire a free API key: 'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html 'Written By: Christos Samaras 'Date: 12/06/2014 'Last Updated: 24/03/2018 'E-mail: xristos.samaras@gmail.com 'Site: https://www.myengineeringworld.net '----------------------------------------------------------------------------------------------------- 'Declaring the necessary variables. Using 30 at the first two variables because it 'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll). 'Dim ApiKey As String 'Removed (Does not appear to be required) Dim Request As New XMLHTTP30 Dim Results As New DOMDocument30 Dim StatusNode As IXMLDOMNode Dim LatitudeNode As IXMLDOMNode Dim LongitudeNode As IXMLDOMNode '############################################################################## 'API des not seem to be required now. 'Set your API key in this variable. Check this link for more info: 'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html 'ApiKey = "Your API Key goes here!" 'Check that an API key has been provided. 'If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then ' GetCoordinates = "Invalid API Key" ' Exit Function 'End If '############################################################################## 'Generic error handling. On Error GoTo errorHandler 'Create the request based on Google Geocoding API. Parameters (from Google page): '- strAddress: The address that you want to geocode. '############################################################################## 'Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _ & "&address=" & strAddress & "&key=" & ApiKey, False 'Following line replaces the previous line (No ApiKey)

    DoEvents Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _ & "&address=" & strAddress & "&sensor=false", False '############################################################################## 'Send the request to the Google server. Request.send 'Read the results from the request. Results.LoadXML Request.responseText 'Get the status node value. Set StatusNode = Results.SelectSingleNode("//status") 'Based on the status node result, proceed accordingly. Select Case UCase(StatusNode.Text) Case "OK" 'The API request was successful. At least one geocode was returned. 'Get the latitdue and longitude node values of the first geocode. Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat") Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng") 'Return the coordinates as string (latitude, longitude). GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text Case "ZERO_RESULTS" 'The geocode was successful but returned no results. GetCoordinates = "The address probably not exists" Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day. GetCoordinates = "Requestor has exceeded the server limit" Case "REQUEST_DENIED" 'The API did not complete the request. GetCoordinates = "Server denied the request" Case "INVALID_REQUEST" 'The API request is empty or is malformed. GetCoordinates = "Request was empty or malformed" Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error. GetCoordinates = "Unknown error" Case Else 'Just in case... GetCoordinates = "Error" End Select 'In case of error, release the objects. errorHandler: Set StatusNode = Nothing Set LatitudeNode = Nothing Set LongitudeNode = Nothing Set Results = Nothing Set Request = Nothing End Function '------------------------------------------------------------------------------------------------------------------- 'The next two functions using the GetCoordinates function to get the latitude and the longitude of a given address. '------------------------------------------------------------------------------------------------------------------- Function GetLatitude(strAddress As String) As Double 'Declaring the necessary variable. Dim Coordinates As String 'Get the coordinates for the given address. Coordinates = GetCoordinates(strAddress) 'Return the latitude as number (double). If Coordinates <> vbNullString Then GetLatitude = CDbl(Left(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1)) Else GetLatitude = 0 End If End Function Function GetLongitude(strAddress As String) As Double 'Declaring the necessary variable. Dim Coordinates As String 'Get the coordinates for the given address. Coordinates = GetCoordinates(strAddress) 'Return the longitude as number (double). If Coordinates <> vbNullString Then GetLongitude = CDbl(Right(Coordinates, Len(Coordinates) - WorksheetFunction.Find(",", Coordinates))) Else GetLongitude = 0 End If End Function Sub GoogleCoOrdinates() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetCoordinates(strAddr) MsgBox strReturn End Sub Sub TestLatitude() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetLatitude(strAddr) MsgBox strReturn End Sub Sub TestLongitude() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetLongitude(strAddr) MsgBox strReturn End Sub


    Regards, OssieMac


    Wednesday, May 30, 2018 7:11 AM

All replies

  • The code usually stops on a line of code to identify where problem is. Does this occur and if so, please post the associated code.

    Regards, OssieMac

    Sunday, May 27, 2018 11:08 AM
  •               

    As the Subject mentions, it fails on OPEN for the command request.open in the last line of code posted below.

    Function GetCoordinates(Address As String) As String


        'In order to use this function you must enable the XML, v3.0 library from VBA editor:
        'Go to Tools -> References -> check the Microsoft XML, v3.0.

        'Written by:    Christos Samaras
        'Date:          12/06/2014
        'e-mail:        xristos.samaras@gmail.com
        'site:          http://www.myengineeringworld.net
        '-----------------------------------------------------------------------------------------------------

        'Declaring the necessary variables. Using 30 at the first two variables because it
        'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
        Dim Request         As New XMLHTTP30
        Dim Results         As New DOMDocument30
        Dim StatusNode      As IXMLDOMNode
        Dim LatitudeNode    As IXMLDOMNode
        Dim LongitudeNode   As IXMLDOMNode

        On Error GoTo errorHandler

        'Create the request based on Google Geocoding API. Parameters (from Google page):
        '- Address: The address that you want to geocode.
        '- Sensor: Indicates whether your application used a sensor to determine the user's location.
        'This parameter is no longer required.
        Request.
    Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?" _
        & "&address=" & Address & "&sensor=false", False

                
    Tuesday, May 29, 2018 3:44 PM
  • Code below has been tested and works. See my comments re the API between the hash ("####") lines.

    There are 3 test subs at the bottom and they return the correct co-ordinates for the addresses that I have tested. You will need to insert valid addresses in these subs.

    Occasionally it tells me that I have exceeded my limit. Maybe getting an API Key will alleviate this.

    Function GetCoordinates(strAddress As String) As String 'Credit: https://www.myengineeringworld.net/2014/06/geocoding-using-vba-google-api.html '----------------------------------------------------------------------------------------------------- 'This function returns the latitude and longitude of a given address using the Google Geocoding API. 'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter), 'so, optional parameters such as bounds, language, region and components are NOT used. 'In case of multiple results (for example two cities sharing the same name), the function 'returns the FIRST OCCURRENCE, so be careful in the input address (tip: use the city name and the 'postal code if they are available). 'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500 'requests per day, so be careful not to exceed this limit. For more info check: 'https://developers.google.com/maps/documentation/geocoding/usage-limits 'In order to use this function you must enable the XML, v3.0 library from VBA editor: 'Go to Tools -> References -> check the Microsoft XML, v3.0. '2018 Update: In order to use this function you will now need a valid API key. 'Check the next link that guides you on how to acquire a free API key: 'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html 'Written By: Christos Samaras 'Date: 12/06/2014 'Last Updated: 24/03/2018 'E-mail: xristos.samaras@gmail.com 'Site: https://www.myengineeringworld.net '----------------------------------------------------------------------------------------------------- 'Declaring the necessary variables. Using 30 at the first two variables because it 'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll). 'Dim ApiKey As String 'Removed (Does not appear to be required) Dim Request As New XMLHTTP30 Dim Results As New DOMDocument30 Dim StatusNode As IXMLDOMNode Dim LatitudeNode As IXMLDOMNode Dim LongitudeNode As IXMLDOMNode '############################################################################## 'API des not seem to be required now. 'Set your API key in this variable. Check this link for more info: 'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html 'ApiKey = "Your API Key goes here!" 'Check that an API key has been provided. 'If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then ' GetCoordinates = "Invalid API Key" ' Exit Function 'End If '############################################################################## 'Generic error handling. On Error GoTo errorHandler 'Create the request based on Google Geocoding API. Parameters (from Google page): '- strAddress: The address that you want to geocode. '############################################################################## 'Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _ & "&address=" & strAddress & "&key=" & ApiKey, False 'Following line replaces the previous line (No ApiKey)

    DoEvents Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _ & "&address=" & strAddress & "&sensor=false", False '############################################################################## 'Send the request to the Google server. Request.send 'Read the results from the request. Results.LoadXML Request.responseText 'Get the status node value. Set StatusNode = Results.SelectSingleNode("//status") 'Based on the status node result, proceed accordingly. Select Case UCase(StatusNode.Text) Case "OK" 'The API request was successful. At least one geocode was returned. 'Get the latitdue and longitude node values of the first geocode. Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat") Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng") 'Return the coordinates as string (latitude, longitude). GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text Case "ZERO_RESULTS" 'The geocode was successful but returned no results. GetCoordinates = "The address probably not exists" Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day. GetCoordinates = "Requestor has exceeded the server limit" Case "REQUEST_DENIED" 'The API did not complete the request. GetCoordinates = "Server denied the request" Case "INVALID_REQUEST" 'The API request is empty or is malformed. GetCoordinates = "Request was empty or malformed" Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error. GetCoordinates = "Unknown error" Case Else 'Just in case... GetCoordinates = "Error" End Select 'In case of error, release the objects. errorHandler: Set StatusNode = Nothing Set LatitudeNode = Nothing Set LongitudeNode = Nothing Set Results = Nothing Set Request = Nothing End Function '------------------------------------------------------------------------------------------------------------------- 'The next two functions using the GetCoordinates function to get the latitude and the longitude of a given address. '------------------------------------------------------------------------------------------------------------------- Function GetLatitude(strAddress As String) As Double 'Declaring the necessary variable. Dim Coordinates As String 'Get the coordinates for the given address. Coordinates = GetCoordinates(strAddress) 'Return the latitude as number (double). If Coordinates <> vbNullString Then GetLatitude = CDbl(Left(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1)) Else GetLatitude = 0 End If End Function Function GetLongitude(strAddress As String) As Double 'Declaring the necessary variable. Dim Coordinates As String 'Get the coordinates for the given address. Coordinates = GetCoordinates(strAddress) 'Return the longitude as number (double). If Coordinates <> vbNullString Then GetLongitude = CDbl(Right(Coordinates, Len(Coordinates) - WorksheetFunction.Find(",", Coordinates))) Else GetLongitude = 0 End If End Function Sub GoogleCoOrdinates() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetCoordinates(strAddr) MsgBox strReturn End Sub Sub TestLatitude() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetLatitude(strAddr) MsgBox strReturn End Sub Sub TestLongitude() Dim strAddr As String Dim strReturn As String '************************************************** strAddr = "####################" 'Insert a valid address between the double quotes '************************************************** strReturn = GetLongitude(strAddr) MsgBox strReturn End Sub


    Regards, OssieMac


    Wednesday, May 30, 2018 7:11 AM
  • Since my previous post I have been testing the code further and periodically I was also getting a similar error. I inserted the line DoEvents immediately prior to the Request.Open line and have not had the error since.

    Periodically over the years I have had to use the DoEvents to overcome periodic errors but usually after the code is performing disk related work like saving.

    I am wondering if there is some behind the scenes processing taking place with the  "New XMLHTTP30" and it is not completing prior to the Request line.

    I edited my previous post and inserted the DoEvents in the code example.

    Just for interest, I have also changed the reference to Microsoft XML, v6.0 and then edited the dimensioned variables to the following but none of this was not done until well after I inserted the DoEvents. Therefore, I don't believe it is related to the errors. I just wanted to see if the later version made any difference but if it did, then I can't detect it.

        Dim Request         As New XMLHTTP60
        Dim Results         As New DOMDocument60


    Regards, OssieMac


    • Edited by OssieMac Thursday, May 31, 2018 6:24 AM
    Thursday, May 31, 2018 6:23 AM
  • Hi Phil,

    Haven't heard from you and wondering if your problem has been resolved.


    Regards, OssieMac

    Sunday, June 10, 2018 6:47 AM
  • Sorry, priority project and a couple fires have prevented me from getting back to that particular project.  I should hit it in a the couple of days.  I will let you know.

    Thanx

    Phil

    Monday, June 11, 2018 3:33 PM
  • OK, so that seems to work.  I tried to add DoEvents to my existing code, and that did not do it.  What is it about what you gave me that is different?  Why does yours work on all machines and mine only one some?

    Thank you

     
    Tuesday, June 12, 2018 10:00 PM
  • What is it about what you gave me that is different?  Why does yours work on all machines and mine only one some?

    Can't say for sure. Are you sure that the code is identical in all of the machines? I note that you used http and the example code that I obtained had https. (I believe that I obtained the code from the same place that you did.)

    Also the variable for the Address I used strAddress because Address is really a reserved word and is used in the line of code to identify the parameter for the address.


    Regards, OssieMac

    Wednesday, June 13, 2018 11:18 AM
  • Same exact spreadsheet on a networked drive.  Works on the development machine, fails on either of the two suitable production machines. 

    Yes, your source was the same, although I hacked it up a bit.  Bottom line is it works, and I appreciate your help.

    Thank You

    Phil

    Wednesday, June 13, 2018 3:52 PM