'KML文件中如何PickUp一条Line的数据,将其中东西经,南北纬同时存在的情况,负值全转为正值
Attribute VB_Name = "KMLDLPNCal"
'2015/04/16 ADD By MAOUIZAYOIOption Explicit
' Excute Interface.
Sub KMLDLPNCal()
Dim conSht As Object 'conSht is named from console.
Dim srcRng As Range
Dim aimRng As Range
Dim itemFilter, strFilter, strBuffer, strBlank As String
Dim strAarry() As String
Dim latiSum, logiSum, commaL As Integer, i As Variant
Set conSht = ActiveSheet.UsedRange
Set srcRng = conSht.Range("A1")
Set aimRng = conSht.Range("A3")
itemFilter = ","
strFilter = ",0 "
strBuffer = ""
strAarry = Split(srcRng.Value, strFilter)
latiSum = 180
logiSum = 360
commaL = 1
For i = 0 To UBound(strAarry)
If strAarry(i) = "" Then
Exit For
ElseIf strAarry(i) = " " Then
Exit For ' Otherwise TODO someting.
Else
' Pattern 4 List
If CDbl(Left(strAarry(i), InStr(strAarry(i), itemFilter) - commaL)) < 0 Then
If CDbl(Mid(strAarry(i), InStr(strAarry(i), itemFilter) + commaL)) < 0 Then
strBuffer = strBuffer & CStr(CDbl(Left(strAarry(i), InStr(strAarry(i), itemFilter) - commaL)) + logiSum) & itemFilter & CStr(CDbl(Mid(strAarry(i), InStr(strAarry(i), itemFilter) + commaL)) + latiSum)
Else
strBuffer = strBuffer & CStr(CDbl(Left(strAarry(i), InStr(strAarry(i), itemFilter) - commaL)) + logiSum) & itemFilter & Mid(strAarry(i), InStr(strAarry(i), itemFilter) + commaL)
End If
ElseIf CDbl(Left(strAarry(i), InStr(strAarry(i), itemFilter) - commaL)) >= 0 Then
If CDbl(Mid(strAarry(i), InStr(strAarry(i), itemFilter) + commaL)) < 0 Then
strBuffer = strBuffer & Left(strAarry(i), InStr(strAarry(i), itemFilter) - commaL) & itemFilter & CStr(CDbl(Mid(strAarry(i), InStr(strAarry(i), itemFilter) + commaL)) + latiSum)
Else
strBuffer = strBuffer & strAarry(i)
End If
End If
strBuffer = strBuffer & strFilter
End If
Next
If strBuffer <> "" Then
aimRng.Value = strBuffer
End If
End Sub
PS:如果是把KML中数据拷到Excel 一单元格然后执行的话,切记把源单元格和目标单元格的格式都切换成String:文字列的那种。普通默认的格式,数据存到一定长度会被截断。(Excel 2003 以上版本每个单元格大约存32000左右字节,自己注意好)