Do Util End of Document is not working

[MrMilr]

Hello everyone: I’ve been in IT forever but I just recently started using VBA for MS Word and I am having just a bit of trouble and I would be delighted if you could help. I have a VBS script that quires Active Directory for computers that have not been used in 21 days or more. Normally this file is under 100 lines but the exact number of Non-Reporting Computers varies each day. The Visual Basic Script writes the Output to a TXT file. Every day I need to copy the output file into a Outlook document (MS Word) and email it to about 5 or 6 people. I have been running short Macros to remove lines with “Retired” in them and to move Lines with the word “ACE” to the top of the list but that is taking longer to do each day and I feel like I should automate the process..

Output from the VBS text file looks like the following example.

Sample of Non-Reporting Computers for August 31, 2012

# DATE SN Days Note
1 JULY 7 2012 2ua8144d21 21 ace3
2 JUNE 30 2012 CMD1234TRE 22 New Town
3 JUNE 30 2012 CMD1234TRE 24 New Town
4 JULY 8 2012 234ABCDx21 22 ACE3
Sorry the document has these items arranged on columns but I'm unable to reproduce it here

The code I have so far follows this but it does not exit at EOF and produces an endless loop. The second problem is I would like to use a Variable to call additional subs without having the repeat the Do Loop (everything I have tried so far will not compile) and maybe using a variable to call a Sub routines is forbidden .

Perhaps I am just getting old or maybe I am blind but I don’t know why the loop is not ending at End of Document. Any help will be greatly appreciated.


Sub LoopThroughDoc () ‘loops through until EOF

Selection.HomeKey Unit:=wdStory
Selection.HomeKey Unit:=wdLine


Do Until ActiveDocument.Bookmarks("\Sel").Range.End = _
ActiveDocument.Bookmarks("\EndOfDoc").Range.End
Selection.MoveDown
RemoveRetire ‘ go Sub - I would like to use a variable here to call outer Subs something like SubVariable = RemoveRetire Or MoveACE
Loop

MsgBox ("the end of the document")

End Sub
___________________________________________________________________________
Sub RemoveRetire() ‘ Sub to Remove the line with “Retired” in it

Selection.Find.ClearFormatting
With Selection.Find
.Text = "retired"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.EndKey Unit:=wdLine
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
End Sub
_______________________________________________________________________

 

[PHV]

As the file is a .TXT, why not use the native VBA instructions like Open, Line Input and Close ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

In Word, assuming the lines contain, but don't start with 'Retired' or 'retired', you can delete the 'Retired/retired' lines throughout the document is one pass with:

With ActiveDocument.Content.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Text = "[!^13]@[Rr]etired*[^13]"
  .Replacement.Text = ""
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchAllWordForms = False
  .MatchSoundsLike = False
  .MatchWildcards = True
  .Execute Replace:=wdReplaceAll
End With

No loops, etc required.

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

And here's a sub to sort the ACE/ace records:

Sub SortRecords()
Application.ScreenUpdating = False
Dim StrIn As String, StrTmp As String, StrHdr As String
Dim StrACE As String, StrOth As String, i As Long, j As Long
With ActiveDocument.Range
  'Get the document's text
  StrIn = .Text
  'Clean up any duplicate breaks
  While InStr(StrIn, vbCr & vbCr) > 0
    StrIn = Replace(StrIn, vbCr & vbCr, vbCr)
  Wend
  StrIn = vbCr & StrIn
  j = UBound(Split(StrIn, vbCr))
  For i = 1 To j
    StrTmp = Split(StrIn, vbCr)(i)
    'Put the ACE/ace strings into one stream; the remainder into another stream
    If Right(StrTmp, 4) Like "[Aa][Cc][Ee][0-9]" Then
      StrACE = StrACE & StrTmp & vbCr
    ElseIf Right(StrTmp, 4) = "Note" Then
      StrHdr = StrTmp & vbCr
    Else
      StrOth = StrOth & StrTmp & vbCr
    End If
  Next
  'Ouput the list
  .Text = StrHdr & StrACE & StrOth
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[MrMilr]

Thank you for your wonderful and eloquent examples - exactly what I needed. It is obvious I have a lot to learn an if you could recommend a text book I would spend the time with it to avoid bothering you again.

The people I work with are remarkably deserve in there literary abilities but their abilities to follow precise instructions are less than stellar and they will annotate the records with "Retire", "retired", and "RETIRED" and a few spellings that are very innovative. (I correct the misspellings in the records as I find them so they are not an issue) I ran your excellent sub against the full record but it missed "RETIRED" I modified the code with #4E9A06]#4E9A06].Text = "[!^13]@[Rr]ETIRED*[^13]" and ran a second pass through the document and that got all of the remaining uppercase examples out. If there a better way to take care of the lines containing all uppercase "RETIRED"? Would entering the remaining letters in "retire" like you have for the first letter (ie [Rr] [Ee] etc) work?

