Stop Save As dialogue box excel in powershell

Christopher Jack 1,601 Reputation points
2021-04-23T08:55:44.683+00:00

Hi,

I have the following code

## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$workbook = $xlsObj.Workbooks.Open($filename1)

$workbook.ActiveSheet.Cells.Item(1,4) = ""
$workbook.ActiveSheet.Cells.Item(1,10) = ""

## - Saving Excel file - if the file exist do delete then save
$xlsFile = $filename1;

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

However it is coming up with the save as dialogue box instead of just saving the excel workbook.
I am wanting it just to save.

Can one help?

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,621 questions
No comments
{count} votes

Accepted answer
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 18,351 Reputation points Microsoft Employee
    2021-04-23T09:35:10.25+00:00

    Hi,

    Your file is being used by excel so you cannot remove it. Why not just invoke the save() method?

    ## - Create an Excel Application instance:  
    $xlsObj = New-Object -ComObject Excel.Application;  
          
    ## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)  
    $xlsObj.Visible = 0;  
    $workbook = $xlsObj.Workbooks.Open($filename1)  
          
    $workbook.ActiveSheet.Cells.Item(1,4) = ""  
    $workbook.ActiveSheet.Cells.Item(1,10) = ""  
          
    $xlsObj.ActiveWorkbook.Save()  
          
    ## Quit Excel and Terminate Excel Application process:  
    $xlsObj.Quit();   
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)   
    Stop-Process -Name "EXCEL"  
    

    Best Regards,
    Ian Xue

    ============================================

    If the Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

0 additional answers

Sort by: Most helpful