Getting a line number from a word document 2

[glarior]

Hi,

My macro is utilizing a find text command and when it finds the text how do I find out what line number the text is on and than save it to a variable for later use?

I have done some searching on this but still shooting in the dark so if this has been posted elsewhere please send me a URL, or if you have other information that may be useful to me please send me that link as well.

Also let me know if I need to be more descriptive.

Thanks

Glarior

 

[macropod]

Hi glarior,

Here's some code to get you started:

Sub GetPos()
MsgBox Selection.Information(wdFirstCharacterLineNumber)
End Sub

Cheers

 

[glarior]

Thanks I will put it to some good use.

After I get the line number, how do I make the program select all the text between my two line numbers. Could I use something like this
Range("variable1:variable2").select (I would have to play with this more, I am new to VBA so I really thank you)
I am not sure about that but I am going to get back on my computer and give it a try.

Thanks again for the help.

Glarior

 

[macropod]

Hi Glarior,

Yes, you could do something like that, but I wonder why. If you know what the text is, you don't need to know the line numbers to select it - and in many cases it's also more efficient to act on the text without selecting it. And, what would you do if the text you wanted to select started part way along one line and ended part way along another?

Cheers

 

[fumei]

macropod, as usual, as very valid points.

You need to determine precisely your needs.

1. what if the text in the middle of the third line of a paragraph? do you want tyo select the whole line including the text after the search word?

2. Do you want to select the text between the word regardless of lines?

3. do you want to select the text for the lines that have the text, but only to where the text is.

Better specs, better code. Again, you must determine exactly what you want to happen. While you may think you have expressed what you want, there are still possible outocme that you have not stated. here are some samples of possible choices. One selectes the text betwen inptu strings - in this case I randomly put in the words "here" and "there" in a document/ Adjust this fotr your needs, but more importantly....figure out exactly what your needs are.

Option Explicit
Sub GetRangedSelection()
Dim response
Dim msg As String
msg = "Select YES if you want the range of the LINES that have " _
    & "the search text, or NO if you want the range of the actual text " _
    & "between the search text"

response = MsgBox(msg, vbYesNo)
If response = vbYes Then
    BetweenTextAsLines "here", "there"
Else
    BetweenTextAsText "here", "there"
End If

End Sub
Sub BetweenTextAsLines(sStart As String, sEnd As String)
Dim SearchRangeStart As Long
Dim SearchRangeEnd As Long
Dim SearchLineNumberA As Integer
Dim SearchLineNumberB As Integer
Selection.HomeKey unit:=wdStory
With Selection.Find
' find the first text string
' get line number
    If .Execute(findtext:=sStart, Forward:=True) = True Then
            SearchLineNumberA = Selection.Information(wdFirstCharacterLineNumber)
'        SearchRangeStart = Selection.Range.Start
    End If
' must clear the search critieria
    .ClearFormatting
    Selection.Collapse direction:=wdCollapseEnd
' find the second string
' get line number
    If .Execute(findtext:=sEnd, Forward:=True) = True Then
        SearchLineNumberB = Selection.Information(wdFirstCharacterLineNumber)
''        SearchRangeEnd = Selection.Range.End
    End If
End With
Selection.Collapse direction:=wdCollapseEnd
' go to first line number and pick up start interger
    Selection.GoTo What:=wdGoToLine, _
            Which:=wdGoToFirst, Count:=SearchLineNumberA, Name:=""
        SearchRangeStart = Selection.Range.Start
' go to second line line and pick up end integer
Selection.Collapse direction:=wdCollapseEnd
    Selection.GoTo What:=wdGoToLine, _
            Which:=wdGoToFirst, Count:=SearchLineNumberB + 1, Name:=""
        SearchRangeEnd = Selection.Range.Start - 1
' set SetRange to get the range between
Selection.SetRange Start:=SearchRangeStart, End:=SearchRangeEnd
End Sub

Sub BetweenTextAsText(sStart As String, sEnd As String)
Dim SearchRangeStart As Long
Dim SearchRangeEnd As Long

Selection.HomeKey unit:=wdStory
With Selection.Find
' find the first text string
' set range integer
    If .Execute(findtext:=sStart, Forward:=True) = True Then
        SearchRangeStart = Selection.Range.Start
    End If
' must clear the search critieria
    .ClearFormatting
    Selection.Collapse direction:=wdCollapseEnd
' find the second string
' set range end integer
    If .Execute(findtext:=sEnd, Forward:=True) = True Then
        SearchRangeEnd = Selection.Range.End
    End If
End With
Selection.Collapse direction:=wdCollapseEnd
' set SetRange to get the range between
Selection.SetRange Start:=SearchRangeStart, End:=SearchRangeEnd
End Sub

Gerry

 

[glarior]

Thanks for the input I will see what I can do with it today.

The reason I am doing this is we have this big word document with a listing of each sink we have. Well each sink is ended by a page break. I thought it would be good (plus was the only idea I had to try and implement) to search the document for that sink and find where it ends than select the text in between and paste it on a new document and keep doing that for each sink.

