I have created this AS, want to optimize it more.
Requirement is like, In the 9th column of Excel, we have to check every cell value and manipulate it.
Criteria is like, we want only those value starting with EXT and delimited with a semicolon( ; )
Current Value in cell:
RETURN_regener;EXT-Quan;EXT-Quanta Fremont;ext-International Press;refurbEXT-Regenersis;EXT-Flextronics Refurb;
Desired VAlue: EXT-Quan;EXT-Quanta Fremont;EXT-Flextronics Refurb;
Find the current AS below:
–Choosing the Excel workbook
set theWorkbookFile to choose file with prompt “Please select an Excel workbook file:”
set theWorkbookName to name of (info for theWorkbookFile)
tell application “Microsoft Excel”
open theWorkbookFile
set theWorkbook to active workbook
set temp to “null”
--Insert the column number here and the String to scearch for filteration
set colIndex to 9
set scearch to "EXT"
set totalrows to first row index of (get end (cell 1 of row (count rows)) direction toward the top)
--Cycle through each of the rows
repeat with rowIndex from totalrows to 2 by -1
set strTemp to value of (cell colIndex of row rowIndex)
--Split the value of each row in the Usergroup Access column based on a deliminator ";"
set myArray to my theSplit(strTemp, ";")
set Upper to length of myArray
if Upper > 1 then
repeat with i from 1 to (length of myArray)
set temp1 to item i of myArray
set strVal to text 1 thru 3 of temp1
--Filteration of data in Usergroup Access column for each row is done
if strVal = scearch then
if temp = "null" then
set temp to temp1
else
set temp to temp & ";" & temp1
end if
end if
end repeat
--Putting back the filtered value inside the Usergroup Access column for a particular row
set value of cell colIndex of row rowIndex to temp
set temp to "null"
end if
end repeat
display dialog "Completed"
end tell
–theSplit function written to split the data based on a particular deliminator
on theSplit(theString, theDelimiter)
– save delimiters to restore old settings
set oldDelimiters to AppleScript’s text item delimiters
– set delimiters to delimiter to be used
set AppleScript’s text item delimiters to theDelimiter
– create the array
set theArray to every text item of theString
– restore the old setting
set AppleScript’s text item delimiters to oldDelimiters
– return the result
return theArray
end theSplit
Browser: Safari 531.22.7
Operating System: Mac OS X (10.5)