Prevent overwrite pop-up when writing into excel using a powershell

Question

I have a bunch of csv files from which I am writing data into a particular worksheet of an existing excel file. I have the below code and it works while looping through the CSV files and writing data into the existing worksheet

$CSVs ="rpt.test1", "rpt.test2"

foreach ($csv in $CSVs) { $csv_name = $csv echo "n - - - $sav_name - - -n"

foreach ($source in $Sources)
{
    $src = $source

    $inputCSV = "C:\Users\xxxx\Desktop\$src.$csv_name.csv"
    $Path = "C:\Users\xxxx\Desktop\$csv_name.xlsx"

    ### Create a new Excel Workbook with one empty sheet
    #$excel = New-Object -ComObject excel.application 
    #$workbook = $excel.Workbooks.Add(1)
    #$worksheet = $workbook.worksheets.Item(1)


    # Open the Excel document and pull in the 'Play' worksheet
    $excel = New-Object -Com Excel.Application
    $Workbook = $Excel.Workbooks.Open($Path) 
    $page = 'data'
    $worksheet = $Workbook.worksheets | where-object {$_.Name -eq $page}

    # Delete the current contents of the page
    $worksheet.Cells.Clear() | Out-Null

    ### Build the QueryTables.Add command
    ### QueryTables does the same as when clicking "Data » From Text" in Excel
    $TxtConnector = ("TEXT;" + $inputCSV)
    $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
    $query = $worksheet.QueryTables.item($Connector.name)

    ### Set the delimiter (, or ;) according to your regional settings
    $query.TextFileOtherDelimiter = $Excel.Application.International(5)

    ### Set the format to delimited and text for every column

    $query.TextFileParseType  = 1
    $query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
    $query.AdjustColumnWidth = 1

    ### Execute & delete the import query
    $query.Refresh()
    $query.Delete()

    $Workbook.SaveAs($Path,51)
    $excel.Quit()
}

Since it is an existing excel workbook, it throws a pop-up every time a file is being over-written. Have more than 15 CSV's and clicking Yes everytime is annoying

I have tried

$excel.DisplayAlerts = FALSE

and I have tried

$excel.CheckCompatibility = $False 

and pretty much anything available on the internet. I am still learning powershell and at my wits end trying to stop this. Any help would be very much appreciated

Solution 1

Use display alerts statement before the SaveAs call:

$excel.DisplayAlerts = $false;
$excel.ActiveWorkbook.SaveAs($xlsFile);

Solution 2

$excel.DisplayAlerts = $false worked for me.

Comments

[Suraj Kumar]

Please explain how/why it worked for the given problem?