I know there may be more efficient ways to do it and I hope to find some. Your input has be wonderful, and I do appreciate the help you have given me.

Charles

 

[fumei]

The best way, if this is going to be an ongoing thing, is to use bookmarks.

Bookmarks can be ANY range of text. I assume you are speaking literally of sinks, so let's say you have:

Sink_TypeA...and text, then page break.
Sink_TypeB...and text, then page break.

etc. etc.

Select all the text for each sink, and make it a bookmark. Insert > Bookmark, and give it the name of the sink. Now the bookmark (for example Sink_TypeA) can be handled directly.

In its simplest form, if you want to grab all the text for Sink_TypeA and copy it to a new document:

Sub CopyBookmarkNewDoc()
With Selection
    .GoTo what:=wdGoToBookmark, Name:="Sink_TypeA"
    .Copy
End With
    Documents.Add
    Selection.Paste
End Sub

With a bit of effort you could, in fact, build some code to create those bookmarks. Since you state each chunk has a page break, you can use that fact to generate your bookmarks.

Again, if you clearly determine the requirements I am positive there is a fairly easy solution.

Gerry

 

[macropod]

Hi Gerry,

I agree. In fact, using section breaks instead of page breaks would make things even easier if the doco for a sink exceeds one page, since one would be able to simply copy the current section. Of course, if each sink occupies no more than one page, one could just as easily copy the current page ...

Cheers

 

[glarior]

Thanks macropod and fumei!!

Great idea on the page break, I never thought about using that. I wish each page was no more than one page cause I could just copy the current page like you said macropod. Well I will go and see what I can do with this new information. Thanks again

Charles

 

[fumei]

As macropod mentions - and he/she/it (couldn't help it....) does know a thing or two, if you replace the page breaks with Section breaks, then this is another way to be able to retrieve the text for specific chunks.

Though you would have to be careful that there are no manual page breaks that are IN the chunk belonging to a specific sink. Here is some code that will go through a document and replace page breaks (manual page breaks) with Section breaks (next page break).

NOTE: you may think that you could do a Find and Replace, replacing manual page breaks (^m) with a Section break (^b). However, while Find can use a ^b to find a Section break, Replace can NOT use a ^b to replace something with a Section break. This is because while ^b can apply to finding the various type of Sections breaks (they are all section breaks), Replace does not know which type to use, so it refuses to use any. Therefore when found, the code deletes the selection, and inserts a Section break by code, rather than a replace operation.

Sub ReplacePageBreaksWithSectionBreaks()
With Selection
  .HomeKey Unit:=wdStory
  With .Find
    .ClearFormatting
    .Text = "^m"
    Do While (.Execute(Forward:=True) = True = True)
       With Selection
         .Delete
         .InsertBreak Type:=wdSectionBreakNextPage
       End With
    Loop
  End With
End With
End Sub

Gerry

 

[macropod]

he/she/it (could be any one or more ...)

 

[glarior]

THANKS for all of the help.

I got the program working great and fumei you were right, "...I am positive there is a fairly easy solution."
Instead of making my program do that search term idea I had, I used what yall were talking about on the bookmarks. Made the code simple, to the point and works great.

But then yall mentioned section breaks. I wonder if that can speed up the process or have my macro run better? Plus its a great way to learn more about VBA for future reference. So what is the difference between using section breaks vs bookmarks?

I may have the answer by the time someone replies but still would like to know yours.

thanks for all the helps once again

charles

 

[glarior]

I would like your input on this on how you would go about it.

any ideas on a faster way to write something that if I check a weekly or monthly check box it will go through my master document and only get a weekly or monthly part.

for ex:

Sink18
weekly
1.check connections
2.check pressure
monthly
1.check ph
2.check for leaks

if they choose weekly it will only get the weekly PM or if they checked boxed monthly it will get both.

If I use bookmarks there is going to be quite a few of them and I know there has to be a more efficent way to do it. I am currently working on it, I love doing this stuff but still new to VBA, I have only been doing VBA for a week and half now thanks again

Charles

 

[glarior]

Here is what I figured out to work to get the weekly or monthly etc etc...
So far everything works like I want it to but still have odd and end things to fix and not to mention try to make it more efficient thanks eveyone for your help

Private Sub SinkInfo(sink)
    sink = ComboBox1.Text           'get sink selection    Application.ScreenUpdating = False
    If CheckBox1 = True Then        'for weekly
        Check1 = "Monthly"
    ElseIf CheckBox4 = True Then    'for monthly
        Check1 = "Quarterly"
    ElseIf CheckBox5 = True Then    'for quaterly
        Check1 = "Semi"
    ElseIf CheckBox3 = True Then
        Check1 = "Annual"           'for semi annual
    ElseIf CheckBox2 = True Then
        Check1 = ""
    Else
        
    End If
'store my data in an array depending on what sink they chose
    If sink = "18Sink" Then
            sinkArray = Array("Sink1", "Sink2")
        ElseIf sink = "19Sink" Then
            sinkArray = Array("SC1", "SC2")
        ElseIf sink = "20Sink" Then
            sinkArray = Array()
        ElseIf sink = "21Sink" Then
            sinkArray = Array()
'there are more sinks but i edited that part out for this forum
    Else
        MsgBox "error"
        Exit Sub
    End If
    For i = 0 To UBound(sinkArray)
        Documents.Open FileName:=("H:\Spec Master.doc")
        Selection.HomeKey unit:=wdStory
        With Selection
            .GoTo what:=wdGoToBookmark, Name:=sinkArray(i)
            .Copy
            Documents.Open FileName:=("H:\testing_paste.doc")
            Selection.Paste
        End With
        
'this checks to see what type of PM it is and than finds the what type it is (weekly, monthly, annual etc) and then deletes it

        If Check1 <> "" Then
        Selection.HomeKey unit:=wdStory, Extend:=wdExtend
        With Selection.Find
            .ClearFormatting
            .MatchWholeWord = True
            .MatchCase = False
            .Execute findtext:=Check1
            
        End With
        Selection.Find.Execute
        Selection.EndKey unit:=wdStory, Extend:=wdExtend
        Selection.Delete unit:=wdCharacter, Count:=1
        Else
        End If
'repeat loop till done
    Next


End Sub

If you have any ideas how I can make it better by all means let me know. Also there is some code missing but this is the main process of it.

 

[fumei]

macropod - oh really? How..........hmmmmmmmmm, ......delightfully enticing.

glarior,

1. You do not explicitly declare your variables. I strongly recommend you use Option Explicit! Put that line at the top of your code module. This makes you use explicit variables. I can figure out that Check1 is a string variable...but it is better to declare it explicitly as such. If it is, then your code is going to complain if you make any spelling missings or use the variable for incorrect data type.

2. When posting, it is helpful to fully describe the situation. If I understand correctly:

You have a UserForm with a combobox, and some checkboxes.

a) - I suggest explicitly naming your objects. keeping the default names (Combobox1, Checkbox1, Checkbox2 et al) is convenient, but in the long run, giving these object actual names will make things MUCH easier for you.

