Script to print excel to PDF and save

PowerShell Help
1

Hello,

I found a script in another forum that works well for reading an excel file, opening it, printing it to pdf, and then saving and closing. What I can’t figure out is how to modify the script so that it only prints one page of the workbook instead of all of it. Does anyone else know how to edit this so I can accomplish this?

#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "C:\Source Path\"  #Path to Excel spreadsheets to save to PDF
[string]$savepath = "C:\Destination Path\"
[string]$dToday = Get-Date -Format "yyyyMMdd"

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] 
$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse 

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false   #Do not open individual windows

foreach($wb in $excelFiles) 
{ 
# Path to new PDF with date 
 $filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf") 
 # Open workbook - 3 refreshes links
 $workbook = $objExcel.workbooks.open($wb.fullname, 3)
 $workbook.RefreshAll()
 
 # Give delay to save
 Start-Sleep -s 5
 
 # Save Workbook
 $workbook.Saved = $true 
"saving $filepath" 
 #Export as PDF
 $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) 
 $objExcel.Workbooks.close() 
} 
$objExcel.Quit()
2

Hi Kailey,

The ExportAsFixedFormat method is documented on MSDN. From looking at the documentation, the method accepts two arguments ‘From’ and ‘To’ that you can use to specify which pages you want to export. Lines with #mb are my edits. I have tested it and it works OK. I don’t believe you can skip the other arguments so I have had to define those as well.

#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "F:\__Temp\excel"  #Path to Excel spreadsheets to save to PDF
[string]$savepath = "F:\__Temp\PDF"
[string]$dToday = Get-Date -Format "yyyyMMdd"

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

$xlQuality = "Microsoft.Office.Interop.Excel.xlQualityStandard" -as [type] #mb
$xlFromPage = 1 #mb
$xlToPage = 1 #mb


$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse 

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false   #Do not open individual windows

foreach($wb in $excelFiles) 
{ 
    # Path to new PDF with date 
     $filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf") 
     # Open workbook - 3 refreshes links
     $workbook = $objExcel.workbooks.open($wb.fullname, 3)
     $workbook.RefreshAll()
 
     # Give delay to save
     Start-Sleep -s 5
 
     # Save Workbook
     $workbook.Saved = $true 
    "saving $filepath" 
     #Export as PDF
     $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath, $xlQuality, $false, $true, $xlFromPage, $xlToPage) #mb
     $objExcel.Workbooks.close() 
} 
$objExcel.Quit()
3

Thanks Matt. I found another way that worked late last night. You can call the worksheet com object and then specify the worksheet instead of the workbook in the save and export section of the code. It seems to work well. I put #SK by those lines of code.

#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "C:\Source Path\"  #Path to Excel spreadsheets to save to PDF
[string]$savepath = "C:\Destination Path\"
[string]$dToday = Get-Date -Format "yyyyMMdd"

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] 
$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse 

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false   #Do not open individual windows

foreach($wb in $excelFiles) 
{ 
# Path to new PDF with date 
 $filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf") 
 # Open workbook - 3 refreshes links
 $workbook = $objExcel.workbooks.open($wb.fullname, 3)
 $worksheet = $objExcel.worksheets.item(1) #SK
 $workbook.RefreshAll()
 
 # Give delay to save
 Start-Sleep -s 5
 
 # Save Workbook
 $workbook.Saved = $true 
"saving $filepath" 
 #Export as PDF
 $worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) #SK
 $objExcel.Workbooks.close() 
} 
$objExcel.Quit()
4

Hello room

The code Matt provided works well for me creating the pdfs, but how may this be modified to handle noncontinuous indices (eg: 27,34,89)?

5

You could attempt to loop through the indexes:

#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "F:\__Temp\excel"  #Path to Excel spreadsheets to save to PDF
[string]$savepath = "F:\__Temp\PDF"
[string]$dToday = Get-Date -Format "yyyyMMdd"

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

$xlQuality = "Microsoft.Office.Interop.Excel.xlQualityStandard" -as [type] #mb
$xlFromPage = 1 #mb
$xlToPage = 1 #mb


$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse 

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false   #Do not open individual windows

foreach($wb in $excelFiles) { 
    # Path to new PDF with date 
     $filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf") 
     # Open workbook - 3 refreshes links
     $workbook = $objExcel.workbooks.open($wb.fullname, 3)
     $workbook.RefreshAll()
 
     # Give delay to save
     Start-Sleep -s 5
 
     # Save Workbook
     $workbook.Saved = $true 
    "saving $filepath" 

     #define indexes
     $indexes = 27,34,89
     
     foreach ($index in $indexes) {
        #$xlFromPage = 1 #mb
        #$xlToPage = 1 #mb
        "Exporting index {0}..." -f $index
        #Export as PDF
        try {
            $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath, $xlQuality, $false, $true, $index, $index) #mb
            "Successfully exported index {0}" -f $index
        }
        catch {
            "Unable to export index {0}. {1}" -f $index, $_
        }
     }

     $objExcel.Workbooks.close() 
} 
$objExcel.Quit()