Extracting data from text file that changes daily

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mikerobe - 19 Mar 2008 23:59 GMT

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.

Reply to this Message

Mark Ivey - 20 Mar 2008 00:36 GMT

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

> Hi,
> I hope I can make this clear. I have written a script to extract data
[quoted text clipped - 3 lines]
> text files are always located in the same folder.
> Thanks for any help.

Reply to this Message

mikerobe - 20 Mar 2008 00:54 GMT

> 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
[quoted text clipped - 11 lines]
> > 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

Reply to this Message

Gary''s Student - 20 Mar 2008 00:50 GMT

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

Signature

Gary''s Student - gsnu2007g

> Hi,
> I hope I can make this clear. I have written a script to extract data
[quoted text clipped - 3 lines]
> text files are always located in the same folder.
> Thanks for any help.

Reply to this Message

mikerobe - 20 Mar 2008 22:40 GMT

On Mar 19, 11:50 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> 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:
[quoted text clipped - 54 lines]
> > text files are always located in the same folder.
> > Thanks for any help.

Thanks Garys Student will have a look at that

Reply to this Message