locked
How to suppress the warning popup window while opening the Macro excel file. RRS feed

  • Question

  • Hi,

    I have an excel macro file which contain multiple sheets in it. 
    Using the below code, I am trying to find out the sheets which contains specific string in it. The code is working fine as expected.

    The problem is, whenever trying to open the macro excel file its showing a popup window(Microsoft Visual Basic) and 
    we suppose to click 'end' button to supress that window. But it will be a difficult one when processing thousands of Macro files.

    I tried set the excel.DisplayAlerts = $false. But it didn't even workout. 
    Please suggest a way the supress that window by the code itself. (Attached is the snapshot of the window, for reference)

    $before				 	= @(Get-Process [e]xcel | %{$_.Id})
    $excel					= New-Object -comobject Excel.Application
    $ExcelId			 	= Get-Process excel | %{$_.Id} | ?{$before -notcontains $_}
    $excel.Visible		 	= $false
    $excel.DisplayAlerts	= $false
    $excel.AskToUpdateLinks = $false
    		
    $workbook = $excel.Workbooks.Open('D:\dinesh\worksheetArea\workbook1.xlsm')
    For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
    {
    	$worksheet = $workbook.sheets.item($i)  
    	$search  = $worksheet.usedRange.Find('ServerId::50012')
    		  If($search){
    				$final_list += $worksheet.Name						
    			}
    }
    if (!$Workbook.saved){ 
    	$workbook.save() 
     }
     $workbook.close()
     $excel.quit()
     $excel = $null

    Thanks
    Dinesh

    Tuesday, February 18, 2020 2:37 PM

All replies

  • You cannot suppress many security alerts.

    As I posted in your other thread, post Excel questions in the Excel developers forum.  They will be better equipped to help you with programmatic access to Excel.


    \_(ツ)_/

    Tuesday, February 18, 2020 2:43 PM