Extracting data from text file that changes daily
 

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.

Extracting data from text file that changes daily
 

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

"mikerobe" wrote in message
...
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.

Extracting data from text file that changes daily
 

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

--
Gary''s Student - gsnu2007g


"mikerobe" wrote:

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.

Extracting data from text file that changes daily
 

On Mar 19, 11:36 pm, "Mark Ivey" wrote:
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

"mikerobe" wrote in message

...

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.

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

Extracting data from text file that changes daily
 

On Mar 19, 11:50 pm, Gary''s Student
wrote:
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

--
Gary''s Student - gsnu2007g

"mikerobe" wrote:
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.

Thanks Garys Student will have a look at that