Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting quarterly data and having it update daily | Excel Worksheet Functions | |||
HOW DO I FREEZE DATA THAT CHANGES DAILY. dAILY/MONTHLY BUDGET | Excel Discussion (Misc queries) | |||
Extracting values from a single dynamic cell to a list daily | Excel Worksheet Functions | |||
Extracting text from one file to another | Excel Discussion (Misc queries) | |||
Macro to copy daily text file | Excel Programming |