Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-08-2022, 03:54 AM
Priyantha Gamini Priyantha Gamini is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Novice
Find a Specific word in doc file using VBA
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default Find a Specific word in doc file using VBA

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.
Attached Files
File Type: doc Thirdparty.doc (94.6 KB, 9 views)

Last edited by Priyantha Gamini; 12-08-2022 at 07:41 AM.
Reply With Quote
  #2  
Old 12-08-2022, 02:35 PM
macropod's Avatar
macropod macropod is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-08-2022, 02:47 PM
Guessed's Avatar
Guessed Guessed is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 12-09-2022, 07:51 PM
Priyantha Gamini Priyantha Gamini is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Novice
Find a Specific word in doc file using VBA
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

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

Last edited by Priyantha Gamini; 12-10-2022 at 02:19 AM.
Reply With Quote
  #5  
Old 12-13-2022, 01:32 AM
Priyantha Gamini Priyantha Gamini is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Novice
Find a Specific word in doc file using VBA
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

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 :

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\Thirdp arty 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.
Attached Files
File Type: doc Thirdparty.doc (87.0 KB, 1 views)
File Type: xlsm Thirdparty Remitance.xlsm (20.5 KB, 0 views)
Reply With Quote
  #6  
Old 12-13-2022, 02:20 PM
macropod's Avatar
macropod macropod is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I said:
Quote:
Originally Posted by macropod View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-13-2022, 08:13 PM
Priyantha Gamini Priyantha Gamini is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Novice
Find a Specific word in doc file using VBA
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
As I said:
Can I find the frame index number included Grand Total

help me.

Thanks,

Priyantha

Last edited by Priyantha Gamini; 12-13-2022 at 11:31 PM.
Reply With Quote
  #8  
Old 12-14-2022, 05:18 AM
macropod's Avatar
macropod macropod is offline Find a Specific word in doc file using VBA Windows 10 Find a Specific word in doc file using VBA Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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 View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Find a Specific word in doc file using VBA 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
Find a Specific word in doc file using VBA 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft