Script to print excel to PDF and save

Tagged: , ,

This topic contains 4 replies, has 4 voices, and was last updated by  Rob Simmers 4 months, 2 weeks ago.

  • Author
    Posts
  • #37787

    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

    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

    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()
  • #69930

    Allan
    Participant

    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)?

  • #69942

    Rob Simmers
    Participant

    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()
    

You must be logged in to reply to this topic.