Help using VBA in word 2

[Richardkl]

I have a need to remove coma's that are between " letters only" with a tab. The files are quite large and I don't feel it is practical to use find and replace. And of I can't use replace all. If I can figure out how to do a loop that can detect an end of document that would be great but I can't figure how to stop the do loop as I don't know how many comas are in the file. Any help or suggestions would be greatly appreciated

 

[fumei]

1. why do you think find and replace is impractical? It would be faster than any possible coded looping.

2. "I have a need to remove coma's that are between " letters only" with a tab. " I have no idea what that really means. With a tab?

3. people often have problems with the end-of-document thing. The answer is, use a Range, and a Do While .Execute = True kind of loop. It will process to the end of the document.

Please post a sample of original text, and what you want it to look like after. As stated, I am not grasping what it is you are trying to do.

faq219-2884

Gerry
My paintings and sculpture

 

[ClulessChris]

If you realy want to use a programatic solution the regular expresions would be the way to go.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Andrzejek]

Just guessing here, but I think he/she has a comma delimited text that needs to be a Tab delimited, but the numbers are in european format: 3,14

So:
John,Smith,123,456,The End

needs to be:
John(tab)Smith(tab)123,456(tab)The End

Am I right?

Have fun.

---- Andy

 

[SkipVought]

and if that's the case, in Word's Edit/Replace window...
[tt]
FIND: ,
REPLACE: ^t
[/tt]
no VBA required.

If you want to exclude COMMA punctuation, then replace COMMA SPACE with ZZ or some other string that you KNOW is not in your doc. Then replace the COMMA with the TAB, as above. The replace ZZ with COMMA SPACE.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[fumei]

Exactly. As I understand the situation, Find and Replace should be able to handle this. As Skip points out, two go rounds may be needed, but native Find and Replace operations are very fast and efficient. While a looping operation in VBA will do the job, strictly speaking - again,if I understand correctly - it is not needed.

faq219-2884

Gerry
My paintings and sculpture

 

[Richardkl]

Thanks all for your responses.

Andy has it correct!

(tab) being equal to "^t" = tab key on keyboard

An simple example of a line is :
John,Smith,123,456
John,Doe,123,456
Until EOF

needs to be:
John(tab)Smith(tab)123,456
John(tab)Doe(tab)123,456
until EOF

The comma between 123,456 can't be removed that is why find and replace will not work.

 

[SkipVought]

"The comma between 123,456 can't be removed that is why find and replace will not work."

Huh? REALLY?

How come find and replace works in my Word doc, thank you very much!

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[ck1999]

I think you meant to say should not be removed instead of cannot be removed.


If your lines are always the same as your sample

John,Smith,123,456
John,Doe,123,456

try this

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/31/2008 by Chris Klingenberg
'
    ActiveDocument.Select
    Selection.Collapse direction:=wdCollapseStart
    With Selection.Find
        .Text = ","
        .Replacement.Text = "^t"
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
   With Selection
   While Selection.Find.Execute
    .Collapse direction:=wdCollapseStart
    .Find.Execute Replace:=wdReplaceOne
    .Collapse direction:=wdCollapseEnd
    .Collapse direction:=wdCollapseStart
    .Find.Execute Replace:=wdReplaceOne
    .Collapse direction:=wdCollapseEnd
     .Find.Execute
   Wend
         
     
    End With
End Sub

This might not be the most elegant way.
But this code replaces 2 , then skips 1.

ck1999

 

[Richardkl]

Thanks for your response but I need a variable solution. The lines can have as few as ZERO commas to be removed to as many as 30 commas that need to be removed.

Someone suggested using a "do while", so I looked for the syntax and found nothing. I did find an example which I copied and changed the findtext value and changed the data within the with clause.
It finds "any letter" and a comma and "any letter" sequence and backs up one letter for the found selection (this deselected the find) than move forward to the "comma" and then replaces the comma with a tab character.

