CODEWITHSUNDEEP
powershell
D.Minella
D.Minella

Reputation: 13

Convert Excel formats to pdf - Powershell

My question's about the error at saving file at this convert script:

https://github.com/idf/batch_dump/blob/master/office_convert.ps1

#Error
Exception when calling "InvokeMember" with "6" argument (s): "Object does not match destination type."
In the line: 28 character: 1
+ $workbook = $objExcel.Workbooks.PSBase.GetType().InvokeMember('Open', ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : TargetException

saving C:\Test\CD_FEV_1.pdf

You can not call a method on a null value expression.
In the line: 30 character: 1
+ $workbook.ExportAsFixedFormat($xlTypePDF, $filepath, $xlQualityStanda ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You can not call a method on a null value expression.
In the line: 31 character: 1
+ [void]$workbook.PSBase.GetType().InvokeMember('Close', [Reflection.Bi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

As i haven't experience in powershell i don't understand the metod they call at script.

Upvotes: 1

Views: 3772

Answers (1)

Rick Eubanks
Rick Eubanks

Reputation: 81

I'm still new to powershell myself, so can't answer specifically what was causing the error, however I think I found the solution. It seemed to be with only the Excel conversion. The Word and PPT conversions worked flawless. I've changed the code for that portion and it seems to be working now:

$folderpath =  $(get-location) 
 Add-type -AssemblyName office
#Convert Word formats to pdf
$wdFormatPDF = 17
 $word = New-Object -ComObject word.application
 $word.visible = $false
 $fileTypes = "*.docx","*doc"
 $wordFiles = Get-ChildItem -path $folderpath -include $fileTypes -Recurse
foreach ($d in $wordFiles) {
 $path = ($d.fullname).substring(0,($d.FullName).lastindexOf("."))
 "Converting $path to pdf ..."
 $doc = $word.documents.open($d.fullname)
 $doc.saveas([ref] $path, [ref]$wdFormatPDF)
 $doc.close()
 }
$word.Quit()
#Convert Excel formats to pdf
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] 
$excelFiles = Get-ChildItem -Path $folderpath -include *.xls, *.xlsx -recurse 
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false 
foreach($wb in $excelFiles) 
{ 
 $filepath = Join-Path -Path $folderpath -ChildPath ($wb.BaseName + ".pdf") 
 $workbook = $objExcel.workbooks.open($wb.fullname, 3) 
 $workbook.ActiveSheet.PageSetup.Orientation = 2
 $objExcel.PrintCommunication = $false
 $workbook.ActiveSheet.PageSetup.FitToPagesTall = $false
 $workbook.ActiveSheet.PageSetup.FitToPagesWide = 1
 $objExcel.PrintCommunication = $true
 $workbook.Saved = $true 
"saving $filepath" 
 $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) 
 $objExcel.Workbooks.close() 
} 
$objExcel.Quit()
#Convert Powerpoint formats to pdf
$ppFormatPDF = 2
 $ppQualityStandard = 0
 $p = new-object -comobject powerpoint.application
$p.visible = [Microsoft.Office.Core.MsoTriState]::msoTrue
 $ppFiletypes = "*.pptx","*ppt"
 $ppFiles = Get-ChildItem -path $folderpath -include $ppFiletypes -Recurse
foreach ($s in $ppFiles) {
 $pppath = ($s.fullname).substring(0,($s.FullName).lastindexOf("."))
 "Converting $pppath to pdf ..."
 $ppt = $p.presentations.open($s.fullname)
$ppt.SavecopyAs($pppath, 32) # 32 is for PDF
 $ppt.close()
 }
$p.Quit()
 $p = $null
[gc]::collect()
 [gc]::WaitForPendingFinalizers()

Upvotes: 2

Related Questions