PDA

View Full Version : Solved: Extract data from text file



swaggerbox
04-28-2010, 02:56 AM
How do I extract data from sample.txt containing the following information: <date Year="2008" Month="09" Day="18"/> into an excel file, formatting the data into a single cell, e.g. 20080918

GTO
04-28-2010, 03:23 AM
I would suggest zipping an example of the text file.

swaggerbox
04-28-2010, 03:31 AM
Here it is...

GTO
04-29-2010, 06:32 PM
Maybe...

This presumes 'sample.txt' and the workbook are in the same folder.


Option Explicit

Sub exa()
Dim _
strLine As String, _
strLineOut As String, _
i As Long

With CreateObject("Scripting.FileSystemObject").OpenTextFile(ThisWorkbook.Path & "\sample.txt")
strLine = .ReadAll
.Close
End With

With CreateObject("VBScript.RegExp")
.Global = False
.MultiLine = True
.Pattern = "(<date)(.+)(/>)"

If .Test(strLine) Then
strLine = .Execute(strLine)(0)
For i = 1 To Len(strLine)
If IsNumeric(Mid(strLine, i, 1)) Then
strLineOut = strLineOut & Mid(strLine, i, 1)
End If
Next
Range("A1").Value = CLng(strLineOut)
End If
End With
End Sub

Hope that helps,

Mark

swaggerbox
04-29-2010, 10:13 PM
thanks Mark! This is what I wanted.