Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-30-2017, 12:13 PM
poetofpiano poetofpiano is offline Why does this Macro take so long to execute? Windows 8 Why does this Macro take so long to execute? Office 2013
Novice
Why does this Macro take so long to execute?
 
Join Date: Sep 2015
Posts: 25
poetofpiano is on a distinguished road
Default Why does this Macro take so long to execute?


I execute the following Macro after manually selecting a block of text that has been pasted into Word from a PDF to replace all the line breaks with spaces and thus make it a single, normal paragraph:

Code:
Sub LinkLinesOfPDF_Text()

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = vbCr
    .Replacement.Text = " "
    .Forward = False    '!!!
    .Wrap = wdFindStop   '!!!
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
Selection.Find.Execute , , , , , , , , , , wdReplaceAll

' remove double spaces:
Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "  "
        .Replacement.Text = " "
        .Forward = False
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub
It works fine, but I have strangely found that at least in Word docs that have a lot of text, the action will take 3 to 4 seconds to complete even though I am only selecting a few lines of text. I do not notice this problem when I have a brand new word document with little text on which I am performing the action.

Is this delay unavoidable, or is there a more elegant, efficient way for me to replace all new lines with spaces in a given selection of text?

Thank you so much in advance!
Reply With Quote
  #2  
Old 09-30-2017, 07:34 PM
Guessed's Avatar
Guessed Guessed is offline Why does this Macro take so long to execute? Windows 10 Why does this Macro take so long to execute? Office 2013
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

The delay may be due to some minor things in your code but may also be related to the format of what you have selected. I've done a tidy up on your code but it essentially does the same thing with less lines so may not be any quicker. I have a feeling that searching forward is faster than searching backwards but I could be wrong.
Code:
Sub MergeParagraph_Text()
  With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "^p"  'same as vbCr but used by the find/replace dialog
    .Replacement.Text = " "
    .Forward = True   'search forwards instead of backwards
    .Wrap = wdFindStop  'stops replacing without asking if you want to do rest of doc
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
    ' remove double spaces:
    .Text = "  "
    .Replacement.Text = " "
    .Execute Replace:=wdReplaceAll
  End With
End Sub
Ultimately, the code speed depends on what you have selected. If the selection includes table cells then there could be differences with the ^p and vbCr values and the code could stumble around a bit.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 09-30-2017, 08:31 PM
poetofpiano poetofpiano is offline Why does this Macro take so long to execute? Windows 8 Why does this Macro take so long to execute? Office 2013
Novice
Why does this Macro take so long to execute?
 
Join Date: Sep 2015
Posts: 25
poetofpiano is on a distinguished road
Default

Thank you so much for the advice. Unfortunately, your version still goes just as slow for me. It is strange, but I really think the slowness comes from the size of the word document I'm working in for some reason. I can paste the exact same text that the macro functions slowly on into a new word document and the find and replace action then works instantaneously, but in the big word document I've been working in, it takes several seconds to do the same thing. Not really sure why...
Reply With Quote
  #4  
Old 09-30-2017, 11:59 PM
macropod's Avatar
macropod macropod is offline Why does this Macro take so long to execute? Windows 7 64bit Why does this Macro take so long to execute? Office 2010 32bit
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

Perhaps you should look at the Cleaning up Text Pasted from Websites, E-mails, PDFs etc. 'Sticky' thread at the top of the Word forum: https://www.msofficeforums.com/word/...s-e-mails.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Execute autocorrects? KaiserD2 Word 6 04-03-2017 10:18 PM
Simple Hide columns macro, when I execute it hides everything left of selection, please help mcronin Excel Programming 2 08-31-2016 05:15 PM
Why does this Macro take so long to execute? I need to convert shaded text into highlighted text on about 80 different long documents. VBA macro? AustinBrister Word VBA 8 05-28-2015 02:42 PM
Why does this Macro take so long to execute? Find and Execute cksm4 Word VBA 1 10-22-2011 11:36 PM
Why does this Macro take so long to execute? How do I call/execute an Excel macro from Access 2003 KramerJ Excel 2 05-11-2009 11:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:58 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