How do I print all the Excel files from a directory?

This Function Prints out all the Excel files in “My Documents” in Landscape mode. You can change the line: “$ws.PageSetup.Orientation = $xlLandscape”  to  “$ws.PageSetup.Orientation = $xlPortrait”

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($_)
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s