I need to translate hundreds of Xcel files to PDF. I have been trying stuff but with no result. Does anybody have a script handy I can get?
Thanks.
tell application "Finder"
activate
set theItems to selection
repeat with itemRef in theItems
set theItemParentPath to (container of itemRef) as text
set theItemName to (name of itemRef) as string
set theItemExtension to (name extension of itemRef)
set theItemExtensionLength to (count theItemExtension) + 1
set theOutputPath to theItemParentPath & (text 1 thru (-1 - theItemExtensionLength) of theItemName)
set newfile to theOutputPath & ".pdf"
set oldfile to theItemParentPath & theItemName
if theItemExtension is "pptx" then
my savePowerPointAsPDF(oldfile as text, newfile as text)
delay 1
else if theItemExtension is "docx" then
my saveDocxAsPDF(oldfile as text, newfile as text)
delay 1
else if theItemExtension is "xlsx" or theItemExtension is "xlsm" or theItemExtension is "xls" then
my saveXlsxAsPDF(oldfile as text, newfile as text)
delay 1
else if theItemExtension is "pdf" then
display dialog "already pdf"
end if
end repeat
end tell
on savePowerPointAsPDF(documentPath, PDFPath)
set f to documentPath as alias -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
tell application "Microsoft PowerPoint"
launch
open f
tell application "System Events"
tell process "Microsoft PowerPoint"
set visible to false
end tell
end tell
set PDFPath to my createEmptyFile(PDFPath) -- the handler return a file object (this line must be inside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the saving command)
delay 0.5
save active presentation in PDFPath as save as PDF
delay 0.5
close active presentation saving no
quit
end tell
end savePowerPointAsPDF
on createEmptyFile(f)
do shell script "touch " & quoted form of POSIX path of f -- create file (this command do nothing when the PDF file exists)
delay 1
return (POSIX path of f) as POSIX file
end createEmptyFile
on saveDocxAsPDF(documentPath, PDFPath)
set f to documentPath as alias -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
tell application id "com.microsoft.word"
open f
set PDFPath to my createEmptyFile(PDFPath)
delay 1
tell active document
save as it file name PDFPath file format format PDF
delay 2
close saving no
end tell
end tell
end saveDocxAsPDF
on saveXlsxAsPDF(documentPath, PDFPath)
set f to documentPath as alias -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
set tFile to (POSIX path of documentPath) as POSIX file
tell application id "com.microsoft.excel"
activate
open f
set screen updating to false
set wb to active workbook
set wsName to the name of the active sheet of wb
repeat with ctr from 1 to the count of worksheets in wb
log ctr
set sName to (the name of worksheet ctr of wb)
select worksheet sName
tell page setup object of active sheet
set zoom to false
set fit to pages wide to 1
set fit to pages tall to automatic
try
set center header to "&C" & "&KFF0000&"Consolas"" & sName
end try
try
run VB macro "'Personal.xlsb'!AddHeaderFooterAllSheet"
end try
end tell
autofit every column of range ("$A:$C") of worksheet sName of wb
end repeat
save wb
select worksheet wsName -- return to the original active sheet
set screen updating to true
set isRun to running
set wkbk1 to active workbook
alias PDFPath
save workbook as wkbk1 filename PDFPath file format PDF file format
close wkbk1 saving no
if not isRun then quit
end tell
end saveXlsxAsPDF
(Script text reformatted for posting by NG.)
This is more combined code for pptx, docx and xlsx conversion to pdf. The vb code in excel file is used to sheet name in header / footer with specific font. I use PDF X-change to get bookmarks in pdf if the need be.
Hope this helps
Hi @pankajsz.
The way to post AppleScript code on MacScripter is to use plain text and to put three backticks on separate lines above and below it:
```
AppleScript code.
```
This way, when posted, the code will appear in a box with an âOpen in Script Editorâ button under it. Iâve edited it for you above. See also the Markdown Reference here.
1 Like
Thanks, unfortunately the script throws an error.
Perhaps itâs due to something I did when reformatting the code text, although I hope not! 
Thereâs an isolated âalias PDFPathâ towards the bottom of the script which looks as if it should be part of a longer line, but I donât know anything scripting Excel.
What is the error? What line throws an error?
When I try to compile the script, I get an error in the âconsolasâ line and that word is highlighted. Escaping the quotes around the word allows it to compile. However, when I add a single backslash before each quote, it doesnât appear in this pageâs text preview. Double backslash before the quotes shows up as single quotes.
set center header to â&Câ & â&KFF0000&âConsolasââ & sName
This line has double backslashes when entered:
set center header to â&Câ & â&KFF0000&\âConsolas\ââ & sName
This line doesnât compile because of the Quotes.
How should it be?
Iâm guessing
set center header to â&Câ & â&KFF0000&\âConsolas\ââ & sName
Correct me if wrong.
I ran this script on 2 Excel files, and it created PDFs, but it then made each of the excel files unreadable. Excel failed in trying to recover data.
on saveXlsxAsPDF(documentPath, PDFPath)
set f to documentPath as alias -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
set tFile to (POSIX path of documentPath) as POSIX file
tell application id "com.microsoft.excel"
activate
open f
set screen updating to false
set wb to active workbook
set wsName to the name of the active sheet of wb
repeat with ctr from 1 to the count of worksheets in wb
log ctr
set sName to (the name of worksheet ctr of wb)
select worksheet sName
end repeat
select worksheet wsName -- return to the original active sheet
set screen updating to true
set isRun to running
set wkbk1 to active workbook
alias PDFPath
save workbook as wkbk1 filename PDFPath file format PDF file format
close wkbk1 saving no
if not isRun then quit
end tell
end saveXlsxAsPDF
This works for me. I have shifted page width and header setting to VB
Sub FormatExcelWithHeader()
Dim ws As Worksheet
Dim wb As Workbook
Dim sName As String
' Get the active workbook
Set wb = ActiveWorkbook
' Loop through all worksheets in the workbook
For Each ws In wb.Worksheets
' Get the worksheet name
sName = ws.name
' Select the worksheet
ws.Activate
' Set the header and page setup properties
With ws.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
On Error Resume Next ' Avoid errors for unsupported header/footer settings
.CenterHeader = "&C" & "&KFF0000" & sName
On Error GoTo 0
End With
Next ws
' Save the workbook
wb.Save
'MsgBox "Formatting completed and saved.", vbInformation, "Done"
End Sub
set center header to â&Câ & â&KFF0000&âConsolasââ & sName
on Consolas
Just read the rest, see you already found the error.
I compiled it but it does not run at all, well it selects the finder and that is ti.
**tell** *application* "Finder"
**activate**
**set** theItems **to** selection
**repeat** **with** itemRef **in** theItems
**set** theItemParentPath **to** (*container* **of** itemRef) **as** *text*
**set** theItemName **to** (name **of** itemRef) **as** *string*
**set** theItemExtension **to** (name extension **of** itemRef)
**set** theItemExtensionLength **to** (**count** theItemExtension) + 1
**set** theOutputPath **to** theItemParentPath & (*text* 1 **thru** (-1 - theItemExtensionLength) **of** theItemName)
**set** newfile **to** theOutputPath & ".pdf"
**set** oldfile **to** theItemParentPath & theItemName
**if** theItemExtension **is** "pptx" **then**
**my** savePowerPointAsPDF(oldfile **as** *text*, newfile **as** *text*)
**delay** 1
**else** **if** theItemExtension **is** "docx" **then**
**my** saveDocxAsPDF(oldfile **as** *text*, newfile **as** *text*)
**delay** 1
**else** **if** theItemExtension **is** "xlsx" **or** theItemExtension **is** "xlsm" **or** theItemExtension **is** "xls" **then**
**my** saveXlsxAsPDF(oldfile **as** *text*, newfile **as** *text*)
**delay** 1
**else** **if** theItemExtension **is** "pdf" **then**
**display dialog** "already pdf"
**end** **if**
**end** **repeat**
**end** **tell**
**on** savePowerPointAsPDF(documentPath, PDFPath)
**set** f **to** documentPath **as** *alias* -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
**tell** *application* "Microsoft PowerPoint"
**launch**
**open** f
**tell** *application* "System Events"
**tell** *process* "Microsoft PowerPoint"
**set** visible **to** *false*
**end** **tell**
**end** **tell**
**set** PDFPath **to** **my** createEmptyFile(PDFPath) -- the handler return a file object (this line must be inside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the saving command)
**delay** 0.5
**save** active presentation in PDFPath as *save as PDF*
**delay** 0.5
**close** active presentation saving *no*
**quit**
**end** **tell**
**end** savePowerPointAsPDF
**on** createEmptyFile(f)
**do shell script** "touch " & quoted form **of** POSIX path **of** f -- create file (this command do nothing when the PDF file exists)
**delay** 1
**return** (POSIX path **of** f) **as** *POSIX file*
**end** createEmptyFile
**on** saveDocxAsPDF(documentPath, PDFPath)
**set** f **to** documentPath **as** *alias* -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
**tell** *application* *id* "com.microsoft.word"
**open** f
**set** PDFPath **to** **my** createEmptyFile(PDFPath)
**delay** 1
**tell** active document
**save as** **it** file name PDFPath file format *format PDF*
**delay** 2
**close** saving *no*
**end** **tell**
**end** **tell**
**end** saveDocxAsPDF
**on** saveXlsxAsPDF(documentPath, PDFPath)
**set** f **to** documentPath **as** *alias* -- this line must be outside of the 'tell application "Microsoft PowerPoint"' block to avoid issues with the open command
**set** tFile **to** (POSIX path **of** documentPath) **as** *POSIX file*
**tell** *application* *id* "com.microsoft.excel"
**activate**
**open** f
**set** screen updating **to** *false*
**set** wb **to** active workbook
**set** wsName **to** **the** name **of** **the** active sheet **of** wb
**repeat** **with** ctr **from** 1 **to** **the** **count** **of** *worksheets* **in** wb
**log** ctr
**set** sName **to** (**the** name **of** *worksheet* ctr **of** wb)
**select** *worksheet* sName
**end** **repeat**
**select** *worksheet* wsName -- return to the original active sheet
**set** screen updating **to** *true*
**set** isRun **to** running
**set** wkbk1 **to** active workbook
*alias* PDFPath
**save workbook as** wkbk1 filename PDFPath file format *PDF file format*
**close** wkbk1 saving *no*
**if** **not** isRun **then** **quit**
**end** **tell**
**end** saveXlsxAsPDF
Hi SW108.
pankajszâs code is written to act on items that have already been selected in the Finder and which have either âpptxâ, âdocxâ, âxlxsâ, or âpdfâ name extensions. It saves the pdf versions in the same folders alongside the originals.
When I told pankajsz above to use âplain textâ when posting code, I meant unstyled text â ie. donât copy/paste compiled text directly from Script Editor. Sorry if that wasnât clear. Ideally, MacScripterâs site software should be able to deal with it, but unfortunately it doesnât. 
ha now it works, Thanks.
Insert correction into original script. Compiles and works (only excel tested).
@pankajsz:Thanks for the great script!
Since the thread has been reactivated, here is a minor revision that is applescript-only and a bit streamlined.
Specific to the original post, as SW108 noted, here is the syntax to get the sheet name centred and in red Consolas. Spaces between the codes are optional however you may want to include one between any two codes that generate text (rather than formatting), for example â&A &Dâ for the sheet name followed by the date.
set center header to "&C&KFF0000&\"Consolas\"&A"
Note that the above would need to be inside the appropriate tell blocks.
Here is microsoftâs page for the page setup header/footer codes.
Aside: Most work but I couldnât get the odd-seeming &K xx. S nnn code to do anything useful. By using â&K09S100 &Aâ, I could get it to colour the text a bluish green but I couldnât get it to any other colour. It seems easiest to go with the HTML-style hex colours.
Of course, it may need to be tweaked depending upon the required workflow.
set documentPath to (path to desktop as text) & "spreadsheet.xlsx"
set PDFPath to "two-tables.pdf"
tell application id "com.microsoft.excel"
activate
set wb to open workbook workbook file name documentPath
set screen updating to false
tell active workbook
set topSheet to the name of the active sheet
set sheetList to worksheets
repeat with eachSheet in sheetList
activate object eachSheet
tell page setup object of active sheet
set page orientation to landscape
-- set fit to pages wide to 1
-- codes: centre text, red Consolas font, print workbook tab/sheet
set simple to "&C&KFF0000&\"Consolas\"&A"
set center header to simple
end tell
autofit every column of range ("$A:$I") of active sheet
end repeat
activate object worksheet topSheet
save workbook as wb filename PDFPath file format PDF file format
end tell
set screen updating to true
end tell
This thread caught my attention as I have Excel files that go back years, and could be better archived as PDF files rather than the original Excel files. I ran your script but there are differences between the original Excel file and the resulting PDF file.
The Excel file has 50 columns, with an entry in column 50. The Excel file starts with the date 2/8/21 and ends with the date 12/31/21.
PDF starts with date 2/8/21 but ends with date 12/17/21, so it is missing the entry for 12/24/21 and 12/31/21.
The script doesnât address which rows or columns are printed (or exported). But even if it did restrict which were printed, it would generally cut it short. It wouldnât skip two columns and then print the last one.
Whatever is happening, it is likely in the current settings of the spreadsheet.
What happens when you manually save it as a pdf?
And just to check something out, put this in a new script and run it. Iâd like to know what the print area is set to. It should either encompass the entire table or be "".
tell application id "com.microsoft.excel"
tell active workbook
print area of page setup object of active sheet
end tell
end tell