The idea of the script you provided started me thinking, could the print area be set incorrectly. Checked and is was. Set it to the entire area of the spreadsheet, and now the PDF is identical to the spreadsheet. Thanks.
One last question. How would the script need to be changed to have the name of the PDF file be the same as the Excel file, and saved to the Desktop?
Both of these changes can be handled with the PDFPath variable.
You can set it to a complete path, including a new name. If you leave out a path and provide only a file name, then it will save in the same folder that the workbook is in.
These changes should do what you ask. Basically you replace the lines before the ‘tell excel’ statement.
set spreadsheetName to "spreadsheet.xlsx" -- name of source spreadsheet
set documentPath to (path to desktop as text) & spreadsheetName -- path to source spreadsheet
set text item delimiters to {"pdf", "xlsx"}
set pdfName to text items of spreadsheetName as text -- name for pending PDF
set pdfPath to (path to desktop as text) & pdfName -- path for pending PDF
set text item delimiters to {""}
Basically, they break up the name and path for both files before and after.
Note, this set-up is obviously tailored to a single file that you know in advance. It could easily be edited to choose the file, work with selected files or grab all spreadsheets in a folder. For multiple files, a repeat loop would have to be set up.
That script seems to be working (goes through the process) but nothing shows up on the Desktop.
Never mind, I caught my error, the two scripts needed to be combined.
This works perfectly.
set spreadsheetName to "Market Track 2022.xlsx" -- name of source spreadsheet
set documentPath to (path to desktop as text) & spreadsheetName -- path to source spreadsheet
set text item delimiters to {"pdf", "xlsx"}
set pdfName to text items of spreadsheetName as text -- name for pending PDF
set pdfPath to (path to desktop as text) & pdfName -- path for pending PDF
set text item delimiters to {""}
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
1 Like