Posts by Kestrel A

  • Auto open KML file in Google Earth

    THANK YOU PIKE!!!
    You have nailed it.


    It worked flawlessly.


    I then changed the "Hidden" A2 cell to C:\Windows\Temp\Charter Quote.kml which opened a Save As dialog but once saved, it now overwrites the file and automatically launches Google Earth with the kml file when the command button is clicked.


    Bravo, I will be donating $50 to your nominated charity.

  • Auto open KML file in Google Earth

    Wow, that's a lot cleaner Pike. Many thanks for your hep with this, I'm learning a lot.


    However I still get the Save *.kml dialogue when clicking the command button.
    I did have some other VBA which opened Google Earth on click.


    Can the Save As code be Open Application?


    Code
    filepath = Application.GetSaveAsFilename(".kml", "Google Earth files (*.kml),*.kml", 1, "Save As *.kml")


    Code
    Open Application.DefaultFilePath & "quote.kml" For Output As #1

  • Auto open KML file in Google Earth

    yA is the Latitude and Longitude data.


    Both sets of code still open an Explorer dialogue box to save the file. Not the usual Save As as seen normally, but a Save *.kml


    After saving and opening the file in Google Earth the filename used in the dialogue box gets overwritten to sheet "Hidden" A2.
    Here I saved as different file name and the A2 cell is now this.


    I did post a Dropbox link to the workbook previously.


    https://www.dropbox.com/s/e6zv…%20enquiry%20v4.xltm?dl=0


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tSave .kml.png Views:\t0 Size:\t37.9 KB ID:\t1224577","data-align":"none","data-attachmentid":"1224577","data-size":"full","title":"Save .kml.png"}[/ATTACH][ATTACH=JSON]{"alt":"Click image for larger version Name:\tdifferent.png Views:\t0 Size:\t37.1 KB ID:\t1224578","data-align":"none","data-attachmentid":"1224578","data-size":"full","title":"different.png"}[/ATTACH]

  • Auto open KML file in Google Earth

    Only one Macro.
    Entire content


    When I click on the macro button Explorer Save As opens to C:\Users\Paul\Desktop with an Excel icon and Save *.kml in the top of the window.
    When I save the file in a different name "Hidden" A2 gets overwritten with the new name. After saving MsgBox opens with "File successfully exported, Open in Google Earth?"


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Save .kml.png","data-attachmentid":1224525}[/ATTACH]

  • Auto open KML file in Google Earth

    Pike,
    Still not quite there.
    I still have to Save as
    Thanks for you patience.



  • Auto open KML file in Google Earth

    Hi All,


    So I have my Excel file (Creating KML file from Excel) successfully producing a KML file complete with waypoints and linestrings.


    In Hidden A1 is "C:\Program Files\Google\Google Earth Pro\client\googleearth.exe"
    In Hidden A2 is "C:\Windows\Temp\Quote.kml


    When I run the Macro the Msg Box opens with File successfully exported, Open in Google Earth?
    When I click Yes I am prompted to save the file before Google Earth opens.


    Is there a way to modify the code to auto open (overwrite?) the file referenced in Hidden A2 value without needing to save as each time?


    EDIT: to add;


    When choosing a new path for the filename, Hidden A2 gets overwritten with the new path. I thought the idea was to nominate a path in Hidden A2.



  • Creating KML file from Excel

    Left click just refreshes th page, no workbook.


    Right click, open in new tab... blank page.


    Should it not have a download link on the end like this...

    • [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/images\/attach\/xlsm.gif"}[/IMG2] Charter enquiry.xlsm‎ (983.5 KB, 4 views) Download
    • [IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.excelforum.com\/images\/attach\/txt.gif"}[/IMG2] Sample.txt‎ (470 Bytes, 2 views) Download

  • Creating KML file from Excel

    Hi Carim,


    I found another way out of my problem with another set of code I was trying.
    http://www.vbaexpress.com/forum/show...s-(LineString)


    I finally found a solution to the 0.00,0.00 coordinates. The erroneous waypoints are still generated but they now return the start coordinate. An elegant solution, pity it took me so long.


    =IF(Quote!E25="",$D$2,IFERROR(CONCATENATE(VLOOKUP(A14,'Place Names'!$A$4:$I$6993,5,TRUE),",",VLOOKUP(A14,'Place Names'!$A$4:$I$6993,4,TRUE)),""))


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tfinally.png Views:\t0 Size:\t71.4 KB ID:\t1224345","data-align":"none","data-attachmentid":"1224345","data-size":"full","title":"finally.png"}[/ATTACH]


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tfinally2.png Views:\t0 Size:\t317.9 KB ID:\t1224346","data-align":"none","data-attachmentid":"1224346","data-size":"full","title":"finally2.png"}[/ATTACH]


    Many thanks for your interest in my post. If you do find a solution I'd still be interested in seeing it as I'd still like to learn VBA for future projects.


    Regards, Paul

  • Creating KML file from Excel

    Thanks Carim,


    I have been researching this a few weeks now. The KML file is pretty straight forward, standard "XML" syntax. My problem is I do not really understand VBA.
    When I see


    For i = 1 To Len(Str)
    c = Mid(Str, i, 1)
    b = Asc(c)
    Select Case b
    Case 32, 65 To 90, 97 To 122
    Case Else
    Str = Replace(Str, c, " ")
    End Select
    Next i
    Str = Replace(Str, " ", " ")


    My head spins.


    The code posted earlier (prior to adding the (Public Function CreateLineString) when it contained only the Public Function CreatePlacemark) was working fine and producing this


    [IMG2=JSON]{"data-align":"none","data-size":"full","src":"http:\/\/www.vbaexpress.com\/forum\/attachment.php?attachmentid=25163&d=1569556773"}[/IMG2]
    The VBA code is not looping through the Latitude & Longitude data to add it between the <coordinate> </coordinate> tags but creating separate placemarks.
    Something is not quite right in the code but I don't have enough experience to see it.


    <Placemark>
    <styleUrl>#yellowLine</styleUrl>
    <LineString>
    <extrude>0</extrude>
    <tessellate>1</tessellate>
    <altitudeMode>clampToGround</altitudeMode>
    <coordinates>
    121.4617,-30.78944
    </coordinates>
    </LineString>
    </Placemark>


    <Placemark>
    <styleUrl>#yellowLine</styleUrl>
    <LineString>
    <extrude>0</extrude>
    <tessellate>1</tessellate>
    <altitudeMode>clampToGround</altitudeMode>
    <coordinates>
    120.2206,-26.62917 126.5833,-26.12833
    </coordinates>
    </LineString>
    </Placemark>

  • Creating KML file from Excel

    Hi All,


    I am reasonably proficient in Excel but new to VBA coding and having some trouble getting my head around it all. I have an Excel spreadsheet which has some VBA to produce a KML file. The code I found online and was working fine to display waypoints. I am attempting to modify it to also display linestrings.


    The generated KML should look like this for the linestring section with several latitudes and longitudes between the coordinates tags.




    My KML file is like this, generating separate waypoints like this.



    My code. Sorry for showing all the code but I'm not sure where the error is. I added the Public Function CreateLineString myself but the error may be in another part?