Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2023, 06:10 PM
Big_Sugah Big_Sugah is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2016
Novice
Highlight word document with words from .txt file
 
Join Date: Jan 2023
Posts: 5
Big_Sugah is on a distinguished road
Default Highlight word document with words from .txt file

Hi Everyone,



I would like help in a script I have. This script is meant to highlight words based off a local .txt file. The word document I use has anywhere between 100-300 pages
I find the following script appears to not highlight the entire word and takes a long time to execute.
An example of the .txt document would be
Quote:
Google.com
Google.com.au
Facebook.com
Yahoo.com
etc
Is there any improvements to this script that can be performed? For increased running time and how it actually works

Code:
Sub NewWordReplacement1()
  Dim sCheckDoc As String
  Dim docRef As Document
  Dim docCurrent As Document
  Dim wrdRef As Object

sCheckDoc = "C:\Folder\Textfile.txt"
Set docCurrent = Selection.Document
Set docRef = Documents.Open(sCheckDoc)
docCurrent.Activate
Options.DefaultHighlightColorIndex = wdRed
With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Font.Bold = True
    .Forward = True
    .Format = True
    .MatchWholeWord = True
    .MatchCase = True
    .MatchWildcards = False
End With

For Each wrdRef In docRef.Words
    If Asc(Left(wrdRef, 1)) > 32 Then
        With Selection.Find
            .Wrap = wdFindContinue
            .Text = wrdRef
            .Execute Replace:=wdReplaceAll
            .Replacement.Highlight = True
        End With
    End If
Next wrdRef

docRef.Close
docCurrent.Activate
End Sub
Reply With Quote
  #2  
Old 01-18-2023, 09:46 PM
Guessed's Avatar
Guessed Guessed is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file 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

There are two obvious ways to speed up your code.
1. Avoid the selection object to reduce the need for screen scrolling
2. Search each phrase (URL) instead of each word since google.com.au is three separate words and it really should be done as a single find/replace
Code:
Sub NewWordReplacement2()
  Dim sCheckDoc As String
  Dim docRef As Document, docCurrent As Document
  Dim i As Integer, arrFind() As String, sFind As String

  sCheckDoc = "C:\Folder\Textfile.txt"
  Set docCurrent = ActiveDocument
  Set docRef = Documents.Open(sCheckDoc)
  arrFind = Split(docRef.Range.Text, vbCrLf)
  docRef.Close
  Options.DefaultHighlightColorIndex = wdRed
  With docCurrent.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Font.Bold = True
    .Replacement.Highlight = True
    .Forward = True
    .Wrap = wdFindContinue
    .Format = True
    .MatchWholeWord = False
    .MatchCase = True
    .MatchWildcards = False
    For i = LBound(arrFind) To UBound(arrFind)
      sFind = Trim(arrFind(i))
      If Len(sFind) > 1 Then
        .Text = sFind
        .Execute Replace:=wdReplaceAll
      End If
    Next i
  End With
End Sub
If this code improvement is still too slow, you could also pause the screen refresh while it runs.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 01-18-2023, 10:45 PM
gmayor's Avatar
gmayor gmayor is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

There's a third way - and that's to read the text file directly to an array without opening it e.g. Using Andrew's code as an example.

Code:
Sub ReplacefromTXT()
'Graham Mayor - https://www.gmayor.com - Last updated - 19 Jan 2023
Dim FSO As Object, oFile As Object
Dim arrFind() As String, sFind As String
Dim oDoc As Document
Dim i As Integer
Const sName As String = "C:\Test\Test.txt" ' change this to your text file full name

       
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.OpenTextFile(sName, 1)
    arrFind = Split(oFile.ReadAll, vbNewLine)

    Set oDoc = ActiveDocument
    Options.DefaultHighlightColorIndex = wdRed
    With oDoc.Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Font.Bold = True
        .Replacement.Highlight = True
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchWholeWord = False
        .MatchCase = True
        .MatchWildcards = False
        For i = LBound(arrFind) To UBound(arrFind)
            sFind = Trim(arrFind(i))
            If Len(sFind) > 1 Then
                .Text = sFind
                .Execute Replace:=wdReplaceAll
            End If
        Next i
    End With
lbl_Exit:
    Set FSO = Nothing
    Set oFile = Nothing
    Set oDoc = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 01-19-2023, 05:08 PM
Big_Sugah Big_Sugah is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2016
Novice
Highlight word document with words from .txt file
 
Join Date: Jan 2023
Posts: 5
Big_Sugah is on a distinguished road
Default

I have tried both of those but I am now getting the error
vbaerror.png

I am assuming I need to limit the size of whatever text .Text finds
Reply With Quote
  #5  
Old 01-19-2023, 06:20 PM
Guessed's Avatar
Guessed Guessed is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file 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

It would most likely be due to that. The sample you provided only had short URLs so we didn't anticipate much longer strings.

I assume the text file isn't changed often so it would be easier to edit that to split any of overly long lines by adding a paragraph mark in a logical location than it would be to add complexity to the vba code.

I would expect the maximum string length to be something around a power of 2 eg 64, 128 or 256 characters. You can hover your mouse over the Len(sFind) to see how big the unacceptable number is.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 01-22-2023, 05:23 PM
Big_Sugah Big_Sugah is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2016
Novice
Highlight word document with words from .txt file
 
