CODEWITHSUNDEEP
excelapplescript
lonesomematchstick
lonesomematchstick

Reputation: 1

Remove cell borders

I'm having trouble using Applescript to remove cell borders in a table of data. I'm starting out with a spreadsheet that contains data in C15:K31. Each of these cells has a border around it. All I want to do is insert a column at index 4 (column D) and then set each of the cells in column D to not have any borders. I included a repeat to look at every cell in column D, but ideally I would simply remove the formatting all at once, without the repeat.

Any thoughts? My code is below:

tell application "Microsoft Excel"

activate workbook
try
    set maxCount to count of sheets of active workbook
    set sheetCounter to 1
    repeat maxCount times
        activate object worksheet sheetCounter
        set theWorksheetName to name of worksheet sheetCounter of active workbook
        if theWorksheetName contains "gap" then
                insert into range column 4 of active sheet
                set column width of column 4 of active sheet to 7.5
                set color index of interior object of column 4 to 0

                set startFormatHere to range "D1" of active sheet
                set nextRow to 1
                repeat 20 times
                    set formatCell to (get offset startFormatHere row offset nextRow)
                    set formatHere to (get address of formatCell)
                    set cellRange to range formatHere

--None of the below attempts have worked to remove border lines

                    --clear contents range "D2" of active sheet of active workbook 
                    --tell border object of range "D2"
                    --set {line style, line weight, weight, visible} to {None, 0, 0, false}
                    --end tell

                    --set border of range "D2" to false
                    --set weight of (get border of cellRange which border edge bottom) to None
                    --set line style of border object of range "D2" to line style none                  

                    --set line weight of cellRange to 0

                    set nextRow to nextRow + 1
                end repeat
            end repeat
            set sheetCounter to sheetCounter + 1
        else
            set sheetCounter to sheetCounter + 1
        end if
    end repeat
end try

end tell

Upvotes: 0

Views: 786

Answers (1)

jackjr300
jackjr300

Reputation: 7191

You need the get border command --> get border (a range) which border (an enumeration), can be one of the following:

inside horizontal, inside vertical, diagonal down, diagonal up, edge bottom, ‌edge left, ‌edge right, ‌edge top, ‌border bottom, border left, ‌border right, ‌border top

tell application "Microsoft Excel"
    activate workbook
    set maxCount to count of sheets of active workbook
    repeat with sheetCounter from 1 to maxCount
        activate object worksheet sheetCounter
        set theWorksheetName to name of worksheet sheetCounter of active workbook
        if theWorksheetName contains "gap" then
            insert into range column 4 of active sheet
            set column width of column 4 of active sheet to 7.5
            set color index of interior object of column 4 to 0

            set myRange to range "D1:D20" of active sheet
            set myBorders to {border top, border bottom, border left, border right}
            repeat with i from 1 to 4
                set theBorder to get border myRange which border (item i of myBorders)
                set line style of theBorder to line style none
            end repeat
        end if
    end repeat
end tell

Upvotes: 2

Related Questions