Find a Specific word in doc file using VBA

Priyantha Gamini · #1 12-08-2022, 03:54 AM

Dear All,

I received a word file (System generated) & I want to find the word "Grand Total" using "Find Function". After that, the word/Frame in front of that word (Grand Total) should be selected & that word/Frame should be defined as a variable to use in other activities. (finally, I hope to copy that amount into an excel sheet.) my file is attached here.

Help me please

Thanks,

Priyantha.

macropod · #2 12-08-2022, 02:35 PM

Because your document has been produced by a process that uses frames to position the text instead of placing the text directly onto the page, there is no 100% reliable way of doing this. That said, the following seems to work with your attachment.

Code:

Sub GetGrandTotals()
Dim Sctn As Section, Frm As Frame, x As Long, v As Single
With ActiveDocument
  For Each Sctn In .Sections
    With Sctn.Range
      v = 0
      For x = 1 To .Frames.Count
        With .Frames(x)
          If .Range.Text = "Grand  Total" Then
            v = .VerticalPosition - 7.25: Exit For
          End If
        End With
      Next
      For x = 1 To .Frames.Count
        With .Frames(x)
          If .VerticalPosition = v Then MsgBox .Range.Text: Exit For
        End With
      Next
    End With
  Next
End With
End Sub

Guessed · #3 12-08-2022, 02:47 PM

Your sample document is full of framed text all anchored to the same paragraph so the concept of 'in front of' is not exactly clear in vba terms. However since your content is created through automation the order of the framed paragraphs should be consistent.
Looking at the order of the content, which of these paragraphs is the variable you want to use?
EMP No.
Mr. R.P.A.S. Abeywickrama
30662
20.00
Grand Total
Does 'Grand Total' always have two spaces between the words?

Priyantha Gamini · #4 12-09-2022, 07:51 PM

Thaks for all to attending my issue. I will discribe my isuu again.I received a merge word document contains a description of member fee charges related to the trade union. I use a macro to seperate PDF fils belongs to each union. I want to create a summary related to those trade unions in an excel sheet (with Grand Total belongs to Trade union).

I want to get Grand total value to this variable.
My variable :

sTotal = docNew.Frames(21).Range.Text

This frame(21) works correctly For one page union (pdf file) only.i am a beginner for VBA. can not use Selection.Goto /Selection.Move function for this.

Dear macropod, thank your answar i will try it.

Dear Guessed, thanks, i hope to capture Grand Total value to my variable. "Grand Total" should one space between word. I can be corrected in system report.

Thanks,

Priyantha

Priyantha Gamini · #5 12-13-2022, 01:32 AM

Dear Macropod,

I tried your code & it is working. But some values do not copy (only part of the text) to my excel sheet (Summary).

This is my Code :

Code:

