|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
macro to add brackets to each line and add single quotes to each word in the line
Hello
Im looking for a macro that add brackets to the beginning and end of each line and adds single quotes to each word in the line and add a comma at the end of the line. the reason is I am writing code in Sql and need to prepare some of it first in word. Line example AFG FKFKF DDJ DKKFJG DKDKJDJF I want the line above to look like this after the macro is run ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), I would like the macro to work on all the lines in the word dcoument. thanks for any help offered. |
#2
|
||||
|
||||
How about
Code:
Sub Macro1() Dim oPara As Paragraph Dim oRng As Range Dim vWord As Variant Dim strNew As String Dim i As Long For Each oPara In ActiveDocument.Paragraphs Set oRng = oPara.Range oRng.End = oRng.End - 1 oRng.Text = Trim(oRng.Text) vWord = Split(oRng.Text, Chr(32)) strNew = "" For i = 0 To UBound(vWord) strNew = strNew & Chr(39) & vWord(i) & Chr(39) If i < UBound(vWord) Then strNew = strNew & Chr(44) & Chr(32) End If Next i strNew = "(" & strNew & ")," oRng.Text = strNew Next oPara lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thanks gmayor, it works great except there is no comma sperating the text within the brackets such as highlighted in red below. Im sorry as I should have stated that in the original post. Everything else works.
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), |
#4
|
||||
|
||||
The comma was present in your example.
Change the section below to lose the comma (Chr(44)) Code:
If i < UBound(vWord) Then strNew = strNew & Chr(32) End If
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Sorry for not being clear.
What I meant is that after I ran your code there is no comma seperating the text in the results. After the vba is run i would like there to be commas between the values like in the example where they are highlighted in red. ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), thanks for all your help and taking the time to write the code. I do appreciate it. I have 29,300 records that need to have brackets and quotes and commas entered so the code is saving me an enormous amount of time. |
#6
|
||||
|
||||
Does this work?
Code:
Dim oPara As Paragraph Dim oRng As Range, oParaRng As Range Set oRng = ActiveDocument.Range oRng.Text = Replace(oRng.Text, " ", Chr(39) & ", " & Chr(39)) For Each oPara In oRng.Paragraphs Set oParaRng = oPara.Range oParaRng.MoveEnd Unit:=wdCharacter, Count:=-1 oParaRng.Text = "('" & oParaRng.Text & "')," Next oPara
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#7
|
||||
|
||||
Try:
Code:
Sub Demo() Application.ScreenUpdating = False Dim bOpt1 As Boolean, bOpt2 As Boolean With Options bOpt1 = .AutoFormatAsYouTypeReplaceQuotes bOpt2 = .AutoFormatReplaceQuotes .AutoFormatAsYouTypeReplaceQuotes = False .AutoFormatReplaceQuotes = False End With With ActiveDocument With .Range.Find .ClearFormatting .Replacement.ClearFormatting .Format = False .Forward = True .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Wrap = wdFindContinue .Text = " " .Replacement.Text = "', '" .Execute Replace:=wdReplaceAll .Text = "^13" .Replacement.Text = "'),^p('" .Execute Replace:=wdReplaceAll End With .Range.Paragraphs.Last.Range.Cut .Range(0, 0).Paste .Range.Paragraphs.First.Range.Characters.Last.Delete End With With Options .AutoFormatAsYouTypeReplaceQuotes = bOpt1 .AutoFormatReplaceQuotes = bOpt2 End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Thanks Guessed and macropod for doing up the codes.
Unfortunately they dont work completely. Commas do not show in the results and quotes arent applied to both texts within the brackets. Both your result are identical after both the codes are run. As you can see there is no comma seperating the text within the brackets and quotes only show at beginning and end of text. The result from both your codes: ('KAUS AAL'), ('KJFK AAL'), ('KORD AAL'), ('KSAN AAL'), ('CYYZ ACA'), I would like it to be like this ('KAUS', 'AAL'), ('KJFK', 'AAL'), ('KORD', 'AAL'), ('KSAN', 'AAL'), ('CYYZ', 'ACA'), |
#9
|
||||
|
||||
Now I'm completely confused, because that is what the original macro did:
AFG FKFKF DDJ DKKFJG DKDKJDJF AFG FKFKF DDJ DKKFJG DKDKJDJF AFG FKFKF DDJ DKKFJG DKDKJDJF AFG FKFKF DDJ DKKFJG DKDKJDJF becomes ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), ('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'), The other contributors macros removed the commas as per your follow up instructions, whereas my second macro doesn't insert the commas in the first place. In what way does the first macro not work for you?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#10
|
|||
|
|||
Gmayor your code does not add commas between the text in the brackets. Nor does it add quotes to each text in the brackets. This is the result when i use your code
('KAUS AAL'), ('KJFK AAL'), ('KORD AAL'), ('KSAN AAL'), ('CYYZ ACA'), I would like it to be like this ('KAUS', 'AAL'), ('KJFK', 'AAL'), ('KORD', 'AAL'), ('KSAN', 'AAL'), ('CYYZ', 'ACA'), |
#11
|
||||
|
||||
The reason none of these macros is doing what they should is because you have something other than a space between the words. Each attempt has been looking for a space to replace and the fact that it is not working tells us that the separator is something other than what you pasted into the original request.
You are going to need to post your actual document if you want code that works on it.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#12
|
||||
|
||||
In my original code the paragraph is split with
Code:
vWord = Split(oRng.Text, Chr(32)) Change the 32 to 160 and see if that fixes it. If not, then, as Andrew indicates, we need to see a document sample.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#13
|
|||
|
|||
Thanks for pointing that out.
I have the original data in an excel table. This data needs to be Inserted in the Sql database editor. I need to add the quotes,commas,brackets to it before i insert it. I therefore copied and pasted this data from excel to word as text only in order to add brackets and quotes etc. I turned on the show hide button to check if the spacing is correct and it shows a tab marker(arrow) seperating the words instead of a space marker. I know its asking a lot but is it posisble you could alter the code. |
#14
|
|||
|
|||
Gmayor, 160 didnt work. Ive attached a sample doc as original is too big. Hope it uploads correct
|
#15
|
||||
|
||||
Use Chr(9) in place of Chr(32) for the tab.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How many points is line spacing 'single'? | Ugeen | Word | 2 | 11-24-2013 03:14 PM |
Changing single-quotes to double-quotes | Bobosmite | Word | 5 | 04-15-2013 06:40 AM |
From the command line, how do I run a Word macro? | jdockstader | Word | 7 | 02-04-2013 06:09 AM |
Single Line Spacing for Short Lists? | tatihulot | Word | 2 | 04-20-2010 02:53 PM |
Different alignments in a single line | George99 | Word | 2 | 01-24-2010 05:49 PM |