Do While .Execute(FindText:="^$,^$", Forward:=True, _
Format:=False) = True
With .Parent
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=vbTab
End With
loop

I don't like the fact that I can't parse the found string or the fact that I don't have a clue on the DO while syntax.

Does anyone know were I can get more information on this VB part of word? I would like to know what the "found text" varable is so I can do compares and use if statements instead of move teh cursor stuff.

Thanks all,
Richard(462417)

 

[ck1999]

replace

"^$,^$"

with

"[!0-9],"

this searches for a , preceded by Not a number

so john,smith,123,789

This should pick up the 1st 2 commas but not the last

ck1999

 

[fumei]

For the example:

John,Smith,123,456,Harry,Houdini,234,678
Harry,Doe,123,456
John,Smith,123,456

Dim r As Range
Set r = ActiveDocument.Range
With r.Find
   .MatchWildcards = True
   .Text = "[!0-9],"
   Do While .Execute(Forward:=True) = True
      With r
         .MoveStart Unit:=wdCharacter, Count:=1
         .Text = vbTab
         .Collapse Direction:=wdCollapseEnd
      End With
   Loop
End With

will produce:


John(tab)Smith(tab)123,456,Harry(tab)Houdini(tab)234,678
Harry(tab)Doe(tab)123,456
John(tab)Smith(tab)123,456

It will process to the end of the document. What it does is:

1. find a "," that does NOT have 0-9 before it

2. as the found range DOES include the character before the ",", move the Start one character. This makes the range ONLY the ","

3. make the range a Tab - in other words, the "," becomes Tab

4. collapse the range...and move on to the next one.

Done.

"The lines can have as few as ZERO commas to be removed to as many as 30 commas that need to be removed. "

It does not matter if there is 1 comma, 0 commas (although how is that possible??), or 30 commas. The number of commas has nothing to do with it. The code does not even look at "lines". It looks for commas.

"I don't like the fact that I can't parse the found string or the fact that I don't have a clue on the DO while syntax."

You do not need to parse the found string. Why would you? The found string will be the string you give it to look for. It is not Found unless it is what you tell it to look for.

Now, if you are wondering about how to do something else with the found string, that is another question. You can do all sorts of things, once you find something. If you really wanted to know what the character is before the comma - that would be easy. Although not relevant for the purpose asked about, or so it seems.

Note the code does NOT use Selection. There is no need to select anything.

Lastly, while it does replace the appropriate commas with Tabs, you may (or may not) get the full visual result desired, as that will depend on how you have your Tab Stops. However, if that does not matter, you just want the tabs, then the above should do the trick.

faq219-2884

Gerry
My paintings and sculpture

 

[Richardkl]

Well I started writing a response below, than I reviewed the results of the macro and found that there is a pattern of “character comma end-of-paragraph” that the comma must stay. So I tried to change the .Text line back to “.Text = “^$,^$” but I get a “runtime error 5692”. When I change it back to [!0-9],” the error goes away. Any Idea’s?


FIRST response:

Your solution is great and seems very elegant. It also gives me some more incite on the syntax of the .execute. It has also wetted my appetite to find out more about the control words in the VB language that relate to word documents.
Your statements of;
“If you really wanted to know what the character is before the comma - that would be easy.”
Can you tell me how or direct me to somewhere that has the information of what all the control words are?

“Note the code does NOT use Selection. There is no need to select anything.”
I agree nothing needs to be selected but all the examples of code that I found to “find” strings used the control word Selection so I thought it was needed as, again I have been unable to find a summary of the VB control words and their syntax.

“.Collapse Direction:=wdCollapseEnd’
I don’t understand the need for the above statement.
Why would you have to “Collapse” anything to continue the do … loop?
I am sure it is that I don’t know what all the .Collapse Direction control does!


Thanks again,
Richard

 

[WalkerEvans]

