CODEWITHSUNDEEP
excelpdfpowershell
user2547166
user2547166

Reputation: 23

How can I convert .XLS & .XLXS files to .PDF format and return them to each original subfolder in Powershell?

I currently have a powershell script that works well for converting Excel spreadsheets to PDF files but my problem is the new files appear in the parent folder rather than the specific subfolders from whence they came. My current script looks like this:

$path = "c:\users\me\Documents\Test\sub-test" 
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] 
$filter = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse 
$objExcel = New-Object -ComObject excel.application 
$objExcel.visible = $false 
foreach($wb in $filter) 
{ 
$filepath = join-path -Path $path -ChildPath ($wb.basename + ".pdf")
$workbook = $objExcel.workbooks.open($wb.fullname, 3) 
$workbook.Saved = $true 
"saving $filepath" 
$workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) 
$objExcel.Workbooks.close() 
} 
$objExcel.Quit()

Now, if I change the Childpath parameters in $filepath to ($wb.fullname + ".pdf") the full correct paths are listed when run but an error happens because the .xls is still included in the new filename. My ultimate goal is to actually replace all of the excel files with pdf copies but I was just trying to figure this part out first and then I can simply write a script to delete all the excel files later. Any input would be much appreciated. Thanks, CJ

Upvotes: 2

Views: 3383

Answers (1)

latkin
latkin

Reputation: 16812

You can use the FullName property and replace just the extension like this:

($wb.FullName -replace '\.xlsx?$', '.pdf')

This should work for files with extension .xls or .xlsx (I assume XLXS in question title is a typo?)

Upvotes: 1

Related Questions