How to print to PDF a sheet in EXCEL Office 2016 that contains charts/images?


(Jose Zaldivar) #1

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.

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "sample.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

(Ed Stockly) #2
tell application "Microsoft Excel"
   save as document 1 ¬
     filename pdfFilePath ¬
     file format PDF file format 
end tell

Something like that?


(Jose Zaldivar) #3

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

(Chip Towner) #4

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!

----------------------- Script Below ---------------

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-------------------------


(Jonas Whale) #5

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

@josezald101 filename 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.