Extracting data from text file that changes daily

[mikerobe]

Hi,
I hope I can make this clear. I have written a script to extract data
from a text file to excel based on an applied filter. I wish to do a
daily extract to excel of the same data each day from text files that
are named by date eg 20080319.txt. How might this be possible? The
text files are always located in the same folder.
Thanks for any help.

 

[Mark Ivey]

Since you are already building a text file, maybe you could also take the
same data and build a CSV file. This would allow for the file to be opened
in Excel, but would lack formatting features.

Just an idea...

Mark

 

[Gary''s Student]

Here is an example of creating the name of the file based upon the curent
date and then importing the file. It is adapted from the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/19/2008 by James Ravenswood
'

'
Dim sm As String, sd As String
yr = Year(Date)
sm = Month(Date)
If sm < 10 Then sm = "0" & sm
sd = Day(Date)
If sd < 10 Then sd = "0" & sd
s = yr & sm & sd
s1 = "Text;C:\" & s & ".txt"
With ActiveSheet.QueryTables.Add(Connection:=s1, _
Destination:=Range("A1"))
.Name = s
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[mikerobe]

Since you are already building a text file, maybe you could also take the
same data and build a CSV file. This would allow for the file to be opened
in Excel, but would lack formatting features.

Just an idea...

Mark

Thanks Mark
Actually I would like to build an ever expanding Excel file with the
daily extract separated by the extract date.
Eddie

 

[mikerobe]

Here is an example of creating the name of the file based upon the curent
date and then importing the file. It is adapted from the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/19/2008 by James Ravenswood
'

'
Dim sm As String, sd As String
yr = Year(Date)
sm = Month(Date)
If sm < 10 Then sm = "0" & sm
sd = Day(Date)
If sd < 10 Then sd = "0" & sd
s = yr & sm & sd
s1 = "Text;C:\" & s & ".txt"
With ActiveSheet.QueryTables.Add(Connection:=s1, _
Destination:=Range("A1"))
.Name = s
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks Garys Student will have a look at that