![]() |
|
#1
|
|||
|
|||
![]()
Hi All,
I'm new into macro thing. Currently given a task to convert over 50000 ![]() thanks in advance. Cheers!! Kilosub Sub Macro1() ' ' Macro1 Macro ' Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Delete Unit:=wdCharacter, Count:=1 Selection.TypeParagraph Selection.TypeParagraph Selection.TypeParagraph Selection.TypeParagraph Selection.TypeParagraph Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=5 Selection.TypeParagraph Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=4 Selection.TypeParagraph Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.HomeKey Unit:=wdLine Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph ActiveWindow.ActivePane.SmallScroll Down:=14 Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=2 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=5 Selection.TypeParagraph Selection.MoveRight Unit:=wdCharacter, Count:=18 Selection.EndKey Unit:=wdLine Selection.MoveLeft Unit:=wdCharacter, Count:=13, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.HomeKey Unit:=wdLine Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=5 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=4 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.Font.Size = 7 Selection.MoveUp Unit:=wdLine, Count:=1 Selection.Font.Size = 7 Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveUp Unit:=wdLine, Count:=1 Selection.Font.Size = 7 Selection.MoveDown Unit:=wdLine, Count:=2 Selection.TypeParagraph Selection.TypeParagraph ActiveWindow.ActivePane.SmallScroll Down:=5 Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 Selection.TypeParagraph Selection.MoveDown Unit:=wdLine, Count:=1 End Sub |
#2
|
||||
|
||||
![]()
Hi Kilosub,
It would be helpful if you could post some representative examples of the data (say 4 or 5 samples), showing how they appear before processing and how they should appear after processing. I'm sure it can be done more efficiently than you're now doing.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
![]()
Hi Macropod,
i've attach the raw and sample of the output.. ![]() Thanks a lot. Cheers!! Kilosub |
#4
|
||||
|
||||
![]()
Cross-posted at: http://answers.microsoft.com/en-us/o...c-68b599b31bf5
Kilosub, for cross-posting etiquette, please read: http://www.excelguru.ca/node/7
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
![]() Quote:
Cheers!! Kilosub |
#6
|
||||
|
||||
![]()
Hi Kilosub,
Try: Code:
Sub StatementReformatter() Dim StrFind As String, StrRep As String Dim myRange As Range, i As Long StrFind = "[ ]{8}[0-9]{1,2}/[0-9]{2}/[0-9]{2}(^13[ ]{8}[0-9]{10}^13)|( BONUS POINTS AS AT)" StrFind = StrFind & "|( Your card will expire on[!^13]{1,}^13)|( Dear valued cardmember[!^13]{1,}^13)" StrFind = StrFind & "|( Collection date)|( Expiry date of rebate voucher :)([!^13]{1,}^13)" StrFind = StrFind & "|( For enquiries[!^13]{1,}^13)|( I acknowledged receipt[!^13]{1,}^13)" StrFind = StrFind & "|( and / OR[!^13]{1,}^13)|( Signature:[!^13]{1,}^13)|( IC/ Passport No:[!^13]{1,}^13)" StrRep = "^m^p^p^p^p^p^p\1^p^p|^p^p^p^p\1|^p\1|^p\1^p|^p\1|^p\1#\2^p|^p\1^p|^p\1^p|^p^p\1^p^p|^p\1^p|^p\1^p" With ActiveDocument For i = 0 To UBound(Split(StrFind, "|")) Set myRange = .Range With myRange.Find .ClearFormatting .Replacement.ClearFormatting .Format = False .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = True .Text = Split(StrFind, "|")(i) .Replacement.Text = Split(StrRep, "|")(i) .Execute Replace:=wdReplaceAll End With Next With .Range.Find .Text = "(Your card will expire on[!^13]{1,})" .Replacement.Text = "\1" .Replacement.Font.Bold = True .Format = True .MatchCase = False .MatchWholeWord = False .MatchAllWordForms = False .MatchSoundsLike = False .MatchWildcards = True .Execute Replace:=wdReplaceAll .Text = "#([0-9]{1,2} [JFMASOND][anuryebchpilgstmov]{2,8} [12][0-9]{3}.)" .Execute Replace:=wdReplaceAll End With With .Characters.First .Delete .Delete End With End With Set myRange = Nothing End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
![]()
Hi Macropod,
Thanks for the code, I don't understand the coding at all... ![]() Btw, after print out, found the 1st data line is at 6.9cm measure from the top end of the page can you bring it to 2.9cm, so that all data can be in 1 page. Thanks a lot. Cheers!! Kilosub |
#8
|
||||
|
||||
![]()
Hi Kilosub,
The code's just a series of wildcard Find/replace operations, using a loop (you said you wanted a loop ![]() The 'save' message is occuring because of how extensive the changes are. You can probably both eliminate the prompt and make the code run faster by adding the lines: Application.ScreenUpdating = False Application.DisplayAlerts = wdAlertsNone immediately after the line: Sub StatementReformatter() and by inserting: Application.ScreenUpdating = True before the line: End Sub As for the spacing, I simply took that from what you'd posted. If you want to reduce the# of empty lines, go to the line: StrRep = "^m^p^p^p^p^p^p\1^p^p ... and decrease the # of '^p' sequences after the '^m'. For example: StrRep = "^m^p^p^p\1^p^p ... will halve the amount of white space. Alternatively (or as well), you could reduce the size of your document's top/bottom margins.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
![]()
Hi Macropod,
Lol!! Loop all the way ![]() After adding the code and rerun the same data... it completed approx. 5min. ![]() Lastly, found "Collection date:" not same row with ""IC/ Passport No:" Thanks and Cheers!! Kilosub |
#10
|
||||
|
||||
![]()
Hi Kilosub,
Change: ( Collection date) to: (^13 Collection date)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
![]()
Hi Macropod,
Ditto!! dry run on 85Mb data, so far looks good. Awesome!! ![]() There's only 1 minor issue which is the last 2 row lines doesn't have spacing in between it. I've upload the sample together with it. Thanks and cheers!! Kilosub |
#12
|
||||
|
||||
![]()
Hi Kilosub,
When I run the macro, the last few lines are correctly formatted: Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
![]()
Hi Macropod,
I better replace back the code plus the mod and rerun again. Will keep you posted later. Going for a break to long in front of the monitor... ![]() |
#14
|
||||
|
||||
![]()
Hi Kilosub,
All sorted?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
![]()
Hi Macropod,
Being testing the whole day yesterday. Problem solved when run on MS Office 2007 but not in Ms Office 2003. Strange but what's your thought? Thanks and Cheers!! Kilosub |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Looping Question | Steve B | PowerPoint | 0 | 01-19-2011 10:44 AM |
Looping video | tomasball | PowerPoint | 0 | 10-30-2010 02:38 PM |
Unbreakable looping | Dixon | Outlook | 0 | 09-23-2009 02:10 PM |
![]() |
BSquared18 | PowerPoint | 1 | 07-04-2009 01:56 PM |
Looping though Custom Properties in VBA | suekay | Misc | 0 | 05-19-2006 06:10 AM |