Next without For error

[Kestrel]

Hi All,

I've started a new thread on a previous post as I'm now using a new approach with some more code I found online.
In this code I get a Next without For error here on the last print command.

The original code was
Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

Next
Print #1, [Hidden!A13]


Which I changed to
Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

Next
Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]

Next
Print #1, [Hidden!A13]


Which broke it :think:
Any help much appreciated.

Regards Paul


Sub ExportKML()
'Check if there is any data
yA = Sheets("Main").Cells(Rows.Count, 2).End(xlUp).Row
yB = Sheets("Main").Cells(Rows.Count, 3).End(xlUp).Row
If yA <> yB Then
a = MsgBox("Inconsistent Lat and Long data, please check the data and try again", vbCritical)
Exit Sub
End If
If yA < 2 Or yB < 2 Then
a = MsgBox("There is no Lat and Long data, please enter the data and try again", vbCritical)
Exit Sub
End If
sfilename = Application.GetSaveAsFilename(BridgeFIDNo & ".kml", _
"Google Earth files (*.kml),*.kml", 1, "Save *.kml")
If sfilename = False Then
Exit Sub
Else
Sheets("Hidden").Range("A2") = sfilename
End If
Set filepath = Sheets("Hidden").Range("A2")
' Set document name
docName = "KML Document exported from Excel"

Open filepath For Output As #1

'Write header to file
outputText = [Hidden!A4] & docName & [Hidden!A5]
Print #1, outputText

'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
For j = 2 To yA
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column
For k = 4 To ncols
Attrib = Sheets("Main").Cells(j, k)
AttribHeading = Sheets("Main").Cells(1, k)
Print #1, [Hidden!A8] & AttribHeading & [Hidden!A9] & Attrib & [Hidden!A10]


Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

Next
Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]

Next
Print #1, [Hidden!A13]

Close #1

[gmayor]

You have two loops For j = 2 To yA and For k = 4 To ncols
Each loop should end with Next (ideally Next J and Next K so you understand what the code is doing
However you have three Next commands hence the error message.
Put your additional command in the appropriate loop and remove the extra Next command.

[Kestrel]

Thanks Graham,

I placed Next J and Next K in the code, which moved the Next without For error up to the first Print command, which means I need one more Next command somewhere previous?

Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

Thanks for helping a newbie out...
Regards, Paul

[gmayor]

No you don't. You have one next too many. As you don't appear to understand the significance of the loops it is perhaps better that you leave out the J and K from the Next commands

It works like this


For j = 2 To yA
'Do stuff here for the outer loop before running the inner loop
For k = 4 To ncols
'Do stuff here for the inner loop
Next k
'Do stuff here for the outer loop after running the inner loop
Next jYou don't need an unrelated Next command between commands
e.g.

Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]
Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]
Print #1, [Hidden!A13]
would be valid, but without the worksheet it is difficult to understand what you are doing with the code - in any case none of the Print commands make reference to j or k so much depends on where you want to call those commands

[SamT]

The problem is obvious with proper text formatting of the code


'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
For j = 2 To yA
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column

For k = 4 To ncols
Attrib = Sheets("Main").Cells(j, k)
AttribHeading = Sheets("Main").Cells(1, k)
Print #1, [Hidden!A8] & AttribHeading & [Hidden!A9] & Attrib & [Hidden!A10]
Next

Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]
Next

Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]

Next
Print #1, [Hidden!A13]

[snb]

Please post the Excel file.

[Kestrel]

Please post the Excel file.

Hi snb,

The file is 1.3 Mb which exceeds the upload limit.

I've made a few changes in the last few days and the file is working as far as displaying waypoints but still no linestrings, sigh...

I think the problem is the coordinate data is not being parsed for Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15], but it works for Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

I've been struggling with this for a few weeks now and for someone who is reasonably Excel literate but VBA lacking it's frustrating.
Googling Excel to Google Earth VBA has not helped :(

Dropbox link...

https://www.dropbox.com/s/vqnx7rz28hqfbp1/Charter%20enquiry%20v5.xlsm?dl=0

Regards, Paul




'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
For j = 2 To yA
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column
For k = 4 To ncols
Attrib = Sheets("Main").Cells(j, k)
AttribHeading = Sheets("Main").Cells(1, k)
Print #1, [Hidden!A8] & AttribHeading & [Hidden!A9] & Attrib & [Hidden!A10]
Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]

