Macro help

redzan · #1 02-27-2012, 09:03 PM

I need help with a macro.

This is what I have in my document:

STUDY: CHEST
NAME: BOZO, CLOWN
DATE: 02/27/2012
ID: 123456
DOCTOR: SMITH
FACILITY: ADC

This is what I need that to change that to:

###CHEST,BOZO,CLOWN,02/27/2012,123456,SMITH,ADC

Here's the catch...Those fields change with every report so the name could be longer or shorter, etc.

Thanks you for your help.

macropod · #2 02-28-2012, 12:03 AM

Hi redzan,

Apart from the ###, which you haven't told us anything about, this can be done without a macro, using a wildcard Find/Replace, where:
Find = STUDY: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})
Replace = \1,\2,\3,\4,\5,\6
Assuming you have multiple records of 6 fields each, each set will be in a new paragraph.

redzan · #3 02-28-2012, 06:16 AM

Thank you for your reply.

I don't understand your suggetion very well. I'm very much a novice. Can you explain in detail how to make your suggestion work? What exactly is a wild card? I really appreciate your help.

### is used as a divider between each report. At the beginning of each report I put ### to separate them. I have to have a separator so that each report can be counted for billing.

I transcribe these reports one after another for an entire month. What I'm trying to create is required for billing for each report inside the file.

Thanks

redzan · #4 02-28-2012, 06:19 AM

I referred to the information as fields. I don't have fields set up - I don't know how to. That information is just typed in each time. Hopefully that makes sense and helps.

Thanks again

redzan · #5 02-28-2012, 07:30 AM

I goggled wildcards and figured it out. It works just perfect. Thanks for your help.

macropod · #6 02-28-2012, 02:41 PM

Hi redzan,

Glad you got it sorted. If you want ### in front of the text, simply put them in front of the Replace expression (ie: ###\1,\2,\3,\4,\5,\6).

redzan · #7 02-29-2012, 07:54 AM

I do need one more thing...

When it replaces is there a way to get it to remove all spaces? It needs to be one continous line.

Thanks again for your help, I really appreciate it.

macropod · #8 02-29-2012, 02:01 PM

Hi redzan,

The existing Find/Replace only leaves spaces where they exist between words such as 'BOZO, CLOWN'. Do you want those spaces deleted? As for having all the output on one line, simply add ^13 to the end of the Find expression.

redzan · #9 02-29-2012, 03:00 PM

Yes, I would like the spaces between "BOZO, CLOWN" deleted.

The information is pasted into my document with the spaces so after I find/replace and get it all on one line I then need to remove the spaces.

The end result needs to be a line that looks exactly like this:

###CHEST,BOZO,CLOWN,02/27/2012,123456,SMITH,ADC

Thank you

macropod · #10 02-29-2012, 11:55 PM

In that case, an ordinary Find/Replace to delete all spaces is all you need, after the one I've already given you. I could provide a macro to perform both actions, but I'm unclear as to whether you want the paragraph breaks at the end of each line deleted also.

redzan · #11 03-01-2012, 06:13 AM

I'm not doing a very good job describing what I need. I'm sorry but will keep trying.

The only line that needs to have no spaces is the line that starts with ###. All of the rest of my document lines will be spaced as usual.

The find/replace you gave me works perfect and I did record it as a macro. Next I just need to be able to select the line beginniung with ### and remove the spaces for that line only. Again, the rest of the document must have regular spacing. Recording this as another macro to perform this is what I was planning on doing.

Thank you

macropod · #12 03-01-2012, 03:02 PM

Hi redzan,

Try:

Code:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Content
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "STUDY: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})*: ([!^13]{1,})"
    .Replacement.Text = "###\1,\2,\3,\4,\5,\6"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
    .Text = "(###[!^13 ]{1,})[ ]{1,}(*^13)"
    .Replacement.Text = "\1\2"
    .Execute Replace:=wdReplaceAll
    .Execute
  End With
  Do While .Find.Found
    .Text = Replace(.Duplicate.Text, " ", "")
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

redzan · #13 03-01-2012, 07:42 PM

You are a genius! It works just exactly like I need it to. I really appreciate your help on this. This will save me a ton of time.

macropod · #14 03-01-2012, 07:53 PM

Hi redzan,

Thanks for the feedback.

One unusual thing about the macro is that is has these two lines:
.Execute Replace:=wdReplaceAll
.Execute
Ordinarily, you might not use the first one. In this case, however, I decided to do so on the premise that most of the converted paragraphs would have no more than a single space. If none has more than a single field with a space, the loop following the second line will never execute. However, if the initial reformatting gives something like:
###CHEST,BOZO, THE, CLOWN,02/27/2012,123456,WILL, SMITH,ADC
the first pass won't be sufficient and the loop will execute. It's more efficient this way and could make a noticeable difference in a really large document.

redzan · #15 06-08-2012, 11:43 AM

This macro has been great but I need to modify it a bit. Can you help again please:

This is new my template:

ACCESSION: 1234
DATE: CURRENT DAY
DOCTOR: SMITH
FACILITY: ADC

I need it to change to:

###,1234,06/08/2012,SMITH,ADC

In the current day spot I want it to automatically populate the current date there.

Thanks again

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I assign a macro to a button when the macro is in my personal workbook?	foolios	Excel Programming	2	07-27-2011 02:41 PM