Is there a faster way of counting lines?

[Conan Kelly]

Hello all,

I'm working with plain text files of data that are in a report format: a new page after 60 (or so) lines of data with new page
headers & column headers on each new page along with summaries at the end of each section (8 or 9 sections). I need to chop out all
the summaries, page headers, footers, report headers, etc... so all I have is the data so that it can be imported into SQL Server.

I'm doing this in MS Word because I can use VBA to automate it.

I have the code to search for the category summaries and delete them. I want to keep these summaries, so I have code to write them
to another text file before deleting them.

The problem I'm having is that I would like to count the number of lines in these summaries that I'm moving/deleting.

I have come up with the following code:


Sub testCountLines()
Dim pintCounter As Integer
Dim pintIndex As Integer
Dim pdteStartTime As Date
Dim pdteEndTime As Date
Dim pdteRunTime As Date

pdteStartTime = Now()

pintCounter = 0

For pintIndex = 1 To Selection.Characters.Count
If Asc(Selection.Characters(pintIndex)) = 13 Then pintCounter = pintCounter + 1
' MsgBox "''" & Selection.Characters(pintIndex) & "''" & vbCrLf & Asc(Selection.Characters(pintIndex))
Next pintIndex

' MsgBox pintCounter
pdteEndTime = Now()
pdteRunTime = pdteEndTime - pdteStartTime

MsgBox Format(pdteRunTime, "hh:mm:ss") & vbCrLf _
& "Number of Characters: " & Selection.Characters.Count & vbCrLf _
& "Number of Lines: " & pintCounter

End Sub


The problem is that it took 3 mins 34 secs to loop through 5143 characters and count 49 lines.

Is there a faster way of doing this?

Keep in mind, yesterday someone suggested Selection.Range.ComputeStatistics(wdStatisticLines). But when I tried that, it returned
around 690 for what was actually 31 lines of text.

(These sections of these text files that I'm processing are aligned summary "Tables" (counts & sums) in the text files that are
aligned using spaces with a carriage return at the end of each line--that is why there is such a high character:lines ratio)

49 lines
5143 characters
00:03:34

 

[Greg Maxey]

Hello all,

I'm working with plain text files of data that are in a report format: a new page after 60 (or so) lines of data with new page
headers & column headers on each new page along with summaries at the end of each section (8 or 9 sections). I need to chop out all
the summaries, page headers, footers, report headers, etc... so all I have is the data so that it can be imported into SQL Server.

I'm doing this in MS Word because I can use VBA to automate it.

I have the code to search for the category summaries and delete them. I want to keep these summaries, so I have code to write them
to another text file before deleting them.

The problem I'm having is that I would like to count the number of lines in these summaries that I'm moving/deleting.

I have come up with the following code:


Sub testCountLines()
Dim pintCounter As Integer
Dim pintIndex As Integer
Dim pdteStartTime As Date
Dim pdteEndTime As Date
Dim pdteRunTime As Date

pdteStartTime = Now()

pintCounter = 0

For pintIndex = 1 To Selection.Characters.Count
If Asc(Selection.Characters(pintIndex)) = 13 Then pintCounter = pintCounter + 1
' MsgBox "''" & Selection.Characters(pintIndex) & "''" & vbCrLf & Asc(Selection.Characters(pintIndex))
Next pintIndex

' MsgBox pintCounter
pdteEndTime = Now()
pdteRunTime = pdteEndTime - pdteStartTime

MsgBox Format(pdteRunTime, "hh:mm:ss") & vbCrLf _
& "Number of Characters: " & Selection.Characters.Count & vbCrLf _
& "Number of Lines: " & pintCounter

End Sub


The problem is that it took 3 mins 34 secs to loop through 5143 characters and count 49 lines.

Is there a faster way of doing this?

Keep in mind, yesterday someone suggested Selection.Range.ComputeStatistics(wdStatisticLines). But when I tried that, it returned
around 690 for what was actually 31 lines of text.

(These sections of these text files that I'm processing are aligned summary "Tables" (counts & sums) in the text files that are
aligned using spaces with a carriage return at the end of each line--that is why there is such a high character:lines ratio)

49 lines
5143 characters
00:03:34

 

[Greg Maxey]

Conan,

Based on a simply test here with 49 lines (lines of text ending in a
paragraph mark) of text selected and no blank lines, each of these
methods returned 49:

MsgBox Selection.Range.ComputeStatistics(wdStatisticLines)
MsgBox Selection.Range.ComputeStatistics(wdStatisticParagraphs)
MsgBox Selection.Paragraphs.Count

 

