CODEWITHSUNDEEP
windowsexcelcsvpowershelldialog
Vibhav MS
Vibhav MS

Reputation: 163

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

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

Upvotes: 6

Views: 19107

Answers (3)

James Duncan
James Duncan

Reputation: 21

do an if statement to check if the file exists, delete it, save the new one as that file.

If(Test-Path $filepath)
{
    Remove-Item $filepath -verbose
 }
$workbook.SaveAS($filepath)

Upvotes: 2

Sandesh Honnatti
Sandesh Honnatti

Reputation: 141

Use display alerts statement before the SaveAs call:

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

Upvotes: 14

RuntimeError
RuntimeError

Reputation: 73

$excel.DisplayAlerts = $false worked for me.

Upvotes: 4

Related Questions