Next

Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]
Print #1, [Hidden!A13]

Close #1

[Kestrel]

The problem is obvious with proper text formatting of the code


'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
For j = 2 To yA
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column

For k = 4 To ncols
Attrib = Sheets("Main").Cells(j, k)
AttribHeading = Sheets("Main").Cells(1, k)
Print #1, [Hidden!A8] & AttribHeading & [Hidden!A9] & Attrib & [Hidden!A10]
Next

Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]
Next

Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]

Next
Print #1, [Hidden!A13]


Hi Sam,

"The problem is obvious"
It might be obvious to you, but I'm very new to VBA and struggling a bit despite being fairly proficient in Excel and generally computer literate. Your reply is "not very helpful" to someone who is struggling with a new concept.

If you could suggest a fix...

Humbly yours,
Paul

[Paul_Hossler]

"The problem is obvious"

It might be obvious to you, but I'm very new to VBA and struggling a bit despite being fairly proficient in Excel and generally computer literate. Your reply is "not very helpful" to someone who is struggling with a new concept.

If you could suggest a fix...

Humbly yours,
Paul


What I believe Sam was referring to in his example was a formatting style that indents each line based on how embedded it is in the code. It helps to show which Next goes with which For, as well as the other bracketing constructs (With/End With, If/ElseIf/End If, etc.)

25174

I took your Dropbox sample and reformatted your macro and used Option Explicit which requires all variables to be explicitly Dim-ed. Some people don't like to use it and just let everything be a Variant, but I find it very helpful and helps me to prevent silly typos and other dumb mistakes


I cleared sheet Place Names to make the file uploadable here, so put them back

[SamT]

With a loop, (or a With...,) the For and the Next are in the same text column. everything inside the loop is indented 3 spaces or one Tab. Each loop is separated from the preceding and following code by a blank line.

IMO, good code should be easy to read, easy to visualize the different parts and be self explanatory. Being an expert in VBA Language will not necessarily make one a good coder by those standards. ie, the greatest expert on the VBA Language here, is, IMO, a terrible coder, even though I study his code all the time. It can take me hours of research to understand his code:) But I really get to know thwe language better for doing so.

To meet my own standards, I usually spend more time proofreading and copy editing than I do actually coding.

To assist you in finding errors, Go to the VBA menu>> Tools >> Options >> Editor tab, and check everything in the Code Settings box.

That will put "Option Explicit" at the top of new code pages. In the meantime, you can manually insert it at the top of any existing code pages.

Here is one example of your code written, (mostly,) in my style.

Option Explicit

Sub ExportKML()
Dim LastRowB As Long
Dim LastRowC As Long
Dim nCols As Long
Dim vFileName As Variant
Dim rngFilepath As Range
Dim sOutputText As String
Dim docName As String

'I don't know the desriptive names for these ranges/values/
'You can use VBA Menu >> Edit >> Replace, (or CTRL+H,) to change all instances of a word in the code at once.
'Using Variables, instead of referencing the sheet many times is faster
Dim sHiddenA4 As String
Dim sHiddenA5 As String
Dim sHiddenA6 As String
Dim sHiddenA7 As String
Dim sHiddenA8 As String
Dim sHiddenA9 As String
Dim sHiddenA10 As String
Dim sHiddenA11 As String
Dim sHiddenA12 As String
Dim sHiddenA13 As String
Dim sHiddenA14 As String
Dim sHiddenA15 As String

LastRowB = Sheets("Main").Cells(Rows.Count, "B").End(xlUp).Row
LastRowC = Sheets("Main").Cells(Rows.Count, "C").End(xlUp).Row

'Set document name
docName = "KML Document exported from Excel"

'Check if there is any data
If LastRowB < 2 Or LastRowC < 2 Then
MsgBox "There is no Lat and Long data, please enter the data and try again", vbCritical
Exit Sub
End If

'Check if lat and Long Data matches
If LastRowB <> LastRowC Then
MsgBox "Inconsistent Lat and Long data, please check the data and try again", vbCritical
Exit Sub
End If