I've been away from the keyboard for a week or so; my wife makes me leave it at home when we go away for a little R&R (I married a smart woman). While reviewing all that I missed your last post caught my eye, and I have to admit it lost me completely. Fumei's solution is tight and elegant and does all you originally said you wanted done (good job, Gerry!), but you have now thrown out another situation that I don't understand at all.

Why would you need to keep a comma preceded by a letter and followed by a {RETURN}? If you have comma delimited text that needs to be reformatted to tab delimited, you would need to replace every single comma, without exception. Should that not be done, your document is going to fail when you try to work with it, as it will NOT be fully tab delimited.

For me at least, a bit more information is needed. Not the brightest bulb in the pack I'm afraid, but I just don't see why this is a problem.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[fumei]

I have to agree with Walker. I am not sure of what, EXACTLY, your situation is. A comma followed by a paragraph mark seems odd, and if the purpose is to converted to a tab-delimited file, this would be an issue.

"“.Collapse Direction:=wdCollapseEnd’
I don’t understand the need for the above statement.
Why would you have to “Collapse” anything to continue the do … loop?"

May I ask...did you try commenting it out and actually see for yourself what the result is? Hmmm? That would be a first step in understanding. Or at least understanding what is the real question.

I will try again, although I thought I had clearly stated what is going on.

The search string: .Text = "[!0-9]," Do you understand what that actually means? It means: ANY character is that is NOT 0 to 9. The "!" means not. So...

"a," will be found.
"4," will not be found
"T," will be found
"8," will not be found.

Now, as I mentioned in my post:

"2. as the found range DOES include the character before the ",", move the Start one character. This makes the range ONLY the ",""

what is actually found is "x," - a comma preceded by ANY character NOT 0 to 9. So, again:

"a," will be found; "H," will be found. The Range becomes that. But...you do not really want the character before the comma, now do you? You NEED that character in order to check if it is 0 to 9, but after you find it (a character NOT 0 to 9 followed by a comma) you do not want to do anything with that character.

So, you take the found range (eg. "a,"), and you Move the Start, in order to get JUST the comma. Here it is again.

"2. as the found range DOES include the character before the ",", move the Start one character. This makes the range ONLY the ",""

Now the Range is just the comma. You make the range a Tab. Be very clear about this. The Range object is now that Tab.

The Do loop is functioning on the range object (r).

With r.Find
   .MatchWildcards = True
   .Text = "[!0-9],"
   Do While .Execute(Forward:=True) = True

r is the Tab....so, as far as the Do loop is concerned, there IS no more commas, there IS no Forward.

If you Collapse the range object to a point, then there IS a Forward. Thus, the loop can continue.

Try commenting out the Collapse line. You will see that it will find the first character/comma....and that will be that. It will not find any more.

It is crucial to undertsand that a Range.Find resizes the range itself to the .Found (if found). Now if you are performing action ON THAT RANGE, there is no need to Collapse. However, if you perform action on PART OF that range, you do have to collapse. Why? Because you are changing the Range.Start and End. Check it out.

Dim r As Range
Set r = ActiveDocument.Range
With r.Find
   .MatchWildcards = True
   .Text = "[!0-9],"
   Do While .Execute(Forward:=True) = True
      With r
         .Font.Bold = True
      End With
   Loop
End With

Notice no Collapse? The code will perform action against the entire range for each Found, and keep going. You are not doing anything to boundaries of the range, just actioning the range itself.

"a,"
"G,"
"q,"

both the character and the comma will be bolded. The Range.Found will be actioned, and the code goes on to the next Found.

HOWEVER,

Dim r As Range
Set r = ActiveDocument.Range
With r.Find
   .MatchWildcards = True
   .Text = "[!0-9],"
   Do While .Execute(Forward:=True) = True
      With r
         .MoveStart Unit:=wdCharacter, Count:=1
         .Font.Bold = True
      End With
   Loop
End With

would take the first "a," (character and a comma), move (change) the Range, and action a bold on the comma. As there is NO collapse, the variable r (the Range) IS the comma...and there ain't no more Forward to look through.

