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.