Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Hi Guys,
I have a workbook that write a new line to an external (text file) log each day. This works fine no problem here... But we really only need to be able to view the lines for the past month - in fact even only the last 23 lines in the log file, since there are never more than 23 workdays in a month. So, how to delete all but the last 23 rows in an external text files, using VBA? TIA, CE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Hi Charlotte
One way, simplified to three lines and without a saving procedu Sub test() Dim iFnum As Integer Dim Linje As String Dim S1 As String, S2 As String, S3 As String iFnum = FreeFile Open "C:\Temp\Test.txt" For Input As #iFnum While Not EOF(iFnum) Line Input #iFnum, Linje S1 = S2 S2 = S3 S3 = Linje Wend Close #iFnum ' save to original file instead of MsgBox S1 & vbNewLine & S2 & vbNewLine & S3 End Sub Beste hilsen Harald "Charlotte E." skrev i melding ... Hi Guys, I have a workbook that write a new line to an external (text file) log each day. This works fine no problem here... But we really only need to be able to view the lines for the past month - in fact even only the last 23 lines in the log file, since there are never more than 23 workdays in a month. So, how to delete all but the last 23 rows in an external text files, using VBA? TIA, CE |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Bingo!
Working :-) Thanks, Harald :-) CE "Harald Staff" wrote in message ... Hi Charlotte One way, simplified to three lines and without a saving procedu Sub test() Dim iFnum As Integer Dim Linje As String Dim S1 As String, S2 As String, S3 As String iFnum = FreeFile Open "C:\Temp\Test.txt" For Input As #iFnum While Not EOF(iFnum) Line Input #iFnum, Linje S1 = S2 S2 = S3 S3 = Linje Wend Close #iFnum ' save to original file instead of MsgBox S1 & vbNewLine & S2 & vbNewLine & S3 End Sub Beste hilsen Harald "Charlotte E." skrev i melding ... Hi Guys, I have a workbook that write a new line to an external (text file) log each day. This works fine no problem here... But we really only need to be able to view the lines for the past month - in fact even only the last 23 lines in the log file, since there are never more than 23 workdays in a month. So, how to delete all but the last 23 rows in an external text files, using VBA? TIA, CE |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Another way that doesn't pad with empty lines...
Sub TrimLinesFromFile() Dim vText, n& vText = Split(ReadTextFileContents(sFile), vbCrLf) If UBound(vText) < 23 Then Exit Sub Const sFile$ = "C:\Temp\Test.txt" '//edit to suit For n = 0 To UBound(vText) - 22 vText(n) = "~" Next 'n vText = Filter(vText, "~", False) WriteTextFileContents Join(vText, vbCrLf), sFile End Sub ...which uses the following reusable helper routines to read from and write back to text files the entire contents in one shot. The loop assumes your file gets the daily input 'appended' to existing content and so removes the oldest entries (at the top of the file). If UBound(vText) does not exceed 22 then there's nothing to do so the code below that line will only run when the file contains more than 23 lines. *It's very important that there are no empty lines at the end of the file for this to work! Also, as written there will never be more than 23 lines in the file.* <Helper routines WriteTextFileContents does not insert a line feed so your file contains no blank lines at the end. Function ReadTextFileContents(Filename As String) As String ' Reads large amounts of data from a text file in one single step. Dim iNum As Integer On Error GoTo ErrHandler iNum = FreeFile(): Open Filename For Input As #iNum ReadTextFileContents = Space$(LOF(iNum)) ReadTextFileContents = Input(LOF(iNum), iNum) ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Function 'ReadTextFileContents() Sub WriteTextFileContents(TextOut As String, _ Filename As String, _ Optional AppendMode As Boolean = False) ' Reusable procedure that Writes/Overwrites or Appends ' large amounts of data to a Text file in one single step. ' **Does not create a blank line at the end of the file** Dim iNum As Integer On Error GoTo ErrHandler iNum = FreeFile() If AppendMode Then Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut; Else Open Filename For Output As #iNum: Print #iNum, TextOut; End If ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Sub 'WriteTextFileContents() </Helper routines -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
GS wrote:
Function ReadTextFileContents(Filename As String) As String [snip] Sub WriteTextFileContents(TextOut As String, _ Y'know, upon seeing your code, it occurs to me that I never thought to move that code into functions myself. Sigh. -- Behind every new sign is someone stupid. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
GS wrote:
Another way that doesn't pad with empty lines... Sub TrimLinesFromFile() Dim vText, n& vText = Split(ReadTextFileContents(sFile), vbCrLf) If UBound(vText) < 23 Then Exit Sub Const sFile$ = "C:\Temp\Test.txt" '//edit to suit For n = 0 To UBound(vText) - 22 vText(n) = "~" Next 'n vText = Filter(vText, "~", False) WriteTextFileContents Join(vText, vbCrLf), sFile End Sub *It's very important that there are no empty lines at the end of the file for this to work! Also, as written there will never be more than 23 lines in the file.* This version ignores empty lines at the end of the file, makes it easier to change the number of lines that are kept, and fixes a couple bugs. ;-) Sub TrimLinesFromFile() Dim vText, n&, fnum& Const sFile$ = "C:\Temp\Test.txt" '//edit to suit Const maxLines = 23 vText = Split(ReadTextFileContents(sFile), vbCrLf) For n& = UBound(vText) To 0 Step -1 If "" < vText(n&) Then If n& < UBound(vText) Then ReDim Preserve vText(n&) Exit For End If Next If n& < 1 Then Exit Sub 'text file only contains blank lines If UBound(vText) < maxLines Then Exit Sub 'text file is < 23 lines long For n& = 0 To UBound(vText) - maxLines vText(n&) = "~" Next 'n vText = Filter(vText, "~", False) WriteTextFileContents Join(vText, vbCrLf), sFile End Sub -- Awesome thing #1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
GS wrote:
Function ReadTextFileContents(Filename As String) As String [snip] Sub WriteTextFileContents(TextOut As String, _ Y'know, upon seeing your code, it occurs to me that I never thought to move that code into functions myself. Sigh. Well, I use these in every app because I don't use the Registry to store anything. This keeps my stuff 'fully portable' in that all app settings/configs are stored in text files. I suspect you do same for routines you keep using over and over. I just drop modules containing what I call 'default' or 'core' routines into VBA projects when I first create them. It's unfortunate that VBA components don't ref their source files as do VB components, huh! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
GS wrote:
GS wrote: Function ReadTextFileContents(Filename As String) As String [snip] Sub WriteTextFileContents(TextOut As String, _ Y'know, upon seeing your code, it occurs to me that I never thought to move that code into functions myself. Sigh. Well, I use these in every app because I don't use the Registry to store anything. This keeps my stuff 'fully portable' in that all app settings/configs are stored in text files. AFA settings are concerned, I use standard .ini files, read from & written to using the standard Get/WritePrivateProfileString WinAPI calls. ;-) I suspect you do same for routines you keep using over and over. I just drop modules containing what I call 'default' or 'core' routines into VBA projects when I first create them. It's unfortunate that VBA components don't ref their source files as do VB components, huh! I meant *specifically* reading & writing to text files in their entirety. I *always* retype that code without thinking about it. (Yeah, yeah, I know, that's what functions are for.) -- She's pretty, but dumb as a brick. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Uh.., couple of things worth mentioning here.<g
<1st I'm not sure why you use the type symbol everywhere the var is used: Since Dim n&, fnum& is the same as Dim n As Long, fnum As Long we wouldn't write... For n As Long =... or vText(n As Long) </1st <2nd I had occasion to strip trailing blank lines from 1000s of CNC program files and I did same way using Filter(), only starting with UBound using Step -1 until I got an element with Len(). CNC prog files are never empty, but every time a controller writes back edited files the default line feed was added. That's why my write sub prepends a vbCrLf to the output string if AppendMode. I never thought to use Redim Preserve as you do here. I'm definitely going to review that old code now, after reading your approach!<g </2nd -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
AFA settings are concerned, I use standard .ini files, read from &
written to using the standard Get/WritePrivateProfileString WinAPI calls. I use ini files too and so also the PrivateProfile functions via a wrapper module I got from Rob Bovey way back in my early days of VBA development. I 'mostly' use INIs for default app settings only, and only write to INI during update/upgrade installs. The apps read INI at startup. I was referring, though, to user-defined settings/configs stored in UDTs during runtime. I use a separate file for each group, and use 'Get_FromTextFile' function and 'Put_InTextFile' sub because these are binary files. (When there's only a few values to store I may just use the INI if inclined to do so!<g) I also store my app license profiles in 2 different encrypted 'dat' files. These apps use an automated instance instantiated by a VB6 frontloader EXE, which validates licensing before doing anything else. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
GS wrote:
Uh.., couple of things worth mentioning here.<g <1st I'm not sure why you use the type symbol everywhere the var is used: Since Dim n&, fnum& is the same as Dim n As Long, fnum As Long we wouldn't write... For n As Long =... or vText(n As Long) </1st Because I've used BASICs where n& and n are *not* the same variable. (I can't think of which ones offhand, sorry.) I try to be consistent: everything that uses a type symbol *anywhere*, uses it *everywhere*. (Of course, I don't *usually* use type symbols on variables, preferring to Dim As type. And of course, I missed the $ at the declaration of sFile, or else I would've changed all occurrences to sFile$.) <2nd I had occasion to strip trailing blank lines from 1000s of CNC program files and I did same way using Filter(), only starting with UBound using Step -1 until I got an element with Len(). CNC prog files are never empty, but every time a controller writes back edited files the default line feed was added. That's why my write sub prepends a vbCrLf to the output string if AppendMode. I never thought to use Redim Preserve as you do here. I'm definitely going to review that old code now, after reading your approach!<g </2nd One thing I noticed in your code that I wouldn't do myself is this: For n& = 0 To UBound(vText) - maxLines vText(n&) = "~" Next 'n vText = Filter(vText, "~", False) WriteTextFileContents Join(vText, vbCrLf), sFile ISTM that going through the array, changing everything except what you need, is a waste of CPU cycles (especially with strings). Instead, I would've just pulled out the elements I actually needed and ignored the rest: Open outputfile$ For Output As fnum& For n& = (UBound(vText) - (maxLines - 1)) To UBound(vText) Print #funm&, vText(n&) Next Close fnum& This could be even moved to a subroutine, something like this: Sub array1DSliceToFile(filepath As String, arr As Variant, _ startEl As Variant, endEl As Variant) Dim fnum As Long, L0 As Variant 'some input sanitation... If VarType(arr) = vbArray Then If VarType(startEl) = 0 Then startEl = LBound(arr) If VarType(endEl) = 0 Then endEl = UBound(arr) If startEl endEl Then Exit Sub If startEl UBound(arr) Then Exit Sub If startEl < LBound(arr) Then startEl = LBound(arr) If endEl < LBound(arr) Then Exit Sub If endEl UBound(arr) Then endEl = UBound(arr) End If If Len(filepath) < 1 Then Exit Sub 'end sanitation fnum = FreeFile Open filepath For Output As fnum If VarType(arr) < vbArray Then Print #fnum, arr Else For L0 = startEl To endEl Print #fnum, arr(L0) Next End If Close fnum End Sub (I can't believe I just spent 45 minutes on this.) ....called like so... array1DSliceToFile sFile$, vText, (UBound(vText) - maxLines) + 1, _ UBound(vText) -- Let me not seem to have lived in vain. -- Tycho Brahe |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
One thing I noticed in your code that I wouldn't do myself is this:
For n& = 0 To UBound(vText) - maxLines vText(n&) = "~" Next 'n vText = Filter(vText, "~", False) WriteTextFileContents Join(vText, vbCrLf), sFile ISTM that going through the array, changing everything except what you need, is a waste of CPU cycles (especially with strings). Instead, I would've just pulled out the elements I actually needed and ignored the rest: <1 I have never found writing a text file one line at a time to be very efficient at best. I realize this depends on the number of lines in the file, but since this will always be unknown at design time I'd opt for using more a efficient approach. <2 The point of replacing the elements to be removed by the Filter() function is because it needs that to work for using a common 'Find' string in all elements to be filtered. <3 The way I did this requires way less code and is highly efficient regardless of file length. (IMO) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all, but the last 23 lines of an external text file
Because I've used BASICs where n& and n are *not* the same variable.
(I can't think of which ones offhand, sorry.) I try to be consistent: everything that uses a type symbol *anywhere*, uses it *everywhere*. (Of course, I don't *usually* use type symbols on variables, preferring to Dim As type. And of course, I missed the $ at the declaration of sFile, or else I would've changed all occurrences to sFile$.) I thought this deserved a separate reply... The type symbols are attritional as a result of team development. I fought for 'As <type' long and hard because it a better 'self-documenting' way to program. Habits are hard to break, though, and use of the symbols has evolved into a habit for me now. I'm comfortable using them but I worry that OPs and other readers may not understand them and thus an element of unclarity (is that a word?) in the code offerings I post. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading an external text file | Excel Programming | |||
IF, ADD TEXT. DELETE LINES AND RETURN VALUE... | Excel Worksheet Functions | |||
How to check if external .txt file has text in it... | Excel Programming | |||
excel97 vba to append lines to text file overwriting last 2 lines | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |