How to save as a excel file using AppleScript-CodePudding

I want to Save as a Excel file in same folder using Excel workbook(.xlsx) format without overwrite the opened document

input file type --> .xls

Output file type --> .xlsx

tell application "Finder"

set theWorkbookFile to (choose file with prompt "Select Excel file to process:" of type {"xls", "xlsx"} without invisibles)
set theWorkbookPath to theWorkbookFile as Unicode text


tell application "Microsoft Excel"
    activate
    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
            
            set theCell to cell ("A1" & ":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
        repeat with rowValueColor from 1 to rowsCount
            
            
            tell interior object of range ("B1" & ":B" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("D1" & ":D" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("H1" & ":H" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("I1" & ":I" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
        end repeat      
        
        
        (*
        tell application "System Events"
            keystroke "s" using {shift down, command down} -- shift-command-left
            
        end tell
        *)
                
        
    end tell
end tell

end tell

CodePudding user response：

Why are you resorting to GUI Scripting? Excel’s AppleScript interface is extensive, albeit an ugly mess. I don’t know if it’s improved in newer versions, but traditionally you’d use the save workbook as command like this:

set xlsxPath to "/Users/FOO/test.xlsx"

-- very old apps may not accept POSIX file values or POSIX path strings, requiring long-obsoleted HFS paths instead
set hfsPath to xlsxPath as POSIX file as string

tell application "Microsoft Excel"
    activate
    tell workbook 1
        save workbook as filename hfsPath file format Excel XML file format
    end tell
end tell

(Don’t have an up-to-date version to see if it’s changed, but hopefully that gets you started.)

CodePudding user response：

Finally got it with delimiters: Here is code to save a document in same folder using file prompt.

tell application "Finder"

set theWorkbookFile to (choose file with prompt "Select Excel file to process:" of type {"xls", "xlsx"} without invisibles)
set theWorkbookPath to theWorkbookFile as Unicode text

--text delimiter saving a document inside same path

set saveTID to AppleScript's text item delimiters
set AppleScript's text item delimiters to {":"}
set folderPath to (text 1 thru text item -2 of theWorkbookPath) & ":"
set AppleScript's text item delimiters to saveTID


set theOutputFilePath to folderPath & "Output.xlsx"




tell application "Microsoft Excel"
    activate
    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
            
            set theCell to cell ("A1" & ":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
        repeat with rowValueColor from 1 to rowsCount
            
            
            tell interior object of range ("B1" & ":B" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("D1" & ":D" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("H1" & ":H" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
            tell interior object of range ("I1" & ":I" & rowValueColor) of active sheet
                set color to {255, 242, 204}
            end tell
        end repeat
        
        
    end tell
    save active workbook in theOutputFilePath as Excel XML file format
    close workbooks
    display alert "Processed Completed"
end tell

end tell