cleggy
Newbie
Joined: 11 May 2010
Posts: 1
|
 Posted: Tue May 11, 2010 5:24 am Post subject: Find and Replace Macro - Calc |
 |
|
I have a spreadsheet with over 15,000 products that needs converting to a format that Magento (os-commerce solution) understands.
I have a spreadsheet with 5 digit numbers like this:
| Code: |
Desk Tidies 18141
Desk Tidies 18142
Desk Tidies 18143
Desk Tidies 18144
Desk Tidies 18140
Perforators 18151
Perforators 18152
Perforators 18153
Perforators 18154
Perforators 18155
Perforators 18150
|
These 5 digit numbers need replacing with the numbers that correspond with the category numbers for Magento.
So numbers 18140 to 18144 need replacing with 122,127
Numbers 18150 to 18154 with 122,128
etc..
I want to write a find and replace macro that will replace all the numbers for these categories at once as my client will be sending me an updated database every 3 months and having to go through by hand is taking me far too long.
I have tried using the record macro function but for each find and replace it is generating 30+ lines of code as shown here
| Code: |
rem ----------------------------------------------------------------------
dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "18121"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "122,125"
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(17) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.Backward"
args2(4).Value = false
args2(5).Name = "SearchItem.Pattern"
args2(5).Value = false
args2(6).Name = "SearchItem.Content"
args2(6).Value = false
args2(7).Name = "SearchItem.AsianOptions"
args2(7).Value = false
args2(8).Name = "SearchItem.AlgorithmType"
args2(8).Value = 0
args2(9).Name = "SearchItem.SearchFlags"
args2(9).Value = 65536
args2(10).Name = "SearchItem.SearchString"
args2(10).Value = "18122"
args2(11).Name = "SearchItem.ReplaceString"
args2(11).Value = "122,125"
args2(12).Name = "SearchItem.Locale"
args2(12).Value = 255
args2(13).Name = "SearchItem.ChangedChars"
args2(13).Value = 2
args2(14).Name = "SearchItem.DeletedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.InsertedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.TransliterateFlags"
args2(16).Value = 1280
args2(17).Name = "SearchItem.Command"
args2(17).Value = 3
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())
rem ----------------------------------------------------------------------
|
Is there a way to write a more efficient macro?
Thankyou for any help you can provide, this is driving me up the wall! |
|
