Overwrite existing file without user prompts

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Daniel Brooks Daniel Brooks 2 years, 1 month ago.

  • Author
    Posts
  • #20435
    Profile photo of Daniel Brooks
    Daniel Brooks
    Participant

    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")

  • #20436
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Ugh. Excel automation.

    Take a look at the documentation for the SaveAs method, at http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx. If one of those parameters doesn't do what you need, then no.

    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.

  • #20442
    Profile photo of Charles Downing
    Charles Downing
    Participant

    Ugh. Excel automation.

    Take a look at the documentation for the SaveAs method, at http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx. If one of those parameters doesn't do what you need, then no.

    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.

  • #20443
    Profile photo of Daniel Brooks
    Daniel Brooks
    Participant

    Great suggestion guys, that's exactly what I did.

You must be logged in to reply to this topic.