'Get File Name and path info
vFileName = Application.GetSaveAsFilename(BridgeFIDNo & ".kml", _
"Google Earth files (*.kml),*.kml", 1, "Save *.kml")
If vFileName = False Then
Exit Sub
End If

Open vFileName For Output As #1

'Assign values on sheet Hidden to Variables
With Sheets("Hidden").Columns("A") 'Faster than referencing the sheet each time
sHiddenA4 = .Cells(4)
sHiddenA5 = .Cells(5)
sHiddenA6 = .Cells(6)
sHiddenA7 = .Cells(7)
sHiddenA8 = .Cells(8)
sHiddenA9 = .Cells(9)
sHiddenA10 = .Cells(10)
sHiddenA11 = .Cells(11)
sHiddenA12 = .Cells(12)
sHiddenA13 = .Cells(13)
sHiddenA14 = .Cells(14)
sHiddenA15 = .Cells(15)
End With

'Write header to file
sOutputText = sHiddenA4 & docName & sHiddenA5
Print #1, sOutputText

'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
With Sheets("Main") 'Faster than many calls to the sheet
For j = 2 To LastRowB
Latitude = .Cells(j, "B")
Longitude = .Cells(j, "C")
ptName = .Cells(j, "A") '<----------------------------------------------- Neither Declared nor used later

Print #1, sHiddenA6 & ptName & sHiddenA7
'read no of attributes (max 10 supported)
nCols = .Cells(j, 15).End(xlToLeft).Column

For k = 4 To nCols
Attrib = .Cells(j, k)
AttribHeading = .Cells(1, k)
Print #1, sHiddenA8 & AttribHeading & sHiddenA9 & Attrib & sHiddenA10
Next k
Print #1, sHiddenA11 & Longitude & "," & Latitude & sHiddenA12
Next j
End With

Print #1, sHiddenA14 & Longitude & "," & Latitude & sHiddenA15
'Next 'Next without For
Print #1, sHiddenA13

Close #1

[Kestrel]

Thanks Paul, that helped a little, however your code results in a Run time error 13.
Thanks also Sam, I'm getting a little closer.

What I want to achieve is having line strings also which I though would be called automatically from the latitude and longitude coordinates.
25177

This works - Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]
But this doesn't - Print #1, [Hidden!A14] & Longitude & "," & Latitude & [Hidden!A15]

Thanks for the replies so far.
The KML file should be as in the CODE below.

Regards Paul


<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Style id="yellowLine">
<LineStyle>
<color>7f00ffff</color>
<width>4</width>
</LineStyle>
</Style>
<Placemark>
<name>Kalgoorlie/Boulder</name>
<Description>Waypoint</Description>
<Point>
<coordinates>121.46166666667,-30.7894444444391,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Eucla</name>
<Description>Waypoint</Description>
<Point>
<coordinates>128.883333333302,-31.7166666666744,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Flight plan</name>
<description>Flight plan from Charter Enquiry</description>
<styleUrl>#yellowLine</styleUrl>
<LineString>
<extrude>0</extrude>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
121.46166666667,-30.7894444444391,0
128.883333333302,-31.7166666666744,0
121.46166666667,-30.7894444444391,0
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>

[Paul_Hossler]

Thanks Paul, that helped a little, however your code results in a Run time error 13.

Highly likely

I never tried to run the macro, only reformat it a little do demonstrate an indenting / formatting technique

Since none of your variables were Dim-ed and since I prefer to use Option Explicit, I had to guess from context what kind of variable to Dim

[Kestrel]

Highly likely

I never tried to run the macro, only reformat it a little do demonstrate an indenting / formatting technique

Since none of your variables were Dim-ed and since I prefer to use Option Explicit, I had to guess from context what kind of variable to Dim

I think I got it to run after removing Option Explicit and Dim sFilename As String.
Should the sFilename be another data type?

[snb]

This is all you need:


Sub M_snb()
sn = Array("start", "121.46166666667,-30.7894444444391", "end", "128.883333333302,-31.7166666666744")