Sub Split_Thirdparty_Letters_pdf()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim xlRng As Object
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim lngDocNum As Long
    Dim docOld As Document
    Dim docNew As Document
    Dim sText As String
    Dim sText2 As String
    Dim sTotal As String
    Dim sDirectory As String

    On Error Resume Next
    Set xlApp = GetObject(Class:="Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject(Class:="Excel.Application")
    End If
    On Error GoTo 0

    ' Add path to workbook if necessary
    Set xlWbk = xlApp.Workbooks.Open("C:\Users\User\Desktop\Thirdparty Payments\Thirdparty Remitance.xlsm")
    Set xlWsh = xlWbk.Worksheets("Thirdparty")
    Set docOld = ActiveDocument
    lngStart = 1
    selection.HomeKey Unit:=wdStory
    With selection.Find
        .ClearFormatting
        .Text = "Grand *^12"
        .MatchWildcards = True
        .Wrap = wdFindStop
        Do While .Execute
            selection.Collapse Direction:=wdCollapseEnd
            lngEnd = selection.End
            ' Copy the "section"
            docOld.Range(lngStart, lngEnd - 1).Copy
            'Create a new document to paste text from clipboard.
            Set docNew = Documents.Add
            selection.Paste
            lngDocNum = lngDocNum + 1

            sText = docNew.Frames(8).Range.Text
            sDirectory = "C:\Users\User\Desktop\Thirdparty Payments\Thirdparty Letters"
            
            
            Dim Sctn As Section, Frm As Frame, x As Long, v As Single
            With ActiveDocument
              For Each Sctn In .Sections
                With Sctn.Range
                  v = 0
                  For x = 1 To .Frames.Count
                    With .Frames(x)
                      If .Range.Text = "Grand Total" Then
                        v = .VerticalPosition - 7.25: Exit For
                      End If
                    End With
                  Next
                  For x = 1 To .Frames.Count
                    With .Frames(x)
                     
                      If .VerticalPosition = v Then sTotal = .Range.Text: Exit For
                    End With
                  Next
                End With
              Next
            End With
            
            
            'sTotal = docNew.Frames(21).Range.Text
            Set xlRng = xlWsh.Range("B:B").Find(What:=sText, LookAt:=1, MatchCase:=False)
            
             ''Debug.Print "Third party code was not found''
            sText2 = docNew.Frames(10).Range.Text
            If xlRng Is Nothing Then
            MsgBox "THIRD PARTY CODE :" & sText & "  " & sText2 & ", NOT IN EXCEL SHEDULE"
            End If
                        
            If Not xlRng Is Nothing Then
               xlRng.Offset(0, 5).Value = Val(sTotal)
            End If
            
            '''Convert to PDF'''
            docNew.ExportAsFixedFormat OutputFileName:=sDirectory & Trim(sText) & ".pdf", ExportFormat:=17
            docNew.Close SaveChanges:=False ''close the new document'''

            ' set new start
            lngStart = lngEnd + 1
        Loop
    End With

    ' Last part
    selection.Collapse Direction:=wdCollapseEnd
    lngEnd = ActiveDocument.Content.End
    ' Copy the "section"
    docOld.Range(lngStart, lngEnd - 1).Copy
    'Create a new document to paste text from clipboard.
    Set docNew = Documents.Add
    selection.Paste
     ' Save the new document
    lngDocNum = lngDocNum + 1

    '''Convert to PDF'''
    sText = docNew.Frames(8).Range.Text
    
    
        With ActiveDocument
        For Each Sctn In .Sections
        With Sctn.Range
          v = 0
          For x = 1 To .Frames.Count
            With .Frames(x)
              If .Range.Text = "Grand Total" Then
                v = .VerticalPosition - 7.25: Exit For
              End If
            End With
          Next
          For x = 1 To .Frames.Count
            With .Frames(x)
              If .VerticalPosition = v Then sTotal = .Range.Text: Exit For
            End With
          Next
        End With
      Next
    End With
    
    'sTotal = docNew.Frames(21).Range.Text
    Set xlRng = xlWsh.Range("B:B").Find(What:=sText, LookAt:=1, MatchCase:=False)
    
    ''Debug.Print "Third party code was not found.",,
    sText2 = docNew.Frames(10).Range.Text
    If xlRng Is Nothing Then
    MsgBox "THIRD PARTY CODE :" & sText & "  " & sText2 & ", NOT IN EXCEL SHEDULE"
    End If
    
    If Not xlRng Is Nothing Then
        xlRng.Offset(0, 5).Value = Val(sTotal)
    End If

    docNew.ExportAsFixedFormat OutputFileName:=sDirectory & Trim(sText) & ".pdf", ExportFormat:=17
    docNew.Close SaveChanges:=False ''close the new document'''

    xlWbk.Close SaveChanges:=True
End Sub

I have attached 02 files here. Please help me.

Thanks,

Priyantha.

macropod · #6 12-13-2022, 02:20 PM

As I said:

Quote:

Originally Posted by macropod

Because your document has been produced by a process that uses frames to position the text instead of placing the text directly onto the page, there is no 100% reliable way of doing this.

Priyantha Gamini · #7 12-13-2022, 08:13 PM

Quote:

Originally Posted by macropod

As I said:

Can I find the frame index number included Grand Total

help me.

Thanks,

Priyantha

macropod · #8 12-14-2022, 05:18 AM

Cross-posted at: Help modify code to split & save document at section breaks - Page 2 - Eileen's Lounge
For cross-posting etiquette, please read: A message to forum cross posters - Excelguru

Quote:

Originally Posted by Priyantha Gamini

Can I find the frame index number included Grand Total

Yes but that, too, doesn't provide a reliable way of identifying the corresponding value - even with your sample document.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word is not being able to find/replace a specific color	jaumzaum	Word	3	07-08-2021 09:09 AM
Macro to check the existence of a word docx file and create a new word file with specific content.	staicumihai	Word VBA	14	11-15-2016 01:42 AM
needles and haystacks – how to find that one .doc file containing a specific word? Windows 7	eNGiNe	Windows	7	04-02-2015 05:05 AM
needles and haystacks – how to find that one .doc file containing a specific word? Windows 7	eNGiNe	Word	0	04-01-2015 11:15 PM
How to find a specific word in Powerpoint?	Ozard80	PowerPoint	0	05-11-2011 04:42 PM