I have a script that creates AD reports and then saves them to my desktop. I’ve created another script to attach it as an SMTP email to email myself. I will be setting this script as a scheduled task to run monthly, but want it to overwrite the previous month’s file. (It emails me the attachment with the $date, which I have an email filter set up to save it locally). I always get a prompt that says “A file named XXXXX already exists. Do you want to replace it ?” Can I set it to automatically replace it?
write-Host “Saving file in $env:userprofile\desktop”
$workbook.SaveAs(“$env:userprofile\desktop\GSSActiveDirectoryReport.xlsx”)
You could instead just have your script delete the old file prior to executing SaveAs.
Or, as a much better alternative, write a script that dumps your data into a SQL (or SQL Express) database. Then, use SQL Server Reporting Services to generate the report. Once you set the report up, all you’ll have to do is update the data (which is easy); SSRS can generate and deliver reports, on a schedule, in a variety of formats. It’s a WAY better way to do reporting than the clunky Excel object.
You could instead just have your script delete the old file prior to executing SaveAs.
Or, as a much better alternative, write a script that dumps your data into a SQL (or SQL Express) database. Then, use SQL Server Reporting Services to generate the report. Once you set the report up, all you’ll have to do is update the data (which is easy); SSRS can generate and deliver reports, on a schedule, in a variety of formats. It’s a WAY better way to do reporting than the clunky Excel object.
Excel automation is painful. Period.
If you HAVE to spit out a xlsx file, then do what Don suggests and have your script delete the existing file before creating a new one. That will probably be the easiest solution and the easiest to troubleshoot if you have to.