Open Application.DefaultFilePath & "snb.kml" For Output As #1
Print #1, Replace(Replace(Replace(Replace(Sheets("Hidden").Cells(1), "~", sn(0), , 1), "~", sn(2), , 1), "###", sn(1)), "##", sn(3))
Close
End Sub

In Sheet 'Hidden' cell A1 you put this text:


<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Style id="yellowLine">
<LineStyle>
<color>7f00ffff</color>
<width>4</width>
</LineStyle>
</Style>
<Placemark>
<name>~</name>
<Description>Waypoint</Description>
<Point>
<coordinates>###,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>~</name>
<Description>Waypoint</Description>
<Point>
<coordinates>##,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Flight plan</name>
<description>Flight plan from Charter Enquiry</description>
<styleUrl>#yellowLine</styleUrl>
<LineString>
<extrude>0</extrude>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
###,0
##,0
###,0
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>

From where you'll retrieve the destination names and their coordinates is up to you.
For illustration's sake I put them in array sn.
Don't forget to remove 'option Explicit'.

NB. In this case there's no need to make a distinction between latitude and longitude; the combination (i.e. 'location') is the information that is required in the KML-file. So, to keep your Excel-file simple a database combining destination name and destination location suffices.

[Paul_Hossler]

I think I got it to run after removing Option Explicit and Dim sFilename As String. Should the sFilename be another data type?

Well ...

GetSaveAsFilename() really returns a Variant, so Dim sFileName as a Variant and see if the If sFilename works




sFilename = Application.GetSaveAsFilename(BridgeFIDNo & ".kml", "Google Earth files (*.kml),*.kml", 1, "Save *.kml")

If sFilename = False Then
Exit Sub
Else
Sheets("Hidden").Range("A2") = sFilename
End If





My style / habit (old dog, new tricks) is to Dim it as a String and use this code below since I (IMHO) think it's better to stay with what I think the variable 'really' is, i.e. a String = the file name



sFilename = Application.GetSaveAsFilename(BridgeFIDNo & ".kml", "Google Earth files (*.kml),*.kml", 1, "Save *.kml")

If sFilename = "FALSE" Then
Exit Sub
Else
Sheets("Hidden").Range("A2") = sFilename
End If

[Kestrel]

Wow, I really need to keep up with this thread.
Meanwhile, I'm getting closer. I tried this code and it works but displays erroneous data points from cells unpopulated with lat and long data but containing the formulas in [Main!C5] & "," & [Main!B5] etc. eg: =KML!C13

25180

Many thanks snb and Paul, I will try a new version when I have the time.
Version 7? :doh:

Regards, Paul


Next
Print #1, [Hidden!A11] & Longitude & "," & Latitude & [Hidden!A12]
Next
Print #1, [Hidden!A14]
Print #1, [Main!C2] & "," & [Main!B2] & "," & "0"
Print #1, [Main!C3] & "," & [Main!B3] & "," & "0"
Print #1, [Main!C4] & "," & [Main!B4] & "," & "0"
Print #1, [Main!C5] & "," & [Main!B5] & "," & "0"
Print #1, [Main!C6] & "," & [Main!B6]
Print #1, [Hidden!A15]
Print #1, [Hidden!A13]

Close #1

[snb]

See the file, click the button, see the resulting KML-file represented in the messagebox.

[Kestrel]

Getting closer now, the code is probably a bit crude to real VBA coders. I will also try to format it once I have it all working.
Please remember I only started with VBA a few weeks ago.
The blank cells Lat & Long columns in the Main sheet create waypoints at 0.0,0.0 at the Equator and add an unwanted linestring. I have not been able to find a way of removing the 0.0,0.0 locations. I tried differernt combinations of ISERROR, ISBLANK etc in the affected and adjacent cells but...

25188



Sub ExportKML()

sFilename = Application.GetSaveAsFilename(BridgeFIDNo & ".kml", _
"Google Earth files (*.kml),*.kml", 1, "Save *.kml")
If sFilename = False Then
Exit Sub
Else
Sheets("Hidden").Range("A2") = sFilename
End If
Set filepath = Sheets("Hidden").Range("A2")
' Set document name
docName = "KML Document exported from Excel"

Open filepath For Output As #1

'Write header to file
outputText = [Hidden!A4] & docName & [Hidden!A5]
Print #1, outputText