b) - You have checking logic on the checkboxes. Do you want only one choice to be made? If so, put your checkboxes in a Frame on the form. Then only one can be chosen. If multiple choices (monthly AND quarterly etc etc) are what you want, please state so.

c) - the Sub has "sink" as an input parameter - Sub SinkInfo(sink). Yet the code asssign a value to this. I am a little confused by this.

d) - It may be better, or clearer, to use Select Case, instead of IF statements, in your code. For example:

Case Select sink
  Case "18Sink"
      sinkArray = Array("Sink1", "Sink2")
  Case "19Sink"
      sinkArray = Array("SC1", "SC2")
  Case "20Sink"
      sinkArray = Array()
  Case "21Sink" Then
      sinkArray = Array()
  Case  Else
      MsgBox "error"
      Exit Sub
End Select

I am not exactly sure what is going on with the last part. What is PM??

Gerry

 

[glarior]

Thanks for your reply fumei. Sorry for not explaining my program so clearly. I left out some important information

To your answers...
1.)Most of my variables are declared in a different function for when the user clicks the button I created.

2a.)As of the naming part I will keep that in mind cause I know you are right about that. Mine is a bad habit.

2b.) I would like to only make it where the user can check one box and no more. I will read about the frame thing and see what that does for me.

2c.) "the Sub has "sink" as an input parameter"....Sorry for the confusion that was something I played with and decided to go about that a different way and I never deleted the sink from the sub.

2d.) I knew there was a better way to all the if, and else if statements. I could not think of it but thanks for telling me.

For the last part PM stands for Preventative Maintenance on our sink machines. Each main sink has anywhere from 4 to 12 sink processes in it. To ensure our machines keep running we do different PM's through out the year.

Also on that last part.... My spec master has all the PM's for the sinks (weekly, monthly, semi, annual, etc) well When I have the macro running it copies everything for that sink using the book marks. Well instead of having tons of book marks depending on if it is a weekly, monthly, semi, annual pm I decided it would be easier just to find the next type of PM after it paste to the document and delete everything after that. That is what the check1 is for.

For example: When the code determines the user has selected a Monthly PM it will store the text "quarterly" into check1 and than after it copies and paste the data from the spec master it will do a text search for that word and delete everything after it.

I hope I answered your questions and explained what I am trying to accomplish a little better. If not just let me know and I will glady explain more.

Thanks again for your time and help

Charles

 

[glarior]

Gerry,

I started to look for more information about forms. I opened up my "Word 2000 Developer's Handbook by Guy Hart-Davis" and found what you were talking about. I have seen that done in Visual Basic. In fact I did a cheap calculator using forms years ago when I thought about getting into VB but than realized C++ was better at the time and than just stopped programming for years, until now.

Thanks for mentioning that, I have so many new ideas and things to try out. WOW, so excited.

Thanks

Charles