Am Wed, 2 Jan 2019 10:04:26 -0800 (PST) schrieb :
I tried Claus solution but that did not work. Yours did.
download the workbook from here and test it (Macros are disabled in
In Sheet1 you see your new data, in sheet2 the previous data.
FSO needs to be destroyed when you're finished with it so its memory space gets
released. Each instance you create stays in memory until it's destroyed and so
'programmer best practice' suggests that any code that deliberately creates an
object should also deliberately 'Set objVarName = Nothing' when no longer
The downside to the nature of this task is the 1st line of the blocks of text
varies file-to-file. This needs to be able to pull the block delimiter text and
headers from the file rather than these be hard-coded.
Thanks for the suggestion to 'clean' the text for unwanted chars.
Unfortunately, WorksheetFunction.Clean doesn't include any non-printable chars
above 32 so I made my own function that specifies chars to keep and include
Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Any non alpha-numeric characters to
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
' Returns: String containing only wanted characters.
' Comments: Works very fast using the Mid$() function over other methods.
Const sSource$ = "FilterString()"
'The basic characters to always keep by default
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"
Dim i&, sKeepers$
sKeepers = IncludeChars
If IncludeLetters Then _
sKeepers = sKeepers & sLetters & UCase(sLetters)
If IncludeNumbers Then sKeepers = sKeepers & sNumbers
For i = 1 To Len(TextIn)
If InStr(sKeepers, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
End Function 'FilterString()
Free usenet access at http://www.eternal-september.org