Sunday, October 17, 2021

#1 2021-10-08 06:32:49 pm

nellbern
Member
Registered: 2007-01-16
Posts: 143

Save Excel as PDF

Hi, Is it possible to save as an Excel file as PDF using Applescript? I'm able to save the Excel file on my desktop but I'm getting an error saving it as PDF. The file name needs to be the same as the Excel file but with the PDF extension.
Example: SS_October_2021.xslx    SS_October_2021.pdf
this is the error:
Can’t get PDF of "Macintosh HD:Users:Eli-Ros:Desktop:SS_October_2021.pdf"
I'll appreciate your help.


Applescript:

set today to (current date)
set workbookName to ("SS_" & month of today & "_" & year of today & ".xlsx") as string
set workbookNamePDF to ("OD_FTP_Access_" & month of today & "_" & year of today & ".pdf") as string

set destinationPath to (path to desktop as text) & workbookName
set destinationPath to (path to desktop as text) & workbookNamePDF
tell application "Microsoft Excel"
   
save active workbook in destinationPath
save as active workbook file format PDF in destinationPath
   end tell

Browser: Safari 537.36
Operating System: macOS 10.14


Filed under: PDF, excel

Offline

 

#2 2021-10-08 09:07:11 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 128

Re: Save Excel as PDF

First, your requirements are contradictory in that you mention you would like the pdf to have 'SS_October_2021.pdf' as its name but also 'OD_FTP_Access_October_2021.pdf'. Also, you are using the same 'destinationPath' for both filenames. You should either have two such file references or combine the extensions separately to a file reference containing just the common elements.

I'll let you sort out the naming.

I think Excel separates sheets when creating PDFs but perhaps this depends upon your version.

Applescript:

set today to (current date)
set workbookName to ("SS_" & month of today & "_" & year of today & ".xlsx") as string
set workbookNamePDF to ("OD_FTP_Access_" & month of today & "_" & year of today & ".pdf") as string

set destinationPathX to (path to desktop as text) & workbookName
set destinationPathP to (path to desktop as text) & workbookNamePDF
tell application "Microsoft Excel"
   
   save active workbook in destinationPathX -- as xlsx
   save as active sheet filename destinationPathP file format PDF file format -- as pdfs
end tell

Regarding the 'save as' command:

• You must provide the sheet, not the workbook, but you can still print multiple sheets. Weird.
• Begin with the filename (which can include the full path). Don't use 'in' the way other apps do. This part is obligatory.
• Each file format typically includes the words 'file format'. If you use 'PDF' then you are attempting to use 'PDF' as a variable, which of course, doesn't exist. You can probably see this from its colour. So while it seems redundant, you have to use 'file format PDF file format'. You can get the list of file formats from Excel's dictionary under the 'save as' command.


Filed under: excel, file formats

Offline

 

#3 2021-10-10 11:19:49 pm

nellbern
Member
Registered: 2007-01-16
Posts: 143

Re: Save Excel as PDF

Duh! My bad. I didn't realize I had a typo. Thank you for clarifying the 'save as' command info. That's perfect!

I have an additional thing to ask. How do I change the months to a 2 digit format? (Example:   SS_October_2021.xslx to SS_10_2021.xslx or  SS_June_2021.xslx to  SS_06_2021.xslx). I tried:


Applescript:

set workbookName to ("SS_" & month of today as integer & "_" & year of today & ".xlsx") as string

Offline

 

#4 2021-10-11 04:33:33 am

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 128

Re: Save Excel as PDF

Well technically, you can't. At least not without coercing it to text.

When it comes to dates, some of their behaviour is governed by your Date & Time system settings. In my settings, I use 'y-MM-dd' which produces actual dates like '2021-10-11'. This affects something known as the 'short date string'.

Anyway, there are a couple of ways you can approach this.

Applescript:

-- Get a working date
set cd to ((current date) - (40 * days)) -- to get back to a single digit month
set mm to month of cd as integer
--> 9

If the result is a single digit, then we need to pad it

Applescript:

if mm is less than 10 then
   set mm to "0" & mm
else
   set mm to mm as text
end if
--> "09"

Another approach is to work with the short date string

Applescript:

set sds to short date string of cd
--> "2021-09-01"
set AppleScript's text item delimiters to "-"
middle word of sdd
--> "09"

There are other things you can do, such as making a list of months as two digits, e.g. {"01", "02"…} and then getting 'item (month as integer) of list'. If none of these are suitable, there is an in-depth post titled 'Dates & Times in AppleScripts' within the 'unScripted' forum which has a lot more on the subject, and searching for posts tagged 'date' will generate many hits. Hopefully you can find something useful for your specific needs.

And of course, the Language Guide discusses dates in its class reference:

    https://developer.apple.com/library/arc … g-BBCGECID


Filed under: date, month

Offline

 

#5 2021-10-11 08:31:51 am

nellbern
Member
Registered: 2007-01-16
Posts: 143

Re: Save Excel as PDF

Thank you, for all your help. I decided to keep it simple with the months.  You clarified & answer my questions. Wonderful explanations! smile

Offline

 

#6 2021-10-11 01:25:02 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 128

Re: Save Excel as PDF

Glad it helped. Regards.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)