VBA search and replace script produce long delays

Question

[[UPDATE: Apologies...I forgot the code block. Edited.]]

This is a question from one of my faculty, so I'll quote him directly, and preface this by saying I don't know VB (just the conduit here):

"In order to help an international student, I need to convert Youtube transcripts and Closed Captionings into readable text.

I wrote this routine for the purpose of eliminating time code marks from Youtube transcripts by invoking Visual Basic from within Microsoft Word. Since time codes are always on a separate line and since time codes must contain “:”, I am searching for the character “:” and then deleting the whole line in which it occurs. For some reason, the routine I have written, when completed, forces Word into a long unresponsive period (around 60-100 seconds) after which it works perfectly well. Any suggestions for avoiding that delay or for solving this problem within Word, without writing macros?"

Sub Deleteyt()

Dim oRng As Word.Range

Dim oRngDelete As Word.Range

Set oRng = ActiveDocument.Range

With oRng.Find

.Text = ":"

While .Execute

oRng.Select

Set oRngDelete = ActiveDocument.Bookmarks("\Line").Range

oRngDelete.Delete

Wend

End With

End Sub

So the code works, there's just the long delay up front. Any ideas?

I'd recommend sharing your code and a sample document if possible. — healey, Feb 13 '19 at 20:53
The best you can do is to debug and see exactly where the code stucks, you probably enter a very long unwanted loop. In any case, you should post some code here to have some help. — Matteo NNZ, Feb 13 '19 at 20:54
Indeed, without code-example this question is useless and not answerable. — Bert Verhees, Feb 13 '19 at 21:15
Set Application.ScreenUpdating=False before the start of your search (and back on again afterwards) may help as it will stop word reformatting the document every time you make a deletion. — freeflow, Feb 13 '19 at 21:31

score 0 · Answer 1 · answered Feb 13 '19 at 23:16

Why not modifying the text file which contains the transcript? I would expect you end up with a .txt file.

See example below, highly insipred from: Text file in VBA: Open/Find Replace/SaveAs/Close File

Sub CleanFile()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = application.GetOpenFilename

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    'If the line does not contain a ':', then include the line in the memory sTemp 
    if If InStr(sBuf, ":") = 0 Then sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

iFileNum = FreeFile

sFileName = Application.GetSaveAsFilename()
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub

macropod · Accepted Answer · 2019-02-14T21:20:11.267

0

You don't need a macro for this - all you need is a wildcard Find/Replace, with:

Find = <[0-9]@:[0-9]@>*^13
Replace = nothing

edited Feb 14 '19 at 21:20

answered Feb 14 '19 at 03:13

macropod

12,757
2
9
21

VBA search and replace script produce long delays

2 Answers2