Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How the heck? Dave Excel Worksheet Functions 6 April 8th 08 03:34 AM
trace dependent tool doesn't work but trace precendent doesn't marnie Excel Discussion (Misc queries) 2 May 24th 07 12:26 PM
What the heck is wrong here? [email protected] Excel Programming 1 January 5th 07 07:20 PM
macro doesn't properly record AutoSum (and SendKeys doesn't work) crimsonkng Excel Programming 2 November 21st 06 03:11 PM
Shell Doesn't Work With UNC - Can't Get ShellExecute to Work Either [email protected] Excel Programming 1 September 1st 06 05:35 AM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"