#1
|
|||
|
|||
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. Last edited by Priyantha Gamini; 12-08-2022 at 07:41 AM. |
#2
|
||||
|
||||
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] |
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
As I said:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 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] |
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 |
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 |