'Get Data and its attributes
'Loop through each data point in column A, get attributes if any and write it out to kml
For j = 2 To yA
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column


Next


Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A2]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C2] & "," & [Main!B2]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A3]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C3] & "," & [Main!B3]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A4]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C4] & "," & [Main!B4]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A5]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C5] & "," & [Main!B5]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A6]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C6] & "," & [Main!B6]
Print #1, [Hidden!A12]


Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A7]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C7] & "," & [Main!B7]
Print #1, [Hidden!A12]


Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A8]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C8] & "," & [Main!B8]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A9]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C9] & "," & [Main!B9]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A10]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C10] & "," & [Main!B10]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A11]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C11] & "," & [Main!B11]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A12]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C12] & "," & [Main!B12]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A13]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C13] & "," & [Main!B13]
Print #1, [Hidden!A12]

Print #1, [Hidden!A8]
Print #1, [Hidden!A9]
Print #1, [Hidden!A10]
Print #1, [Hidden!A6]
Print #1, [Main!A14]
Print #1, [Hidden!A7]
Print #1, [Hidden!A11]
Print #1, [Main!C14] & "," & [Main!B14]
Print #1, [Hidden!A12]


Print #1, [Hidden!A14]
Print #1, [Main!C2] & "," & [Main!B2]
Print #1, [Main!C3] & "," & [Main!B3]
Print #1, [Main!C4] & "," & [Main!B4]
Print #1, [Main!C5] & "," & [Main!B5]
Print #1, [Main!C6] & "," & [Main!B6]
Print #1, [Main!C7] & "," & [Main!B7]
Print #1, [Main!C8] & "," & [Main!B8]
Print #1, [Main!C9] & "," & [Main!B9]
Print #1, [Main!C10] & "," & [Main!B10]
Print #1, [Main!C11] & "," & [Main!B11]
Print #1, [Main!C12] & "," & [Main!B12]
Print #1, [Main!C13] & "," & [Main!B13]
Print #1, [Main!C14] & "," & [Main!B14]
Print #1, [Hidden!A15]
Print #1, [Hidden!A13]

Close #1

openinGE = MsgBox("File successfully exported, Open in Google Earth?", vbYesNo + vbInformation, "Open")
If openinGE = vbYes Then
GE_exe_Loc = [Hidden!A1]
If GE_exe_Loc = "" Then
GE_exe = MsgBox("Could not locate Google Earth executable, Locate manually?", vbOKCancel + vbCritical, "Google Earth exe")
If GE_exe = vbCancel Then
Exit Sub
Else
GE_exe_Loc = Application.GetOpenFilename("googleearth (*.exe),*.exe", _
1, "Browse for Google Earth exe", , False)
If GE_exe_Loc = False Then
Exit Sub
Else
Sheets("Hidden").Range("A1") = GE_exe_Loc
End If
End If
End If
sFilename = [Hidden!A2]
Shell (CStr([GE_exe_Loc]) & " " & sFilename)
Exit Sub
ElseIf openinGE = vbNo Then
Exit Sub
End If
End Sub

Sub AddImageCombo()
Sheets("Hidden").Visible = True
End Sub

[snb]

Why don't you use the file I posted in http://www.vbaexpress.com/forum/showthread.php?65974-Next-without-For-error&p=395016&viewfull=1#post395016

[Kestrel]

I had trouble getting it to work. Does it only do one sector?
I copied it as a module and when I click on run I get a Sub or Function not defined error.
When I clicked on complie it opens into multiple Subs?

[snb]

You don't have to copy anything.
Open the file, click the button.
Clik Ok in the messagebox.
Check the contents of the file 'snb.kml'.
No rocket science.

[Kestrel]

Many thanks for your patience snb,
When I open your file and click on Alt+F11 I see this

25189
When I click on the KML-File button I see this, which ends at
<cordinates>
121.46166666667,-30.7894444444391,0
128.88
and the dreaded blue spinning circle, is the file not completing and failing to open?

25190

[snb]

Please read http://www.vbaexpress.com/forum/showthread.php?65974-Next-without-For-error&p=395062&viewfull=1#post395062 and act accordingly.
No Alt-F11 etc.

[Kestrel]

I followed the instructions, clicked on the button which opened a Msg Box, I clicked on OK.
But, it doesn't generate an snb.kml file?


25192

[snb]

The snb.kml file is in the same directory in which the excel file is.

[SamT]

The blank cells Lat & Long columns in the Main sheet create waypoints at 0.0,0.0 at the Equator and add an unwanted linestring. I have not been able to find a way of removing the 0.0,0.0 locations. I tried differernt combinations of ISERROR, ISBLANK etc in the affected and adjacent cells but...


For j = 2 To yA
With Sheets("Main")
If .Cells(j1) = 0 or .Cells(j2) = 0 Then GoTo CellNext
End With
Latitude = Sheets("Main").Cells(j, 2)
Longitude = Sheets("Main").Cells(j, 3)
ptName = Sheets("Main").Cells(j, 1)

Print #1, [Hidden!A6] & ptName & [Hidden!A7]
'read no of attributes (max 10 supported)
ncols = Sheets("Main").Cells(j, 15).End(xlToLeft).Column

CellNext:
Next

[Kestrel]

Global search for *.kml returns no snb.kml

25193

[snb]

In the code it's there:


Msgbox Application.defaultfilepath & "snb.kml"

[Kestrel]

Thanks Sam,

But still no luck. I tried substituting "0" "," "0,0"
I will get there, a week ago I didnt even have a file, now I am pretty close.
I get <coordinates>,</coordinates> in what should be empty cells.
Generated KML below.

Paul



<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><Style id="yellowLine">
<LineStyle>
<color>7f00ffff</color>
<width>4</width>
</LineStyle>
</Style><name>"KML Document exported from Excel "</name>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>
KALGOORLIE
</name>
<Point>
<coordinates>
121.4617,-30.78944
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>
LAVERTON
</name>
<Point>
<coordinates>
122.4239,-28.61361
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>
WARBURTON
</name>
<Point>
<coordinates>
126.5833,-26.12833
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>
WILUNA
</name>
<Point>
<coordinates>
120.2206,-26.62917
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>
KALGOORLIE
</name>
<Point>
<coordinates>
121.4617,-30.78944
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Data name="
">
<value>
</value>
</Data>
<Placemark>
<name>

</name>
<Point>
<coordinates>
,
</coordinates>
</Point>
<Style id="a">
<IconStyle>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
</IconStyle>
</Style>
<styleUrl>#a</styleUrl>
</Placemark>
<Placemark>
<name>Flight plan</name>
<description>Flight plan from Charter Enquiry</description>
<styleUrl>#yellowLine</styleUrl>
<LineString>
<extrude>0</extrude>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
121.4617,-30.78944
122.4239,-28.61361
126.5833,-26.12833
120.2206,-26.62917
121.4617,-30.78944
,
,
,
,
,
,
,
,
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>

[SamT]

But still no luck. I tried substituting "0" "," "0,0"
The blank Cells... Are they really empty or do they contain a text string like "0,0" ?

If they are Empty Text Cells, then
If J2...J3 = "" Then Go To...
If they merely look empty, but contain a space then = " "

If they contain an unknown number of spaces then first

J2 = Trim(J2): J3 = Trim(J3) ': Tells VBA Next Line here
If J2 = "" Or J3 = "" Then GoTo ...
.
.
.
CellNext

In Fact, If you're dealing with cells containing any number, (even just 1,) of spaces, use the Triming line anyway. Trim will delete all leading and trailing spaces

[Kestrel]

Sam,

They're being picked up from here where the points are being created and also where the line-string is created. They are single character cells;
The points create a push pin at Lat 0.0 Long 0.0 which ruins the focus and also creates the unwanted line string from the previous location to the 0.0,0.0 Equatorial location.

Points
</name>
<Point>
<coordinates>,</coordinates>
</Point>


String Line
<coordinates>
121.4617,-30.78944
122.4239,-28.61361
126.5833,-26.12833
120.2206,-26.62917
121.4617,-30.78944
,
,
,
,
,
,
,
,
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>

[Kestrel]

snb,

I managed to get your file to run, but with a Run-time error '9'; Subscript out of range

25203

[Kestrel]

