Saving a *single* Excel worksheet as PDF

Scripting Forums AppleScript | Mac OS X
,
#1

Hello,

I’m hoping someone can give me some guidance on a problem I’m having. I have an Excel (for Mac) 2011 workbook which consists of several worksheets. Each worksheet provides information which feeds into one particular worksheet in the same file; I want to save that worksheet (“feedback”) as a PDF file, under a name that is picked up from a cell in that sheet.

I’ve tried a number of approaches, all of which have the same outcome - I end up with the “feedback” sheet saved as a PDF, but also all of the other sheets as separate PDFs as well. The filenames start with the name I want from the cell, but have the name of the individual sheets appended. For example, if the name I want to save under is “John” and the worksheets I have are called “Feedback”, “Rawmarks” and “Comments”, then what I want is for the sheet “Feedback” to be saved as “John.pdf”. What I actually end up with is three PDFs called “John Feedback.pdf”, “John Rawmarks.pdf” and “John Comments.pdf”.

I’ve noticed that if I change the file format from PDF to, e.g., CSV, then I get what I want - just the active sheet, Feedback, saved as John.csv. It seems that choosing a PDF format causes all of the sheets to be output.

Here’s the script I’ve used - I’ve also tried “save as” with identical results.


on run
	set ptd to path to desktop as string
	tell application "Microsoft Excel"
		set mySheet to sheet "feedback" of active workbook
		set filename to the value of cell "C7" as text
		save mySheet in (ptd & filename & ".pdf") as PDF file format
	end tell
end run

I’d be grateful for any suggestions. This is my first AppleScript, so I’m prepared to be told that I’ve done something stupid or messy!

Thanks

Nigel

Model: Macbook Pro
AppleScript: 2.4.2
Browser: Safari 534.53.10
Operating System: Mac OS X (10.7)

#2

This is one work around


set ptd to path to desktop as string

tell application "Microsoft Excel"
	
	set mySheet to sheet "feedback" of workbook "Workbook1.xlsm"
	set filename to the value of (cell "C7" of mySheet) as text
	
	set tempBook to make new workbook
	
	copy worksheet mySheet before sheet 1 of tempBook

	set display alerts to false
	repeat until (count of sheets of tempBook) = 1
		delete sheet 2 of tempBook
	end repeat
	
	save mySheet in (ptd & filename & ".pdf") as PDF file format
	
	close tempBook without saving
	set display alerts to true
	
end tell
#3

Brilliant - Many Thanks!

#4

Hey all.

Just updated to Excel 2016, and now my save as pdf script isn’t working anymore.

Any of you that have a working script?

Thanks in advance