[Conan Kelly]

Greg,

Thanks for the feed back.

That is trippy. On my machine wdStatisticLines returned around 690 (when 31 lines were selected), wdStatisticParagraphs returned 21
on the same selection. I didn't try Selection.Paragraphs.Count.

But also, remember these are plain text files in report format (*.txt files opened in word). Anything that is centered, right
justified, or aligned in columns is done so with spaces. In the 31 line selection above, there were some blank lines (only a
paragraph mark). So I think that wdStatisticParagraphs returned the correct number. But it appears that wdStatisticLines was
returning all caracters in the selection, counting multiple contiguous spaces as one character.

I don't know if this is just an issue on my machine/version of Word or what is going on. (MS Word 2002 (10.6818.6817) SP3).

Thanks again for all of your help,

Conan

Conan,

Based on a simply test here with 49 lines (lines of text ending in a
paragraph mark) of text selected and no blank lines, each of these
methods returned 49:

MsgBox Selection.Range.ComputeStatistics(wdStatisticLines)
MsgBox Selection.Range.ComputeStatistics(wdStatisticParagraphs)
MsgBox Selection.Paragraphs.Count

 

[Greg Maxey]

Conan,

I'm baffled. Which is not a rare thing.

Maybe you could but the selection in an array using vbCR as a delimiter
and then count the items in the array:

Sub Test()
Dim pString() As String
pString = Split(Selection.Text, vbCr)
MsgBox UBound(pString)
End Sub

Greg,

Thanks for the feed back.

That is trippy. On my machine wdStatisticLines returned around 690 (when 31 lines were selected), wdStatisticParagraphs returned 21
on the same selection. I didn't try Selection.Paragraphs.Count.

But also, remember these are plain text files in report format (*.txt files opened in word). Anything that is centered, right
justified, or aligned in columns is done so with spaces. In the 31 line selection above, there were some blank lines (only a
paragraph mark). So I think that wdStatisticParagraphs returned the correct number. But it appears that wdStatisticLines was
returning all caracters in the selection, counting multiple contiguous spaces as one character.

I don't know if this is just an issue on my machine/version of Word or what is going on. (MS Word 2002 (10.6818.6817) SP3).

Thanks again for all of your help,

Conan

 

[Helmut Weber]

Hi Conan,

yesterday someone suggested

that was Greg Maxey, not someone.

You might have to rethink all.
In Word's terminology,
plain txt-files do not contain headers
nor column headers nor lines at all,
with some disputable exceptions.

There is also no need to use the selection-object,
also with some disputable exceptions.

You don't even need a Word-document at all.

Why do you want to count characters?

For a more professional approach,
read all of the txt-file into a string,
split the string with chr(13) as delimiter,
check the length of array items.

Sure it is a bit demanding.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Conan Kelly]

Helmut,

Thank you for the feed back.

that was Greg Maxey, not someone.

I'm sorry. I didn't go back and check yesterday's post to see who replied. Didn't mean to offend anyone. I hardly ever
post/monitor this newsgroup. Mainly just the Excel ones. I'm very familiar with the Excel Gurus.


Anyways, thanks for the feed back. I'll take it into consideration.

Conan

 

[Greg Maxey]

Conan,

My hide is thick. No harm, no foul. I created a .txt file with lots
of lines, spaces, etc. and still couldn't create an errroneous line
count. The Split(Selection.Text, vbCr) method I posted earlier also
returns a matching number. Perhaps it will work in your case.

 

[Conan Kelly]

Greg,

Thank you for your help.

I don't know why those ComputeStatistics are working either.

This seems like a hokey work-around. NOT hokey meaning you are a fool for coming up with it, but hokey meaning that you should not
have HAD to come up with it--VBA's built in methods, properties, functions, etc... should have worked correctly.

It seems hokey, but it works beautifully. Accomplished exactly what I wanted it to do.

Thanks again for all of your help,

Conan Kelly

 

[Greg Maxey]

Great. Glad it works for you.

Greg,

Thank you for your help.

I don't know why those ComputeStatistics are working either.

This seems like a hokey work-around. NOT hokey meaning you are a fool for coming up with it, but hokey meaning that you should not
have HAD to come up with it--VBA's built in methods, properties, functions, etc... should have worked correctly.

It seems hokey, but it works beautifully. Accomplished exactly what I wanted it to do.

Thanks again for all of your help,

Conan Kelly