And I changed this
Print #1, Replace(Replace(Replace(Replace(Sheets("Hidden").Cells(1), "~", sn(2, 1), , 1), "~", sn(2, 3), , 1), "###", sn(2, 4)), "##", sn(2, 1))

To
Print #1, Replace(Replace(Replace(Replace(Sheets("Hidden").Cells(1), "~", sn(2, 1), , 1), "~", sn(2, 3), , 1), "###", sn(2, 1)), "##", sn(2, 1))
Which helped
25204

But I still don't see a snb.kml file anywhere?

[snb]

That's not the file I posted.
Why don't you simply do what you are being asked ?

[Kestrel]

You know I'm out of my depth here, but trying hard to learn.
When I run your original file _Charter snb.xlsb it produces this which ends in 128.88 on the last line. And no snb.kml file is created. I thought it should run to
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>

25205

I changed my "Hidden" A1 to your code and it didn't do anything. I added your Macro to my file and it ran OK. But with a Run-time error '9'; Subscript out of range. I changed the code as above and it seemed to run with a nearly completed output ending in </Linestring>

http://www.vbaexpress.com/forum/attachment.php?attachmentid=25204&d=1570104004&thumb=1



Private Sub cmdExportToKML_Click()
sn = Sheet3.Cells(1).CurrentRegion

Open Application.DefaultFilePath & "snb.kml" For Output As #1
Print #1, Replace(Replace(Replace(Replace(Sheets("Hidden").Cells(1), "~", sn(2, 1), , 1), "~", sn(2, 3), , 1), "###", sn(2, 2)), "##", sn(2, 1))
Close

MsgBox CreateObject("scripting.filesystemobject").opentextfile(Application.DefaultFilePath & "snb.kml").readall
End Sub

Sorry for being a newbie. I realise you are probably a very busy person but I need a little more detail in your instructions if I'm going to get anywhere.
Many Thanks for your input to date...
Paul

[snb]

Start with the basics of VBA first.

[Kestrel]

The basics will come with time. But that doesn't help me with my obviously complex problem.
I'm trying to work on the basics here. https://docs.microsoft.com/en-us/dotnet/visual-basic/


(https://docs.microsoft.com/en-us/dotnet/visual-basic/)And here
http://www.snb-vba.eu/index_en.html

Thank you for your help.

[Paul_Hossler]

In the code it's there:


Msgbox Application.defaultfilepath & "snb.kml"



You forgot the back slash

Try


Msgbox Application.defaultfilepath & Application.PathSeparator & "snb.kml"

[Kestrel]

You forgot the back slash

Try


Msgbox Application.defaultfilepath & Application.PathSeparator & "snb.kml"

Thanks Paul,

Now the Msg box opens and creates the kml file after clicking on the OK Button, but there is some errors with the coordinates...

25211

The KML file is...



<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Style id="yellowLine">
<LineStyle>
<color>7f00ffff</color>
<width>4</width>
</LineStyle>
</Style>
<Placemark>
<name>KALGOORLIE</name>
<Description>Waypoint</Description>
<Point>
<coordinates>-30.78944,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>121.4617</name>
<Description>Waypoint</Description>
<Point>
<coordinates>KALGOORLIE,0,</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Flight plan</name>
<description>Flight plan from Charter Enquiry</description>
<styleUrl>#yellowLine</styleUrl>
<LineString>
<extrude>0</extrude>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
-30.78944,0
KALGOORLIE,0
-30.78944,0
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>
Due my (dis)ability with VBA I'm not sure if the errors are coming from the Hidden"A1" cell or the Macro.

Regards, Paul

[Kestrel]

Macro here...


Private Sub cmdExportToKML_Click()
sn = Sheet3.Cells(1).CurrentRegion

Open Application.DefaultFilePath & "snb.kml" For Output As #1
Print #1, Replace(Replace(Replace(Replace(Sheets("Hidden").Cells(1), "~", sn(2, 1), , 1), "~", sn(2, 3), , 1), "###", sn(2, 2)), "##", sn(2, 1))
Close

MsgBox CreateObject("scripting.filesystemobject").opentextfile(Application.DefaultFilePath & "snb.kml").readall
End Sub