Powershell to convert Excel to PDF not working?

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 4 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Powershell to convert Excel to PDF not working?

Post by ITEngineer »

Hi Folks,

I need some help in troubleshooting the below Powershell script to convert bulk Excel spreadsheet into .PDF files and save it on the custom directory:

Code: Select all

$Files=Get-ChildItem -path 'C:\Logs' -recurse -include "*.XLS*"

$counter = 0
$filesProcessed = 0
$Excel = New-Object -ComObject Excel.Application

Foreach ($File in $Files) {
    $Name="$(($File.FullName).substring(0, $File.FullName.lastIndexOf("."))).pdf"
    if ((Test-Path $Name) -And (Get-Item $Name).length -gt 3kb) {
        echo "skipping $($Name), already exists"
        continue
    }

    echo "$($filesProcessed): processing $($File.FullName)"
    $Doc = $Excel.Documents.Open($File.FullName)
    $Doc.SaveAs($Name, 17)
    $Doc.Close()
    if ($counter -gt 100) {
        $counter = 0
        $Excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
        $Excel = New-Object -ComObject Excel.Application
    }
    $counter = $counter + 1
    $filesProcessed = $filesProcessed + 1
}
The above code should be able to convert *.XLS, *.XLSX files into .PDF. But there is an error like below:"
You cannot call a method on a null-valued expression. At C:\Intel\Convert-ToPDF.PS1:15 char:5
+ $Doc = $Excel.Documents.Open($File.FullName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Would it be possible to save the result into different folder /directory?

Thanks in advance.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Powershell to convert Excel to PDF not working?

Post by jvierra »

Code: Select all

$xl = New-Object -ComObject Excel.Application
$counter = 0
Get-ChildItem -path C:\Logs\* -include *.xls,*.xlsx -recurse |
    ForEach-Object{
        $counter++
        $pdfname = $_.FullName -replace 'xlsx$|xls$','pdf'

        $f = Get-Item $pdfname -ErrorAction SilentlyContinue
        if($f -and $f.Length -gt 3kb){
            Write-Host "skipping $pdfname, already exists"
            continue
        }

        Write-Host ('{0}:Processing {1}' -f $counter, $_)
        $wb = $xl.Documents.Open($_.FullName)
        $wb.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, $pdfname)

        $wb.Close()
    
        if($counter -gt 100){break}
    }

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Powershell to convert Excel to PDF not working?

Post by ITEngineer »

Hi Mr. Vierra,

Thanks for the reply

What is this counter limiter used for?
if($counter -gt 100){break}
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Powershell to convert Excel to PDF not working?

Post by jvierra »

It exits the loop.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Powershell to convert Excel to PDF not working?

Post by ITEngineer »

jvierra wrote: Tue Nov 05, 2019 10:23 pm

Code: Select all

$xl = New-Object -ComObject Excel.Application
$counter = 0
Get-ChildItem -path C:\Logs\* -include *.xls,*.xlsx -recurse |
    ForEach-Object{
        $counter++
        $pdfname = $_.FullName -replace 'xlsx$|xls$','pdf'

        $f = Get-Item $pdfname -ErrorAction SilentlyContinue
        if($f -and $f.Length -gt 3kb){
            Write-Host "skipping $pdfname, already exists"
            continue
        }

        Write-Host ('{0}:Processing {1}' -f $counter, $_)
        $wb = $xl.Documents.Open($_.FullName)
        $wb.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, $pdfname)

        $wb.Close()
    
        if($counter -gt 100){break}
    }

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
The code failed:
PS C:\WINDOWS\system32> C:\Convert-ToPDF.PS1
1:Processing C:\Logs\MultipleMembers.xls
You cannot call a method on a null-valued expression.
At C:\Convert-ToPDF.PS1:16 char:9
+ $wb = $xl.Documents.Open($_.FullName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Method invocation failed because [System.IO.FileInfo] does not contain a method named 'ExportAsFixedFormat'.
At C:\Convert-ToPDF.PS1:17 char:9
+ $wb.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFix ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

Method invocation failed because [System.IO.FileInfo] does not contain a method named 'Close'.
At C:\Convert-ToPDF.PS1:19 char:9
+ $wb.Close()
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

2:Processing C:\Logs\OnPremise.xlsx
You cannot call a method on a null-valued expression.
At C:\Convert-ToPDF.PS1:16 char:9
+ $wb = $xl.Documents.Open($_.FullName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Method invocation failed because [System.IO.FileInfo] does not contain a method named 'ExportAsFixedFormat'.
At C:\Convert-ToPDF.PS1:17 char:9
+ $wb.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFix ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

Method invocation failed because [System.IO.FileInfo] does not contain a method named 'Close'.
At C:\Convert-ToPDF.PS1:19 char:9
+ $wb.Close()
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

Exception calling "ReleaseComObject" with "1" argument(s): "The object's type must be __ComObject or derived from __ComObject.
Parameter name: o"
At C:\Convert-ToPDF.PS1:25 char:1
+ [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentException

0
Nothing is generated?
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Powershell to convert Excel to PDF not working?

Post by jvierra »

You either have a corrupt XLS file or the file is not an X=Excel file.

The following would be the safe way to do this:

Code: Select all

$xl = New-Object -ComObject Excel.Application
$counter = 0
Get-ChildItem -path C:\Logs\* -include *.xls,*.xlsx -recurse |
    ForEach-Object{
        Try{
            $counter++
            $pdfname = $_.FullName -replace 'xlsx$|xls$','pdf'

            $f = Get-Item $pdfname -ErrorAction SilentlyContinue
            if($f -and $f.Length -gt 3kb){
                Write-Host "skipping $pdfname, already exists"
            }else{

                Write-Host ('{0}:Processing {1}' -f $counter, $_)
                $wb = $xl.Documents.Open($_.FullName)
                $wb.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, $pdfname)

                $wb.Close()
            
                if($counter -gt 100){break}
            }
        }
        Catch{
            Write-Host $_ -Fore Red
        }
    }

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Powershell to convert Excel to PDF not working?

Post by ITEngineer »

Still the same issue?

Code: Select all

PS C:\WINDOWS\system32> C:\Logs\Convert-ToPDF.PS1
1:Processing C:\Logs\MultipleMembers.xls
You cannot call a method on a null-valued expression.
2:Processing C:\Logs\OnPremise.xlsx
You cannot call a method on a null-valued expression.
Exception calling "ReleaseComObject" with "1" argument(s): "The object's type must be __ComObject or derived from __ComObject.
Parameter name: o"
At C:\Convert-ToPDF.PS1:29 char:1
+ [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException
 
0
Why is that still happening considering the script can and only look for .XLS and .XLSX files?
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Powershell to convert Excel to PDF not working?

Post by jvierra »

You seem to have issue with Excel. Have you tried to restart your system. I suspet you heav 100s of copies of Excel running hidden due to all of the errors.
Crumbley
Posts: 1
Last visit: Thu Nov 21, 2019 5:53 am

Re: Powershell to convert Excel to PDF not working?

Post by Crumbley »

What version of Excel is having these issues with PowerShell?
This topic is 4 years and 4 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked