powershell -- how do I programmatically close an excel dialog box?

Christian Bahnsen -- .mil account 201 Reputation points
2020-11-24T12:33:30.263+00:00

I'm trying to figure out how to programmatically close an excel dialog box that pops up when I'm closing a workbook.

# create an instance of excel  
$a = New-Object -comobject Excel.Application  
  
  
# make it visible  
$a.Visible = $True  
  
  
# open  workbook   
$b = $a.Workbooks.Open("filename.xlsx")  
  
# There are a number of steps following, then I manually copy some data from the workbook.  
  
# close the workbook, saving changes  
$b.Close($true)  
  
# this is the point where I see the dialog box I want to close programmatically  
  

42206-excelclipboarddialog11232020.jpg

I don't want to save the info on the clipboard, so I want to answer No

Thanks for any assistance

Christian Bahnsen

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.
5,405 questions
{count} votes

Accepted answer
  1. Rich Matheisen 45,111 Reputation points
    2020-11-24T19:17:43.327+00:00

    You can avoid the need to deal with dialog boxes. Try one (or both) of these:

    $a.DisplayAlerts = $False
    
    $null = [Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)
    [System.Windows.Forms.Clipboard]::Clear()
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 31,091 Reputation points Microsoft Vendor
    2020-11-25T09:10:48.257+00:00

    Hi,

    Please see if this helps
    https://social.technet.microsoft.com/Forums/windowsserver/en-US/5dc5513e-09dd-4ee4-b5d6-2f1e86d12821/powershell-and-excel-cutcopymode?forum=winserverpowershell

    Best Regards,
    Ian

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

    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.

    0 comments No comments

  2. Christian Bahnsen -- .mil account 201 Reputation points
    2020-11-25T12:41:07.467+00:00

    @Rich Matheisen

    Thanks, $a.DisplayAlerts = $False worked like a charm

    Thanks also to @Ian Xue (Shanghai Wicresoft Co., Ltd.) for your reply

    0 comments No comments