I have been implementing an EXCEL solution for a client that uses EXCEL 2016 on a MAC. After a deep dive on AppletScript, I managed to replace many of the VBA code to AppleScript so EXCEL behaves properly on MAC. However, one thing that VBA does not do well is exporting charts/images to PDF. This is a big show stopper for me.
The good news is that if I save the file as PDF (manually), the charts/images are printed correctly. So, after watching some of the cool things this tool can do, I was wondering if it is possible to create a script that will mimic the process of printing an EXCEL sheet to PDF and output the PDF to a folder.
Is this something latenightsw can help with?
Thanks
This is the VBA code that does not print to PDF the charts.
Thanks Ed, This is close to what’s needed. Given that the script will be run from VBA using AppleScriptTask, how would the script look like?
How do you tell what sheet to print? In the example below, I just want to print “MySheetName”.
This is very cool software!
(*
filename: SAVEEXCELSHEETASPDF.scpt
Save this script file in /Users//Library/Application Scripts/com.microsoft.Excel
Source: How to print to PDF a sheet in EXCEL Office 2016 that contains charts/images?
from VBA, call this function:
RunMyScript = AppleScriptTask(“SAVEEXCELSHEETASPDF.scpt”, SaveEXCELSheetasPDF", “~/Desktop/MyReportpdf.PDF;MySheetName”)
--------------------------------------------------------
To test how this script works, simple comment this out and update the parameters.
have the EXCEL Sheet you want to print already open in EXCEL, and rename the paths
SaveEXCELSheetasPDF("~/Desktop/MyReportpdf.PDF;MySheetName")
Credits: Late Night Software
*)
on SaveEXCELSheetasPDF(paramString)
set {strFilePathPDF, strSheetName} to SplitString(paramString, ";")
try
tell application "Microsoft Excel"
save as document 1 ¬
filename strFilePathPDF ¬
file format PDF file format
end tell
end try
end SaveEXCELSheetasPDF
on SplitString(TheBigString, fieldSeparator)
tell AppleScript
set oldTID to text item delimiters
set text item delimiters to fieldSeparator
set theItems to text items of TheBigString
set text item delimiters to oldTID
end tell
return theItems
end SplitString
Since this post is quite recent & directly applies I wanted to insert myself into the conversation. I am trying to create a script I can use to save an entire Excel Document (not a single sheet) as a PDF.
I am finding that the final save step is not working. I get an ambiguous error from “Microsoft Excel got an error: Parameter Error” and the error window had a -50 in the lower left corner which I believe is the error code (the screen shot of the error screen from Script Debugger is attached).
The script I am using is included below and the save step is the same as the example in the previous posts. I have tried several different variants of the save step using different dictionary versions of save (Standard Suite & MS Office Suite) and all result in the same error. I am wondering if the issue is either sandbox related or perhaps a setting in Excel that I have not uncovered.
I have “borrowed” the SplitString function from the previous posts so thanks!
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
on open theFileList
local currentPOSIXfile, CurrentFileAlias, txtList, currentPOSIXPDFFileRef, PDFPOSIXfileName, savedPDFfile
local wkbook1, isRun
repeat with CurrentFileAlias in theFileList
set currentPOSIXfile to (the POSIX path of CurrentFileAlias)
set PDFPOSIXfileName to my replaceExtension(currentPOSIXfile, "pdf") as string
-- set currentPOSIXPDFFileRef to POSIX file PDFPOSIXfileName
tell application "Microsoft Excel"
set isRun to running
set wkbook1 to open workbook workbook file name currentPOSIXfile
save as document 1 filename PDFPOSIXfileName file format PDF file format
close wkbook1 saving no
end tell
end repeat
end open
on run
-- Handle the case where the script is launched without any dropped files
open (choose file with multiple selections allowed)
end run
-- The following was taken from the ScriptDebugger forums.
on SplitString(TheBigString, fieldSeparator)
tell AppleScript
set oldTID to text item delimiters
set text item delimiters to fieldSeparator
set theItems to text items of TheBigString
set text item delimiters to oldTID
end tell
return theItems
end SplitString
on replaceExtension(filename, newExt)
local txtList, newFileName, numItems
set txtList to my SplitString(filename, ".")
set numItems to number of items in txtList
if numItems is 1 then
-- If there is only 1 item in list there is no extension
-- so we will simply add the .pdf extension
set newFileName to txtList & "." & newExt
else
-- Other wise the last item in the list is the extension so
-- we will replace it with the .pdf extension and then
-- rebuild the filename from these pieces
set last item of txtList to newExt
set newFileName to ""
set itemNum to 0
repeat numItems times
set itemNum to itemNum + 1
if itemNum is 1 then
set newFileName to item itemNum in txtList
else
set newFileName to newFileName & "." & item itemNum in txtList
end if
end repeat
end if
end replaceExtension
----------------Screen shot of error screen-------------------------
Save as PDF is not working correctly with Excel 2011.
I suspect it’s the same with 2016…
Here’s a workaround to save the active sheet including charts:
tell application "Microsoft Excel"
activate
set activeSheet to entry_index of active sheet
set visible of sheets whose entry_index ≠ activeSheet to false
save as active sheet file format PDF file format filename "fileName.pdf"
set visible of sheets to true
end tell
@josezald101filename has to be in HFS format if you pass a full path (not a posix path or an alias).
(e.g. “HardDrive:Users:UserName:Desktop:FileName.pdf”)
If you pass only the name (e.g “FileName.pdf”) the file will be saved in the container of the Excel file.
Note that the sheet name will be added at end of file name.