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

PowerShell Help
1

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
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()

Thanks
Dinesh

2

When you crosspost the same question at the same time to different forums you should at least post links the other forums along with your question to avoid peaple willing to help you making the work twice or more.

https://social.technet.microsoft.com/Forums/en-US/e0337417-e4d7-452b-8ee6-df1997c137f2/how-to-suppress-the-warning-popup-window-while-opening-the-macro-excel-file?forum=winserverpowershell

3

I was able to get this done in Word by hacking the registry in the script so I would think it can be done in Excel as well. In my case, I was dynamically updating the embedded macro via script. The tricky part is that the registry location is different per Office version so you would have to figure that out as well in your script. The script would of course need to run as admin to update the registry. Google it and I think you will find your answer. I dont recommend this approach, but it can be done.