Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
Here's the code:
'-------------------------------------------------- Function ReadTextFile$(Filename$) ' Reads large amounts of data from a text file in one shot. Dim iNum% On Error GoTo ErrHandler iNum = FreeFile(): Open Filename For Input As #iNum ReadTextFile = Space$(LOF(iNum)) ReadTextFile = Input(LOF(iNum), iNum) ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Function 'ReadTextFile() '-------------------------------------------------- Sub FlawedSub() Dim vTmp, sLine$ Const sFile$ = "C:\data.txt" sLine = ReadTextFile(sFile) sLine = Trim(sLine) vTmp = Split(Application.Substitute(sLine, " ", "*", 1), "*") MsgBox Trim(vTmp(0)) End Sub '-------------------------------------------------- The file data.txt contains the following string: (There's a tab char just after the a123) a123 A. BC #123 The output is supposed to be "a123". Instead, I'm getting "a123 A." Ugh, why doesn't this work? BTW, I'm using Excel 2010, so I don't know if that's the problem. Is there another way to extract the first substring on the nth line in a file? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
The file data.txt contains the following string:
(There's a tab char just after the a123) a123 A. BC #123 Oh.., now you tell us! Any other *very important details* like this we need to know? Sub ParseTextFile2a() Dim vTmp, sLine$ Const sFile$ = "C:\Data.txt" sLine = ReadTextFile(sFile) vTmp = Split(sLine, vbTab) Debug.Print Trim(vTmp(0)) '//returns "a123" Debug.Print Trim(vTmp(1)) '//returns "A. BC #123" '//where the file contains "a123", a Tab followed by "A. BC #123" vTmp = Split(vTmp(1), " ") Dim n& For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n '//returns '"a123" '"A. BC #123" '"A." '"BC" '"#123" End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
Cleaned up...
Sub ParseTextFile2a() Dim vTmp, n& Const sFile$ = "C:\Data.txt" '//where the file contains "a123 A. BC #123" '//"a123", <Tab "A. BC #123" 'Get the file text parsed by 'TAB' characters vTmp = Split(ReadTextFile(sFile), vbTab) For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n 'Parse the 2nd element by 'SPACE' characters vTmp = Split(vTmp(1), " ") For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n End Sub Returns: a123 A. BC #123 A. BC #123 -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
Finally...
Sub ParseTextFile2b() ' Parses a multi-line text file Dim vText, vTmp, n&, k& Const sFile$ = "C:\Data.txt" '//where the file contains 'a123 A. BC #123 'b123 B. CD #123 'c123 C. DE #123 'Get the file contents and parse each line vText = Split(ReadTextFile(sFile), vbCrLf) 'Parse each line in the file For k = LBound(vText) To UBound(vText) 'Get the line text parsed by 'TAB' characters vTmp = Split(vText(k), vbTab) For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n 'Parse the 2nd element by 'SPACE' characters vTmp = Split(vTmp(1), " ") For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n Next 'k End Sub ...which returns the following: a123 A. BC #123 A. BC #123 b123 B. CD #123 B. CD #123 c123 C. DE #123 C. DE #123 -- 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
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, June 4, 2019 at 12:49:58 AM UTC-7, GS wrote:
The file data.txt contains the following string: (There's a tab char just after the a123) a123 A. BC #123 Oh.., now you tell us! Any other *very important details* like this we need to know? Hi Gary! I've been busy with some stuff, but I'm going to test your latest code soon and see how it works. Regarding the *very important details*, I can explain that. Here goes: I believe I did provide sufficient details in my earlier posts. In my first post titled "Tricky regular expression", I explained that I was looking for "the first set of whitespaces that occur in the line". Also, I figured that once you provided any solution,I could easily tailor it to work with files containing one input line or multiple lines. So, I'm going to test your code soon and see how it goes. Thanks again for your help! 8) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, June 4, 2019 at 12:49:58 AM UTC-7, GS wrote:
The file data.txt contains the following string: (There's a tab char just after the a123) a123 A. BC #123 Oh.., now you tell us! Any other *very important details* like this we need to know? Hi Gary! I've been busy with some stuff, but I'm going to test your latest code soon and see how it works. Regarding the *very important details*, I can explain that. Here goes: I believe I did provide sufficient details in my earlier posts. In my first post titled "Tricky regular expression", I explained that I was looking for "the first set of whitespaces that occur in the line". Technically speaking, I should have caught that the 1st occurance of white space was in fact the TAB character but coming into play after Claus' reply I focused on the SPACE character instead, and so took it as 4 of those. What you should have explained more accurately is the EXACT structure of the data being parsed due to the numerous ways to create character spacing and/or how new lines of text occur. (Assumes you have/had knowledge of the data structure<g) In the latter case, my code delimits the multi-line text file using the CarriageReturn+Linefeed character combo because that's how I structured the file. Your multi-line file may be structured differently and if so the code will not work; - You'll need to test for which new line character is used: vbCr (carriage return) vbLf (linefeed) **most common Also, I figured that once you provided any solution,I could easily tailor it to work with files containing one input line or multiple lines. So, I'm going to test your code soon and see how it goes. Thanks again for your help! 8) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
Hi Garry!
I finally got around to testing your code. It pretty much does what I wanted, but I had to tweak it slightly because the second or third Split function call was leaving a line feed in the beginning of some strings. But, I'm happy with the results, so thank you very much! Robert On Tuesday, June 4, 2019 at 1:54:31 AM UTC-7, GS wrote: Finally... Sub ParseTextFile2b() ' Parses a multi-line text file Dim vText, vTmp, n&, k& Const sFile$ = "C:\Data.txt" '//where the file contains 'a123 A. BC #123 'b123 B. CD #123 'c123 C. DE #123 'Get the file contents and parse each line vText = Split(ReadTextFile(sFile), vbCrLf) 'Parse each line in the file For k = LBound(vText) To UBound(vText) 'Get the line text parsed by 'TAB' characters vTmp = Split(vText(k), vbTab) For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n 'Parse the 2nd element by 'SPACE' characters vTmp = Split(vTmp(1), " ") For n = LBound(vTmp) To UBound(vTmp) Debug.Print vTmp(n) Next 'n Next 'k End Sub ..which returns the following: a123 A. BC #123 A. BC #123 b123 B. CD #123 B. CD #123 c123 C. DE #123 C. DE #123 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, June 4, 2019 at 1:54:31 AM UTC-7, GS wrote:
'Get the file contents and parse each line vText = Split(ReadTextFile(sFile), vbCrLf) Garry, What if the above line crashes for any reason? Like if the file referenced by "sFile" does not exist? If there's an error of any kind while reading the file, I'd like vText to contain the empty string "". How do I do that? Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, June 4, 2019 at 1:54:31 AM UTC-7, GS wrote:
'Get the file contents and parse each line vText = Split(ReadTextFile(sFile), vbCrLf) Garry, What if the above line crashes for any reason? Like if the file referenced by "sFile" does not exist? If there's an error of any kind while reading the file, I'd like vText to contain the empty string "". How do I do that? Thanks! You could dump the file into a string variable before splitting the lines into vText... Dim sData$ sData = ReadTextFile(sFile) If Len(sData) 0 Then vText = Split(sData, vbCrLf) OR You could check if the file exists before reading it: If FileExists(sFile) Then sData = ReadTextFile(sFile) If Len(sData) 0 Then vText = Split(sData, vbCrLf) Else 'do something else... End If 'Len(sData) Else 'do something else... End If 'FileExists Public Function FileExists(Filename$) As Boolean Dim nAttr As Long ' Grab this files attributes, and make sure it isn't a folder. ' This test includes cases where file doesn't exist at all. nAttr = GetFileAttributes(Filename) If (nAttr And vbDirectory) < vbDirectory Then FileExists = True ElseIf Err.LastDllError = ERROR_SHARING_VIOLATION Then FileExists = True End If End Function -- 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
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, August 13, 2019 at 11:02:48 AM UTC-7, GS wrote:
You could dump the file into a string variable before splitting the lines into Yes, I agree I can do some basic checks to see if the file is not empty or if the file does not exist. However, if anything else crashes in my function, couldn't I just use the ON ERROR GOTO statement? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the heck doesn't this work?
On Tuesday, August 13, 2019 at 11:02:48 AM UTC-7, GS wrote:
You could dump the file into a string variable before splitting the lines into Yes, I agree I can do some basic checks to see if the file is not empty or if the file does not exist. However, if anything else crashes in my function, couldn't I just use the ON ERROR GOTO statement? Most all procedures *should have* an error handler built into them as a matter of 'best practice'. Example: Sub DoThis() 'Brief description of process intent 'Declare variables/constants 'Start code 'When it reaches a potential sect to raise an error... On Error GoTo ErrExit '//label specifying jump-to position 'Potential code to raise error ErrExit: 'Code to handle the error (and optionally Resume execution 'Code to cleanup objects before Exiting procedure 'Code to notify user of the error and/or request an action to take 'Function value to return ' ie: FunctionName = (Err = 0) if Boolean ' otherwise a null or default value End Sub Error handling is an elaborate convention on its own which entire book chapters are dedicated for. You can dismiss the error, display a message to the user, and/or write the error to an error.log file using a central error handling system in your VBA projects. -- 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 | |||
How the heck? | Excel Worksheet Functions | |||
trace dependent tool doesn't work but trace precendent doesn't | Excel Discussion (Misc queries) | |||
What the heck is wrong here? | Excel Programming | |||
macro doesn't properly record AutoSum (and SendKeys doesn't work) | Excel Programming | |||
Shell Doesn't Work With UNC - Can't Get ShellExecute to Work Either | Excel Programming |