PDA

View Full Version : [SOLVED] Request to Modify Macro



swaggerbox
02-10-2014, 05:57 AM
Found this code somewhere. How do I change this so that it runs on ALL text files in Sample folder and instead of debug.print, change so that it outputs to excel sheet column A, starting in A2? Can anyone help?


Sub ExtractValueBetweenTags()

Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "E:\Sample\Sample.TXT"

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
cellValue = text
openingParen = InStr(cellValue, "<TAG>")
closingParen = InStr(cellValue, "</TAG>")
enclosedValue = Mid(cellValue, openingParen + 5, closingParen - openingParen - 5)
Debug.Print enclosedValue

End Sub

patel
02-10-2014, 08:01 AM
Sub OpenTxtFiles2()
FPATH = "C:\Users\Andre\Desktop\excel\"
Fname = Dir(FPATH & "*.txt")
R = 2
Do While Fname <> ""
Open FPATH & Fname For Input As #1
Do Until EOF(1)
Line Input #1, textline
Text = Text & textline
Loop
Close #1
cellValue = Text
openingParen = InStr(cellValue, "<TAG>")
closingParen = InStr(cellValue, "</TAG>")
enclosedValue = Mid(cellValue, openingParen + 5, closingParen - openingParen - 5)
Cells(R, 2) = enclosedValue
R = R + 1
Fname = Dir
Loop
End Sub

p45cal
02-10-2014, 09:14 AM
I suspect the 2 in patel's
Cells(R, 2) = enclosedValue should be a 1 if you want the results in column A

patel
02-10-2014, 11:13 AM
you are right

swaggerbox
02-10-2014, 11:13 PM
thanks a lot!!! patel and p45cal