Word 2010 vba Find/Replace Problems

I have a recurring "Import Word to Excel" task that I do manually weekly and would like to automate with VBA the insertion of tabs for certain spaces.

I get ~200 lines of data in 2010 Word files that I need to import into Excel 2010. My problem is that the data is space delimited and I need to use Word to find and only replace certain spaces (most but not all) with tabs. I need to find the first digit ("^#") in a line, then backward two spaces, then replace this and the next 6 spaces with tabs (^t"). The end of each line needs some space unchanged, but if I can do this, I think I can continue on my own.

I have 2 lines of Example Data below and below that Word 2010 VBA code that I have created.

I get it to work occasionally but usually no. It seems the backward search after finding the first "^#" does not work most of the time.

It may be problems with spaces. What is the exact space character that should be used? Or it may be that I don't understand the find and how the selection changes after find? Or whatever?

I also cannot step through my VBA and see the cursor all the time. I don't understand why or what to set/do?

I would appreciate help in solving this problem.

Thank you.

Example Data

Description of XXX xxx 11 a+ 123 456 789 Supporting Data A+ 0.9 P

Description of YYY yyy 18 b- 981 654 321 More Supporting Date B- 0.75 Q

Sub test_TabAdd()
Selection.HomeKey unit:=wdStory
Selection.EndKey unit:=wdLine, Extend:=wdExtend
Selection.Find.ClearFormatting
Selection.Find.Execute Findtext:="^#", MatchWholeWord:=False, Forward:=True
Selection.Find.Execute Findtext:=" ", MatchWholeWord:=False, Forward:=False
Selection.Find.Execute Findtext:=" ", MatchWholeWord:=False, Forward:=False
Selection.Text = "^t"
For i = 1 To 7
Selection.Find.Execute Findtext:=" ", MatchWholeWord:=False, Forward:=True, Replacewith:="^t"
Next i
End Sub

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Graham Mayor

The examples find A to E followed by +, - or space? Note the space before the closing square bracket. The first example will find multiple spaces. If you want it to only find the A+ or A- of Aspace then change 1, in the first example to 1 without the trailing comma. Note that this will also find any sentence beginning Aspace.

([A-E][+\- ]{1})

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer

Greg Maxey

Undoutably some wildcard guru will be along to dazzle with a find and replace solution. In the meantime, provided each line of data represents a singe paragraph, you could use a range manipulation:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oPar As Paragraph
Dim oRng As Word.Range
Dim lngIndex As Long
For Each oPar In ActiveDocument.Paragraphs
    Set oRng = oPar.Range
    oRng.Collapse Direction:=wdCollapseStart
    oRng.MoveStartUntil "1234567890"
    oRng.Move wdCharacter, -1
    Do
      oRng.Move wdCharacter, -1
    Loop Until oRng.Characters.First = " "
    oRng.Characters.First = vbTab
    For lngIndex = 1 To 6
      oRng.MoveEndUntil " "
      oRng.MoveEnd wdCharacter, 1
      oRng.Characters.Last = vbTab
    Next lngIndex
Next oPar
End Sub

Greg Maxey
***
Death smiles at us all, but all a man can do is smile back.

For more help with Word visit:
http://gregmaxey.com/word_tips.html

Word Top Contributors:

Word 2010 vba Find/Replace Problems

Replies (9)

Question Info

Word 2010 vba Find/Replace Problems

Replies (9) 

Question Info

Replies (9)