Script to print excel to PDF and save

Tagged: , ,

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Kailey Scott Kailey Scott 5 months, 2 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #37787
    Profile photo of Kailey Scott
    Kailey Scott
    Participant

    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()
    
    
    #37854
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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()
    
    
    #37857
    Profile photo of Kailey Scott
    Kailey Scott
    Participant

    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()
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.