Powershell Script to Print Excel Files Landscaped to PDFs

The PowerShell script posted to this site, will allow you take files saved to your My Documents folder and print them as landscaped PDFs. Follow the instructions on how to use PowerShell posted in the Tip of the Night for July 24, 2015 . You can change the setting of $homedocuments = "C:\Users\YourUserName\Documents" to whatever folder you specify.

Function PrintXL($FileName) {

$xlPortrait = 1

$xlLandscape = 2

$xlPrintNoComments = -4142

$xlPaperLetter = 1

$xlPaperLedger=4

$xlPaperLegal = 5

$xlPaperFolio=14

$xlPaper11x17=17

$xlDownThenOver = 1

$xlAutomatic = -4105

$xl = New-Object -comobject excel.application

$xl.Visible = $true

$xl.DisplayAlerts = $False

$wb = $xl.Workbooks.Open($FileName)

$ws = $wb.Worksheets.Item(1)

#== PRINT SETUP TO FIT DATA TO ONE PAGE

$ws.PageSetup.PrintTitleRows = "$1:$1"

$ws.PageSetup.PrintTitleColumns = ""

$ws.PageSetup.LeftHeader = ""

$ws.PageSetup.CenterHeader = "&""MS Sans Serif,Bold""&14&A"

$ws.PageSetup.RightHeader = ""

$ws.PageSetup.LeftFooter = ""

$ws.PageSetup.CenterFooter = "Page &P"

$ws.PageSetup.RightFooter = ""

$ws.PageSetup.LeftMargin = $xl.InchesToPoints(0.25)

$ws.PageSetup.RightMargin = $xl.InchesToPoints(0.25)

$ws.PageSetup.TopMargin = $xl.InchesToPoints(0.5)

$ws.PageSetup.BottomMargin = $xl.InchesToPoints(0.5)

$ws.PageSetup.HeaderMargin = $xl.InchesToPoints(0.25)

$ws.PageSetup.FooterMargin = $xl.InchesToPoints(0.25)

$ws.PageSetup.PrintHeadings = $False

$ws.PageSetup.PrintGridlines = $true

$ws.PageSetup.PrintComments = $xlPrintNoComments

# $ws.PageSetup.PrintQuality = 600 $ws.PageSetup.CenterHorizontally = $FALSE

$ws.PageSetup.CenterVertically = $FALSE

$ws.PageSetup.Orientation = $xlLandscape

$ws.PageSetup.Draft = $False

$ws.PageSetup.PaperSize = 5

$ws.PageSetup.FirstPageNumber = $xlAutomatic

$ws.PageSetup.Order = 1

$ws.PageSetup.BlackAndWhite = $FALSE

$ws.PageSetup.Zoom = $False

$ws.PageSetup.FitToPagesWide = 1

$ws.PageSetup.FitToPagesTall = 9999

$range = $ws.usedRange

$r = $range.rows.count

$c = $range.Columns.count

$S = $ws.Cells.Item($r,$c)

$S = $S.Address()

$U = $ws.Cells.Item(1, 1)

$U = $U.Address()

$T = $U + ":" + $S

$ws.PageSetup.PrintArea = $T

$ws.Printout()

$wb.Close(0)

# Quit Excel

$xl.quit()

spps -n excel

}

$homedocuments = "C:\Users\YourUserName\Documents"

# Windows 7 does not have a %homedocuments%

$s = dir $homedocuments\*.xls? -Recurse

$s | foreach -process {

Write-Host $_

PrintXL($_)

}