Aung Shine

Hi KEVIN,
Thanks for reaching out to us. To get only the parameters you mentioned, adjust the XPath expressions used to select the specific elements from the XML response. Here’s the modified code:

Option Explicit

Function ReverseGeocode(lati As Double, longi As Double) As String
    On Error GoTo ErrorHandler
    
    Dim xD As New MSXML2.DOMDocument
    Dim URL As String
    
    xD.async = False
    URL = "https://nominatim.openstreetmap.org/reverse?lat=" & CStr(lati) & _
          "&lon=" & CStr(longi)
    
    xD.Load (URL)
    
    If xD.parseError.ErrorCode <> 0 Then
        ' Handle parsing errors
        ReverseGeocode = "Error: " & xD.parseError.reason
    Else
        ' Set XPath selection language
        xD.SetProperty "SelectionLanguage", "XPath"
        
        ' Select specific elements using XPath
        Dim country As MSXML2.IXMLDOMElement
        Dim state As MSXML2.IXMLDOMElement
        Dim city As MSXML2.IXMLDOMElement
        Dim county As MSXML2.IXMLDOMElement
        Dim zipCode As MSXML2.IXMLDOMElement
        
        Set country = xD.SelectSingleNode("/reversegeocode/addressparts/country")
        Set state = xD.SelectSingleNode("/reversegeocode/addressparts/state")
        Set city = xD.SelectSingleNode("/reversegeocode/addressparts/city")
        Set county = xD.SelectSingleNode("/reversegeocode/addressparts/county")
        Set zipCode = xD.SelectSingleNode("/reversegeocode/addressparts/postcode")
        
        ' Build the result string in usual address manner
        ReverseGeocode = country.Text & ", " & state.Text & ", " & city.Text & ", " & _
                         county.Text & ", " & zipCode.Text
    End If
    Exit Function
ErrorHandler:
    ' Handle runtime errors
    ReverseGeocode = "Error: " & Err.Description
End Function

Hope this helped. Let us know your further queries.
Regards,
Aung
Team ExcelDemy

Hello,
Thanks a lot for letting us know about this process. We’ve added the new method.
Regards,
Aung
ExcelDemy

Thanks for reaching out.

You can follow method 3 of this article to accomplish your task. Go to the destination sheet and click a cell where you want to paste the names. Insert this formula:
=FILTER(Source!A2:B20, “Y”=Source!C2:C20)
Press Enter to get the results. Here, “Source” is the sheet name of your source sheet where the names are present along with Yes/No. A2:B20 is the cell range of the first and last names. Change it according to your dataset. C2:C20 is the cell range with Yes/No.

This should work. Hope this helped.
Regards,
Aung
ExcelDemy.

Thank you Michael for your comment. We made adjustments in the Excel file. Please find the added VBA code for the last example you mentioned.
Regards,
Aung

Thank you very much. This article shows how you can pull data from different worksheets present in a workbook. However, if you want to extract data from sheets present in different workbooks, kindly go through the article linked below. It will guide you through the complete procedure.

https://www.exceldemy.com/excel-macro-extract-data-from-multiple-excel-files/

Good luck.

Thank you very much for correcting us. We removed the reference as it’s not really necessary. You just have to color the cells E5 and E6 in blue and orange respectively. This is for the purpose of taking reference in the argument of the function we inserted in cells F5 and F6.

Thank you again.

We’re glad to know that we could help you out. In the case of the VBA method, kindly refresh the worksheet after you change the color of the cells. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

Good luck.

Thank you so much for pointing out the issue. We have updated the article. Now it’s alright. Please check.

Thanks, glad we could help you.

Thanks, LOUIS for reaching out to us. Please drop your excel file and queries at [email protected]
This is Aung, one of the writers of ExcelDemy. I’d like to help to solve your problem, but I didn’t exactly understand your criteria. I hope you elaborate a bit further.
Thanks.

Hello BRIAN,

This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. I understand you want to split the cell values. Kindly send me your excel file at [email protected]

I’ll make the necessary changes and explain them to you.

Good luck.

Thank you Roger for the beautiful comment.

Welcome WILLIAM, it’s always a pleasure to hear that we were of any help to you.
Thanks for the nice comment. Good luck.

Hello COEN,
Thanks for reaching out to us. Regarding your issue, select the range A1:I30 of Sheet1 first. Then, go to Sheet2. Press the Ctrl key, and select the range A1:I20 simultaneously. Thus, you’ll have selected your desired ranges from both sheets. Now, follow the steps in method 3. In this way, you’ll have your required email body.
Good luck.

Thank you Hendry for reaching out.
I’ve looked into your matter. You can easily carry out the mentioned operation using the 5th method in this article i.e. VBA method.
Kindly try with VBA and if you still can’t get the job done, feel free to email me. My mail: [email protected]
Good luck.

Welcome SHAUL. It’s always a pleasure to hear that we were of any help to you.
Good luck.