Word 2007 Macro for auto-formatting the content in each line

Description:
Word macro for iterating entire document line by line and edit the content

Sometimes we may need to format the string with quotes and comma. I have been facing this for sometime. If the content is less, it will ok to go to the starting position of the string and type " or ' and go to the end of the string and type " or ' and appending , at the end of every line, etc.

Example:

Input:
abc
def
ghi

Output:
'abc',
'def',
'ghi'

What you need to do, Please follow the below steps.

1) Just download the macro code below and paste in word visual basic editor or create a macro with this code
2) Edit the code according to your convience either to use " or '
3) Save the code and run the macro
4) It will confirm with the user whether the user copied the data or not.
5) Wait for user to copy the content
6) Automatically format and copy the formatted content
7) Displays a message to user saying that "Data is formatted and copied. You can paste the content in required application"

Its very simple and useful. Try this.
Macro Code:

sub FormatData()
Dim lineCount, i As Integer
Dim oWB As Document
Dim decision As Variant


Set oWB = Documents.Add
oWB.Activate


decision = MsgBox("Please copy the content and then click on OK", vbOKCancel, "Decision")


Select Case decision
Case vbOK 'If user agrees proceed further by pasting the content
'Paste the content from clipboard which is copied
Selection.PasteSpecial DataType:=wdPasteText, Placement:=wdInLine
Case vbCancel 'If user clicks on cancel then exit
GoTo last
Case Else
GoTo last
End Select


'got the first line
Selection.GoTo What:=wdGoToLine, Which:=wdGoToFirst


'Formatting the data


'Counting number of lines
lineCount = oWB.BuiltInDocumentProperties("Number of lines").Value


For i = 1 To lineCount
'Quoting the line
Selection.HomeKey Unit:=wdLine
Selection.TypeText Text:="'"
Selection.EndKey Unit:=wdLine
Selection.TypeText Text:="'"


'Appending , till linecount-1 line
If i < lineCount Then
Selection.TypeText Text:=","
End If
'Moving to next line
Selection.MoveDown Unit:=wdLine, Count:=1
Next i


'copy the content in the document
ActiveDocument.Content.Copy


ActiveDocument.Close savechanges:=wdDoNotSaveChanges


'Informing user that he/she can paste the data where ever required
MsgBox ("Content is formatted and copied, Please paste the data in the required application")


last:


End Sub


'Help:
'go to the beginning
'Selection.HomeKey Unit:=wdStory


'go to the end
'Selection.EndKey Unit:=wdStory


'got the first line
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToFirst


'go to the last line
'Selection.GoTo What:=wdGoToLine, Which:=wdGoToLast


'If you just want to select a line, you can also do something like this :
'Selection.HomeKey Unit:=wdLine
'Selection.EndKey Unit:=wdLine, Extend:=wdExtend


'Count number of words
'sNumberofwords = oWB.BuiltInDocumentProperties("Number of words").Value

For any further queries, please send mail to psrdotcom@gmail.com