Rename file and then save as .csv

PowerShell Help
1

Hello,

Any idea what went wrong here, I am able to rename the file with a date string_100.xlsx but the second command to do a save as .csv is failing. The “_100” after the date string is necessary for me to trigger an upload.

If I remove “_100” the script works and saves the .xlsx to a .csv file.

$datestring = (Get-Date).ToString(“yyyyMMdd”)

dir ‘\Sun\D$\Works\ShellTRP\Test.xlsx’|Rename-Item -NewName Test_$datestring’_100’.xlsx

$xlCSV = 6
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open(“\Sun\D$\Works\ShellTRP\Test_$datestring’100’.xlsx")
$Workbook.SaveAs("\Sun\D$\Works\ShellTRP\Test$datestring’_100’.csv”,$xlCSV)
$Excel.quit()

2

Pat,
Welcome to the forum. :wave:t4:

Please … When you post code, sample data, console output or error messages format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

I’d recommend instead of the Excel com object to use the great module from Doug Finke

And I’d recommend to use the sub expression operator when expanding variables in strings. :wink:

$datestring = (Get-Date).ToString('yyyyMMdd')

Get-ChildItem -Path '\\Sun\D$\Works\ShellTRP\Test.xlsx' |
    Rename-Item -NewName "Test_$($datestring)_100.xlsx"

$xlCSV = 6
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("\\Sun\D$\Works\ShellTRP\Test_$($datestring)_100.xlsx")
$Workbook.SaveAs("\\Sun\D$\Works\ShellTRP\Test$($datestring)_100.csv", $xlCSV)
$Excel.quit()
3

Thank you Olaf, that worked.