Home > OS >  how to add border to a range of cell using AppleScript
how to add border to a range of cell using AppleScript

Time:11-20

I want to add thin border around a range of cell (cell input varies from file to file).

I need someone to help me on this. Finding it more difficult to automate excel with AppleScript.

This is my code

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
tell application "Microsoft Excel"
    open theWorkbookFile
    set theWorkbook to active workbook
    tell active sheet
        tell used range
            set RowCount to count of rows
            --display dialog RowCount
            set columnCount to count of columns
            --display dialog columnCount
            
            
            repeat with theRowValue from 1 to RowCount
                repeat with theColumValue from 1 to columnCount
                    set theCell to "A" & theColumValue & ":K" & theRowValue
                    --set myRanger to theCell
                    select range theCell
                    --set mySel to selection
                    --set myRange to first item of (get areas of mySel)
                    set myBorders to {border top, border bottom, border left, border right}
                    repeat with i from 1 to 4
                        set theBorder to get border theCell which border (item i of myBorders)
                        --set weight of theBorder to border weight thin
                    end repeat
                end repeat
            end repeat
        end tell
    end tell
end tell

Thanks Ashwin

CodePudding user response:

set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"

tell application "Microsoft Excel"
    open theWorkbookFile
    set myBorders to {border top, border bottom, border left, border right}
    tell active workbook to tell active sheet
        
        tell used range to set {rowsCount, columnsCount} to {count of rows, count of columns}
        
        repeat with rowValue from 1 to rowsCount
            repeat with columnValue from 1 to columnsCount
                set theCell to cell ("A" & columnValue & ":K" & rowValue)
                repeat with i from 1 to 4
                    set theBorder to get border theCell which border (item i of myBorders)
                    set weight of theBorder to border weight thin
                end repeat
            end repeat
        end repeat
        
    end tell
end tell
  • Related