Join Date: Jan 2023
Posts: 5
Big_Sugah is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
It would most likely be due to that. The sample you provided only had short URLs so we didn't anticipate much longer strings.

I assume the text file isn't changed often so it would be easier to edit that to split any of overly long lines by adding a paragraph mark in a logical location than it would be to add complexity to the vba code.

I would expect the maximum string length to be something around a power of 2 eg 64, 128 or 256 characters. You can hover your mouse over the Len(sFind) to see how big the unacceptable number is.
Hi Guessed,

Thanks for this, I just changed the script (That generates the .txt document) to cull links over 256
I've got one more question, it seems that the VBA macro will highlight any words separated by spaces. Is there a way to instead find and replace by the whole word?
For example if I had the word "Google Account Services" in the .txt document instead of Google, Account and Services being highlighted I only wanted occurrences of the entire word.
Reply With Quote
  #7  
Old 01-22-2023, 07:33 PM
Guessed's Avatar
Guessed Guessed is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file 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

Looking at the code both Graham and I provided looks like PHRASES like "Google Account Services" would not find "Google", "Account" or "Services" unless they are together. Perhaps you need to provide a sample txt file that is not working for you so we can see what the problem might be.

The MatchWholeWord setting is irrelevant if the find string includes spaces as you describe.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 01-23-2023, 06:15 PM
Big_Sugah Big_Sugah is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2016
Novice
Highlight word document with words from .txt file
 
Join Date: Jan 2023
Posts: 5
Big_Sugah is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
Looking at the code both Graham and I provided looks like PHRASES like "Google Account Services" would not find "Google", "Account" or "Services" unless they are together. Perhaps you need to provide a sample txt file that is not working for you so we can see what the problem might be.

The MatchWholeWord setting is irrelevant if the find string includes spaces as you describe.
Hi Guessed,

I've rerun the script that generates the .txt file and again I am getting the "String Parameter too long" error
List of words from the .txt file
  • Limelite Linear 300 Plaster recess luminaire Spec
  • Ampac Warranty
  • Hochiki Warranty
The error appears even with a single word

Screenshot 2023-01-24 091434.png
Reply With Quote
  #9  
Old 01-23-2023, 07:02 PM
Guessed's Avatar
Guessed Guessed is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file 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

You need to look at the invisible characters in your text file.
sFind = "Ampac
implies that following the word Ampac is something else, like a new line rather than " Warranty". I would expect it to be showing
sFind = "Ampac Warranty"

You can add a Watch to the sFind variable to monitor its value is while the macro is running.

The macro assumes that the search terms are separated by a vbCrLf which translates to a Carriage Return/Line Feed in a text file. If your text file contents is structured differently to what the macro assumes then the code will need to be adjusted to correctly deal with the ACTUAL contents of the text file rather than the assumed contents.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #10  
Old 01-23-2023, 07:34 PM
Big_Sugah Big_Sugah is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2016
Novice
Highlight word document with words from .txt file
 
Join Date: Jan 2023
Posts: 5
Big_Sugah is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
You need to look at the invisible characters in your text file.
sFind = "Ampac
implies that following the word Ampac is something else, like a new line rather than " Warranty". I would expect it to be showing
sFind = "Ampac Warranty"

You can add a Watch to the sFind variable to monitor its value is while the macro is running.

The macro assumes that the search terms are separated by a vbCrLf which translates to a Carriage Return/Line Feed in a text file. If your text file contents is structured differently to what the macro assumes then the code will need to be adjusted to correctly deal with the ACTUAL contents of the text file rather than the assumed contents.
Hi Guessed,

This is an example of what the .txt document looks like
Screenshot 2023-01-24 102823.png
I've tried to add extra spaces to the end and new lines between the phrases but this did not work
Reply With Quote
  #11  
Old 01-24-2023, 01:52 AM
gmayor's Avatar
gmayor gmayor is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Your example appears to show a table, or line numbering, neither of which are compatible with a text file. For the process to work (certainly the version I posted) each line should only contain the search string e.g.


Ampac Warranty
Hochiki Warranty
Panasonic Warranty
HP Warranty
Sony Warranty
LG Warranty
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #12  
Old 01-24-2023, 02:15 PM
Guessed's Avatar
Guessed Guessed is offline Highlight word document with words from .txt file Windows 10 Highlight word document with words from .txt file 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

Attach a sample text file so we can do some testing to see what is going on.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highlight word document with words from .txt file Find and highlight multiple words in MS Word document AtaLoss Word VBA 37 09-22-2021 12:04 PM
Highlight word document with words from .txt file Macro To Identify & Highlight Words In MS Word Based Upon A List In Excel File Column abhimanyu Word VBA 5 03-20-2020 01:33 PM
How to find (highlight) two and more words in a list of 75k single words in Word 2010 Usora Word 8 05-29-2018 03:34 AM
Find and highlight multiple words in MS Word document qkjack Word VBA 7 02-21-2018 07:09 PM
Macro to highlight repeated words in word file and extract into excel file aabri Word VBA 1 06-14-2015 07:20 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:22 AM.


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