CODEWITHSUNDEEP
excelvba
user1592380
user1592380

Reputation: 36247

Adding macro from one excel sheet to another

enter image description here

I'm working with a list of addresses in excel 365. I want to perform a reverse geocode function to produce addresses from the available longitude and latitude info. to do this I have found an excel addin https://github.com/gramener/geocode-excel , which I downloaded. you can see an example in the screenshot above. the formula which it introduces is:

=@NominatimReverseGeocode(B32,C32)

Now what I want to do is make this available in my main sheet so that I can use the NominatimReverseGeocode. I've gone to File - Options - File - Add Ins

enter image description here

But the function 'NominatimReverseGeocode' does not show up in my main sheet. How can I make this function available in my main sheet?

EDIT:

I renamed the file to geocode.xlam in powershell. Now I'm getting:

enter image description here

Upvotes: 1

Views: 332

Answers (2)

Absinthe
Absinthe

Reputation: 3391

If you only need this code available for yourself and not other users by far the simplest way would be to create a personal macro workbook.

Create a workbook and save it as

C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsm

Copy the code from the GeoCode workbook into PERSONAL.xlsm.

PERSONAL.xlsm will autoload with Excel. Prefix your function call with the workbook name: =PERSONAL.xlsm!@NominatimReverseGeocode(B32,C32)

Upvotes: 1

Robson
Robson

Reputation: 2032

If your intent is to make the Geocode functions available in all of your workbooks on your computer, do this:

  1. Delete all previously saved Geocode.xlsm and Geocode.xlam files.
  2. Download Geocode.xlsm.
  3. Open it.
  4. Go to File > Save As.
  5. Change the file type from Excel Macro-Enabled Workbook (*.xlsm) to Excel Add-in (*xlam).
  6. Click Save.
  7. Close all instances of Excel.
  8. Open Excel. (i.e. just the Excel application, so not opening a workbook.)
  9. You should be on the Home screen.
  10. Go to New > Blank workbook.
  11. In the ribbon go to Developer > Excel Add-ins.
  12. Check Geocode.
  13. Click OK.
  14. Close all instances of Excel.
  15. Open any Excel workbook or make a new blank workbook.
  16. Put =@NominatimReverseGeocode(51.5,-0.144) in any cell.
  17. This should resolve to the Buckingham Palace Garden.

If your intent is to just have the geocode functions in one specific workbook, let me know and I'll write up the steps for that.

Upvotes: 4

Related Questions