Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old May 19th 19, 04:36 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,084
Default Tricky regular expression

You could simply split the lines using the space as the delimiter; - skip the
empty element! Your sample text "a123 A. BC #123", for example:

vTmp = Split(sLine, " ") returns the following...
vTmp(0) = "a123"
vTmp(1) = ""
vTmp(2) = "A."
vTmp(3) = "BC"
vTmp(4) = "#123"

...so checking each line for content is the lines to process.

HOWEVER:

You should read the entire file into an array and process (dump) it into a
blank worksheet, then parse the data however desired. Alternatively, you could
process each line with the code I posted earlier and do whatever with the
results.

Here's how:

Sub ParseFile()
Dim sFile$, sTextIn$, vData, n&, rng

'Select the file
sFile = Get_FileToOpen(): If sFile = "" Then Exit Sub

'Get file contents into an array
sTextIn = ReadTextFile(sFile): vData = Split(sTextIn, vbCrLf)

'Dump the array into a blank worksheet
ActiveWorkbook.Sheets.Add
Set rng = ActiveSheet.Cells(1).Resize(UBound(vData) + 1)
rng = Application.Transpose(vData)
'Process each cell's contents
For n = 1 to rng.rows.count
If StrChk(rng.Cells(n)) Then
'do something...
Else
'do something else...
End If
Next 'n

'-OR- Process each element of the array
For n = LBound(vData) to UBound(vData)
If StrChk(vData(n)) Then
'do something...
Else
'do something else...
End If
Next 'n
End Sub 'ParseFile

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()

Function Get_FileToOpen$(Optional FileTypes$)
Dim vFile
If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*"
vFile = Application.GetOpenFileName(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function

Function StrChk(sText$) As Boolean
Select Case Asc(Left(sText, 1))
Case 97 To 122
Select Case Asc(Right(sText, 1))
Case 48 To 122: StrChk = (InStr(sText, " ") 0)
End Select '//Asc(Right(sText, 1))
End Select '//Asc(Left(sText, 1))
End Function '//StrChk()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

  #12   Report Post  
Old May 19th 19, 07:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 64
Default Tricky regular expression

Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible.

The code that I posted earlier should have worked, and it fits my needs, but for some reason the OpenTextFile and ReadLine combination somehow taints the file string and prevent's Claus's solution from working.

  #13   Report Post  
Old May 19th 19, 08:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,731
Default Tricky regular expression

Hi Robert,

Am Sun, 19 May 2019 11:06:28 -0700 (PDT) schrieb RG III:

Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible.

The code that I posted earlier should have worked, and it fits my needs, but for some reason the OpenTextFile and ReadLine combination somehow taints the file string and prevent's Claus's solution from working.


try:

Function StrChk(myStr As String) As Boolean
Select Case Asc(Left(myStr, 1))
Case 97 To 122
Select Case Asc(Right(myStr, 1))
Case 48 To 122
If InStr(myStr, " ") 0 Then StrChk = True
End Select
End Select
End Function

Sub ParseFile()
Dim n As Integer, i As Integer, RowNmbr As Integer
Dim varOut() As Variant
Dim sText As String

Const sPath = "G:\Data\data.txt"

'Select the file
Open sPath For Input As #1

Do Until EOF(1)
RowNmbr = RowNmbr + 1
Line Input #1, sText
If StrChk(sText) = True Then
ReDim Preserve varOut(i)
varOut(i) = RowNmbr
i = i + 1
End If
Loop
Close #1
MsgBox "The rows:" & Chr(10) & Join(varOut, Chr(10)) & _
Chr(10) & "of the text file are correct"
End Sub

The messagebox will show you the row numbers with correct data.


Regards
Claus B.
--
Windows10
Office 2016
  #14   Report Post  
Old May 20th 19, 12:19 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,084
Default Tricky regular expression

Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT
to dump the file contents into an empty sheet. I wanted my code to be as
compact, fast, and non-intrusive as possible.


So use the array solution instead (that's why I included it), and process a
file as Claus suggests!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #15   Report Post  
Old May 20th 19, 12:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,084
Default Tricky regular expression

Hi Robert,

Am Sun, 19 May 2019 11:06:28 -0700 (PDT) schrieb RG III:

Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT
to dump the file contents into an empty sheet. I wanted my code to be as
compact, fast, and non-intrusive as possible.

The code that I posted earlier should have worked, and it fits my needs, but
for some reason the OpenTextFile and ReadLine combination somehow taints the
file string and prevent's Claus's solution from working.


try:

Function StrChk(myStr As String) As Boolean
Select Case Asc(Left(myStr, 1))
Case 97 To 122
Select Case Asc(Right(myStr, 1))
Case 48 To 122
If InStr(myStr, " ") 0 Then StrChk = True
End Select
End Select
End Function

Sub ParseFile()
Dim n As Integer, i As Integer, RowNmbr As Integer
Dim varOut() As Variant
Dim sText As String

Const sPath = "G:\Data\data.txt"

'Select the file
Open sPath For Input As #1

Do Until EOF(1)
RowNmbr = RowNmbr + 1
Line Input #1, sText
If StrChk(sText) = True Then
ReDim Preserve varOut(i)
varOut(i) = RowNmbr
i = i + 1
End If
Loop
Close #1
MsgBox "The rows:" & Chr(10) & Join(varOut, Chr(10)) & _
Chr(10) & "of the text file are correct"
End Sub

The messagebox will show you the row numbers with correct data.


Regards
Claus B.


Claus,
Loading the entire file into an array will be orders-of-magnitude faster!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #16   Report Post  
Old May 21st 19, 10:55 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 64
Default Tricky regular expression

Thank you Claus and Garry for your assistance. I'm still experimenting with both of your examples, but I'll let you know if I have any more questions.

Thanks again!


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
Another regular expression question Robert Crandal[_2_] Excel Programming 5 February 2nd 13 12:44 PM
Can someone help me with this regular expression? [email protected] Excel Discussion (Misc queries) 3 March 10th 09 08:36 PM
Regular Expression Conditionals (?(if)then|else) in VBA? Lazzaroni Excel Programming 7 October 8th 08 09:53 PM
Help with regular expression PO Excel Programming 3 May 2nd 07 01:39 PM
Regular Expression sl Excel Discussion (Misc queries) 2 January 24th 07 12:57 AM


All times are GMT +1. The time now is 07:39 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017