If you DO collapse it, then the rest of the document IS Forward.

I know this is a little difficult to grasp at first, but once you do grasp what a Word Range really is, using them becomes a very powerful tool. Be careful not to confuse a Word range with an Excel range...they are VERY different!

faq219-2884

Gerry
My paintings and sculpture

 

[Richardkl]

Thanks for the detailed description of the "Word Range". I will play with it to make sure I totally understand the nuances of the control. Yes, I was confused because my use of range in excel (that is were I have a little more experience)

The problem that occurs with your solution is that there are lines/records, (each record is terminated by a paragraph mark AKA Carriage return)the last field in the record is a name field and some data entry idiot’s added a coma after the name hence the situation of a sporadic string of “character comma carriage return”. I have also found in other records of a different format of the name field has a comma with no second name i.e. the data entry string format was suppose to be lastname "comma" first name. The data entry person put in first name "comma" and left out the last name. This generates a string of "character comma comma"'. The second comma is the field delimiter. The delimiter is all I want action on and with the search string of ^$,^$ it covers all my string scenarios. I can take care of name field that has a comma in it after I get all the other commas removed.

Which leads me to the problem or the runtime error 5692. If I change the “.Text =“^$,^$” I get a “runtime error 5692”. When I change it back to "[!0-9],” the error goes away. Any Idea’s? how do I get the

Yes, I did know that the “!” meant not and that the 0-9 meant all numbers.

I now understand the reason for the collapse command, as you said no collapse nothing more to search.

Thanks again,
Richard

 

[Richardkl]

Thanks for all the replies, the following is the soultion that works for all my dfferent strings. I couldn't have done it without your help

Dim r As Range
Set r = ActiveDocument.Range
With r.Find
.MatchWildcards = False
.Text = "^$,^$"
Do While .Execute(Forward:=True) = True
With r
.MoveStart Unit:=wdCharacter, Count:=1
.MoveEnd Unit:=wdCharacter, Count:=-1
.Text = vbTab
.Collapse Direction:=wdCollapseEnd
End With
Loop
End With

The error was caused by the ".MatchWildCards = True"

Thanks again,
Richard

 

[WalkerEvans]

End Users are prone to the Eye-Dee-Ten-Tee problem*; ask any programmer. That's why so many of us spend so much time trying to make our work bullet-proof, all the while knowing that there will always be someone who will still find a way to screw things up. However, there are problems that we can at least try to address.

At the risk of sounding arrogant, or worse, as if I were insulting your intelligence (neither of which is intended) it sounds as if your data input operation is flawed. Since your users are inputting data into a comma-delimited data base, the input mask should be set up to reject (blank out) any field where the data entry operator inserts a comma. This is most effective if the field turns some annoying color, makes a rude sound, and refuses to allow the operator to move on until the error is corrected. You might still end up with a blank field, but at least you won't have any stray commas cluttering your data. Hint: if you really want to get their attention have the field color oscillate rapidly between colors such as [highlight white]bright magenta[/highlight] and [highlight magenta]white[/highlight], which is nearly impossible to ignore.

If you (or whoever controls input) spend a little time looking for ways to eliminate front-end errors, it will make your job here very much easier!



*ID10T

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Richardkl]

You are correct, that the data input methods were flawed, that is why I was given the tasks to export the data from the old programS, massage it,convert many many years of data to a format that can be uploaded into a more FOOL proof single input interface that every one uses. I am not the programmer of any the the interface programs. I am just lucky enough to be assigned the task of getting many diferent programs exported files to a common format. Hopefully now the reports that get generated from the data will make all the managers happy!

And with the help of all you Friendly Tek-tips users I was able to complete the task as of today with the VB that I got from here and added it to some simple macros,some that did some more simple massage, and another that opened all the files in a folder convert them to the new format then finally did a bulk upload to the new program all those years of errors are gone. Hopfully the new interface will prevent any more.

Thanks All,

Richard