get rid of unwanted characters and send to new txt or xls file.

[fbastian]

hi all,
I was wondering if anyone has a skeleton or more developed code which will get rid of unwanted text in a txt file.
example below:
<gml:coord>
<gml:X>150.564466</gml:X>

<gml:Y>-25.3223620007281</gml:Y>

</gml:coord>

- (http://errdwas20/AIRS/AIRSServices/DownloadGML.ashx?LocationID=13156#) <gml:coord>
<gml:X>150.566276</gml:X>

<gml:Y>-25.2994270007276</gml:Y>

</gml:coord>


- (http://errdwas20/AIRS/AIRSServices/DownloadGML.ashx?LocationID=13156#) <gml:coord>
<gml:X>150.578045</gml:X>

<gml:Y>-25.2846840007273</gml:Y>

</gml:coord>


- (http://errdwas20/AIRS/AIRSServices/DownloadGML.ashx?LocationID=13156#) <gml:coord>
<gml:X>150.590719</gml:X>

<gml:Y>-25.2740360007271</gml:Y>

</gml:coord>

All i'd like to keep from this file are the coordinate numbers or the x and y if you like.
and if you can tell me how i can send them to a new txt file it would be great!

thank you.

[stanleydgrom]

fbastian,

See the attached workbook "Get rid of unwanted characters - fbastian - VP - SDG12.xls" with macro "MoveXY".

With your text data beginning in cell A1, run macro "MoveXY".

Have a great day,
Stan

[GTO]

Just another take... Presuming consistant strings and to overwrite the original text file (Please test on junk copies), maybe:


Option Explicit

Sub exa()
Dim _
aryInput As Variant, _
aryY As Variant, _
aryX As Variant, _
aryOutput As Variant, _
i As Long, _
strLine As String, _
FSO As Object, _
fsoFile As Object, _
REX As Object

'// Change textfile name to suit, as well as substitute path for ThisWorkbook.Path //
'// used for testing... //
Const TFILE_NAME As String = "\StripVals.txt"

Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.IgnoreCase = True
.Pattern = "<\/?gml:[XY]>"
End With
ReDim aryX(0 To 0)
ReDim aryY(0 To 0)
Set FSO = CreateObject("Scripting.FileSystemObject")
With FSO
Set fsoFile = FSO.OpenTextFile(ThisWorkbook.Path & TFILE_NAME)
Do While Not fsoFile.AtEndOfStream

strLine = fsoFile.ReadLine

If InStr(1, strLine, "<gml:X>") > 0 Then
ReDim Preserve aryX(1 To UBound(aryX) + 1)
aryX(UBound(aryX)) = CDbl(REX.Replace(strLine, vbNullString))
ElseIf InStr(1, strLine, "<gml:Y>") > 0 Then
ReDim Preserve aryY(1 To UBound(aryY) + 1)
aryY(UBound(aryY)) = CDbl(REX.Replace(strLine, vbNullString))
End If
Loop
fsoFile.Close

aryOutput = Application.Transpose(Array(aryX, aryY))

'// Overwrite the original textfile //
Set fsoFile = .CreateTextFile(ThisWorkbook.Path & TFILE_NAME)

fsoFile.WriteLine "X Val" & vbTab & vbTab & "Y Val"
For i = LBound(aryOutput, 1) To UBound(aryOutput, 1)
fsoFile.WriteLine aryOutput(i, 1) & vbTab & aryOutput(i, 2)
Next
fsoFile.Close
End With
End Sub

Hope that helps,

Mark

[fbastian]

Thank you for your help.
both codes work.

thank you very much! :)