Applescript Excel 2016 СОХРАНИТЬ КАК CSV

Приведенный ниже Applescript отлично работает без каких-либо проблем в Excel 2011. Сценарий открывает файл Excel, удаляет столбец и удаляет любые символы "," или ";" из файла Excel, а затем сохраняет его как файл CSV. Проблема, с которой я сталкиваюсь в Excel 2016, - это последний фрагмент, который нужно сохранить как файл CSV после манипуляции. Ничего не сохраняется, и я не получаю ошибок.

tell application "Microsoft Excel"
activate
open theWorkbookFile #open the xls file
set theWorksheetname to name of worksheet 1 of active workbook
set theWorksheet to worksheet 1 of active workbook
activate object theWorksheet
tell application "System Events" to set visible of process "Microsoft Excel" to false
#Remove the first column
tell theWorksheet
   delete range column 1 shift shift to left
   try
       ##remove any "," and ";" from the product description and replace it with a " " instead.
       replace (range "B:B" of worksheet "Sheet1") what "," replacement " "
       replace (range "B:B" of worksheet "Sheet1") what ";" replacement " "
   end try
end tell

#Set the temp csv file to the name of of the Excel sheet followed by -TMP.csv
set theFile to theWorksheetname & "-TMP" & ".csv" as text
save as theWorksheet filename theFile file format CSV file format with overwrite #save the file in csv format and overwrite if it exist
close active workbook saving no #close the csv file without prompting user to save again
end tell

csv excel excel-2016
person skittymars    schedule 24.08.2015    source источник
comment
Вы нашли решение этой проблемы?   -  person juanheyns    schedule 11.12.2015

Ответы (1)


arrow_upward
0
arrow_downward

Я столкнулся с той же проблемой, и мне удалось сохранить как CSV «вручную», используя AppleScript для взаимодействия с пользователем, которое пользователь должен делать.

Мой код ниже ...

#here you choose the name to your CSV file
set fileName to "ClearD -" & (current date) as string

#here you should have your original .XLSX file name and address to open it further
set theDoc to ((path to desktop folder as text) & "ClearD-Data.xlsx") as string


tell application "Microsoft Excel"
    activate
    open file theDoc
    delay 1

    tell application "System Events"

        keystroke "s" using {command down, shift down}
        delay 0.3

        tell front window of (first application process whose frontmost is true)
            click pop up button 2 of sheet 1
            delay 0.3

            #we are doing this to select CSV in the dropdown menu
            repeat 3 times

                keystroke "c"

            end repeat

            key code 36 # hit return
            delay 1

            repeat 50 times # this will delete whatever the current file name is and put fileName instead

                key code 123 using {shift down}

            end repeat
            delay 1

            #this will type your file name
            keystroke fileName

            delay 1
            click button "Save" of sheet 1
            --    set uiElems to entire contents # I use this command only to see what is out there for me to use and troubleshoot
        end tell
    end tell
    #now we are all good so we will close excel
    close active workbook saving no

end tell
person Israel    schedule 24.05.2018