The SortRecords Sub is not working when I run it. That is my fault and I apologize - below is a better example of the Word data after it is pasted in. As you can see by the example the columns are tab separated (Word interprets it that way) and the word "ACE" can be found anywhere in the Notes Column but is only in the notes of the target line. (I can edit the database to change that if necessary for efficiency).

Non-Reporting Computers for August 31, 2012 'this is the title

# [tab] DATE [Tab] [tab] [tab] [tab] SN [tab] [tab] [tab] Days [tab] Note ' and heading
1 [tab] [JULY 2 2012 [tab] [tab] 2ua8144dff [tab] 19 [tab] Room 228 TACS computer
2 [tab] [JULY 7 2012 [tab] [tab] 2ua8144d21 [tab] 21 [tab] ACE 3 first computer deployed
3 [tab] JUNE 30 2012 [tab] [tab]CMD1234TRE [tab] 22 [tab] Desktop - Ace 3 - room 299
4 [tab] JUNE 30 2012 [tab] [tab] CMD1234TRE [tab] 24 [tab] New Town
5 [tab] JULY 8 2012 [tab] [tab] [tab] 234ABCDx21 [tab] 22 [tab] ACE3 Second deployed PC

On any given day there could be a few to a dozens of lines with the notation "ACE" and I would like all of these to go to the top of the document since the people reading this need to take care of these first.

You are using elements I have never seen before and I am trying understand. Below I've inserted my comments in red with what I understand or think I do. I will eventually work with it enough that I do understand but for now, if you have the time, could you explain the logic in a little more detail.

Again - I greatly appreciate what you have provided me. It is enough to get me started.
Milt

j = UBound(Split(StrIn, vbCr))'this is creating an array I think
For i = 1 To j
StrTmp = Split(StrIn, vbCr)(i) 'spliting the line into array cells
'Put the ACE/ace strings into one stream; the remainder into another stream
If Right(StrTmp, 4) Like "[Aa][Cc][Ee][0-9]" Then 'locating the row with ACE in it
StrACE = StrACE & StrTmp & vbCr
ElseIf Right(StrTmp, 4) = "Note" Then
StrHdr = StrTmp & vbCr
Else

 

[MrMilr]

Thank you for your wonderful and eloquent examples - exactly what I needed. It is obvious I have a lot to learn an if you could recommend a text book I would spend the time with it to avoid bothering you again.

The people I work with are remarkably deserve in there literary abilities but their abilities to follow precise instructions are less than stellar and they will annotate the records with "Retire", "retired", and "RETIRED" and a few spellings that are very innovative. (I correct the misspellings in the records as I find them so they are not an issue) I ran your excellent sub against the full record but it missed "RETIRED" I modified the code with #4E9A06]#4E9A06].Text = "[!^13]@[Rr]ETIRED*[^13]" and ran a second pass through the document and that got all of the remaining uppercase examples out. If there a better way to take care of the lines containing all uppercase "RETIRED"? Would entering the remaining letters in "retire" like you have for the first letter (ie [Rr] [Ee] etc) work?

The SortRecords Sub is not working when I run it. That is my fault and I apologize - below is a better example of the Word data after it is pasted in. As you can see by the example the columns are tab separated (Word interprets it that way) and the word "ACE" can be found anywhere in the Notes Column but is only in the notes of the target line. (I can edit the database to change that if necessary for efficiency).

Non-Reporting Computers for August 31, 2012 'this is the title

# [tab] DATE [Tab] [tab] [tab] [tab] SN [tab] [tab] [tab] Days [tab] Note ' and heading
1 [tab] [JULY 2 2012 [tab] [tab] 2ua8144dff [tab] 19 [tab] Room 228 TACS computer
2 [tab] [JULY 7 2012 [tab] [tab] 2ua8144d21 [tab] 21 [tab] ACE 3 first computer deployed
3 [tab] JUNE 30 2012 [tab] [tab]CMD1234TRE [tab] 22 [tab] Desktop - Ace 3 - room 299
4 [tab] JUNE 30 2012 [tab] [tab] CMD1234TRE [tab] 24 [tab] New Town
5 [tab] JULY 8 2012 [tab] [tab] [tab] 234ABCDx21 [tab] 22 [tab] ACE3 Second deployed PC

On any given day there could be a few to a dozens of lines with the notation "ACE" and I would like all of these to go to the top of the document since the people reading this need to take care of these first.

You are using elements I have never seen before and I am trying understand. Below I've inserted my comments in red with what I understand or think I do. I will eventually work with it enough that I do understand but for now, if you have the time, could you explain the logic in a little more detail.

Again - I greatly appreciate what you have provided me. It is enough to get me started.
Milt

j = UBound(Split(StrIn, vbCr))'this is creating an array I think
For i = 1 To j
StrTmp = Split(StrIn, vbCr)(i) 'spliting the line into array cells
'Put the ACE/ace strings into one stream; the remainder into another stream
If Right(StrTmp, 4) Like "[Aa][Cc][Ee][0-9]" Then 'locating the row with ACE in it
StrACE = StrACE & StrTmp & vbCr
ElseIf Right(StrTmp, 4) = "Note" Then
StrHdr = StrTmp & vbCr
Else

 

[macropod]

Hi Milt,

To delete any line with retire(d) in any upper/lower-case combination, change:
.Text = "[!^13]@[Rr]etired*[^13]"
to:
.Text = "[!^13]@[Rr][Ee][Tt][Ii][Rr][Ee]*[^13]"

As for the sorting, what character(s) are used to separated the fields (eg multiple spaces, tabs)? If spaces, what is the minimum # to separate 'Days' from 'Note'.

FWIW:
j = UBound(Split(StrIn, vbCr)) finds how many paragraph breaks there are in the StrIn variable
StrTmp = Split(StrIn, vbCr)(i) gets the ith paragraph from the StrIn variable

Cheers
Paul Edstein
[MS MVP - Word]

 

[MrMilr]

Thanks again – I got a compile error “too complex” originally but I shortened the code to .Text = "[!^13]@[Rr][Ee][Tt][Ii][Rr]*[^13] and it now works fantastic. The sort on “ACE” is still giving me some trouble. Stepping through the code I found it never goes to the “ELSE” statement and so there is noting in the .text at the end. I’ve been playing with it but had to stop at 4 am to go to work. I will keep playing – this has been a good learning experiance.
Milt


x = InStr(StrTmp, "ACE") 'finds the position of the word ACE

If Right(StrTmp, x) Like "[Aa][Cc][Ee]" Then 'locating the row with ACE in it
StrACE = StrACE & StrTmp & vbCr
MsgBox StrACE
ElseIf Right(StrTmp, x) = "ACE" Then
StrHdr = StrTmp & vbCr
Else
StrOth = StrOth & StrTmp & vbCr

 

[macropod]

I realise the 'ACE' part of the code doesn't work, but that's because I wrote the code to suit what you described in your first post and that is substantially different to what you've since said the data look like. That's also why in my last post I asked what the separators are - which you still haven't answered.

Cheers
Paul Edstein
[MS MVP - Word]

 

[MrMilr]

I really do appreciate your help here and was not trying to be critical in anyway. I am learning a lot in the short time I've had to work with VBA and you have helped me immensely.

I am not absolutely sure but I think the columns are separated by spaces. The original data files is .txt in Notepad. It is copied and pasted into a word document (that Outlook Open). There are 8 spaces between the number on and the first digit of the serial number (SN) - between serial number and date there are 6 spaces - between date and Days 5 - between days and Comments there are 9.

In the example below "ACE" starts in position 70 in line one. Line two it is in position 86.

Thank you again for all of your help it is truly a treat to learn from a master.
Milt

Non-Reporting Computers for 9/4/2012 1:59:00 PM
__________________________________________________________________________________

#. Serial Number Last Logon Date/Time Days Comment

1 2UB2230xRS 7/20/2012 7:31:05 PM 46 - Desktop - ACE 3 - Teutonia Station
2 2UA2340VvH 8/10/2012 9:09:47 PM 25 LAKELAND Workstation FY12 - 4000 PRO Desktop - ACE 3

 

[macropod]

Milt: You cannot reasonably expect anyone to come up with a solution if you keep shifting the goal posts. And this is the third time you have done so.

In your previous posts, you indicated that the heading row ended with the word 'Note'. Your latest post has it ending with 'Comment'. The code I posted for the sorting specifically looked to 'Note' to indicate the heading row.

As for not knowing what the separators are, finding that out is not rocket science. Simply open the file in Word and see what the characters are. I am not interested in the position of 'ACE' but in the separator content. Your latest post also has a hyphen immediately before whatever character precedes the 'ACE'. Is that always the case?

Cheers
Paul Edstein
[MS MVP - Word]

 

[MrMilr]

I am not intentionally shifting the goal post. The person that controls the VB script recently changed the word Note for Comment. The examples today are from his latest effort. The Active Directory Database is not consistent, most of the entries will have the dash. but the database is edited by a lot of people and they are liable to do almost anything. The Database has nearly 12000 entries in it but only about 2000 have the word “ACE”. I have found and replaced the variations of the word “ACE” in the database so that it is always in upper case. I can also edit the Database to either include the dash or not.

The example in today’s post is from the text file I received today. I am very sorry that this is flustering and I understand if you want to stop. I have been trying to talk the boss into letting me have exclusive control for consistencies sake and I think he will let me have it soon.

I have tried to post exact example text into post but the spaces are apparently automatically compress.
Milt

 

[macropod]

So how about we wait until things have stablised and you know for sure what the data will look like and, above all, what the field separator characters are.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

@MrMilr,

I've worked in a projects environment for over 30 years. Unless you get a written statement of work, even it it's only an eMail that is implicitly agreed to (kind of weak IMNSHO) hitting the target for your customer, will be like nailing jello to the wall.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}