how to close Excel if error occurs while opening due to Macros

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Tim Pringle Tim Pringle 2 years ago.

  • Author
    Posts
  • #20489
    Profile photo of Praba kar.m
    Praba kar.m
    Participant

    Hi I had a powershell script that runs to check whether the list of Excels in a folder has VBA code or not,it is working fine until unless any error occurs while opening the workbook.

    Ie., the Power shell script stops prompting the user to end the excel macro error and to close it,only when the powershell continues running., and what i need over here is i want the code to close the excel automatically if any error arises and the script has to keep running till the end.

    Below is the code i am using and needed the issues to be resolved.

    Get-ChildItem "C:\Users\mpraba\Desktop\Macrotest" | where {$_.extension -match "^\.xls(m|)$"} | ForEach-Object -Begin {
    $thisThread = [System.Threading.Thread]::CurrentThread
    $originalCulture = $thisThread.CurrentCulture
    $thisThread.CurrentCulture = New-Object System.Globalization.CultureInfo('da-DK')
    $excel = new-object -comobject excel.application
    }-Process {
    $excel.DisplayAlerts=$False
    $workbook = $excel.workbooks.Open($_.FullName)
    $_.FullName + " : " + $workbook.HasVBProject | add-content C:\Users\mpraba\Desktop\Macrotest\Test.txt
    $workbook.Close()
    } -end {
    $excel.Quit()
    $thisThread.CurrentCulture = $originalCulture
    }

  • #20655
    Profile photo of Don Jones
    Don Jones
    Keymaster

    No, I don't believe there's a programmatic way to bypass that prompt. That said, we don't have a ton of Excel programming experts here; you might consider posting someplace like StackOverflow.com and see if you get a more comprehensive answer. Sorry couldn't be of more help!

  • #20753
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    It's messy, but but maybe you could look at having your script launch part of this this as a job. That then means it's going to run asynchronously. It's off the top of my head here, but you could try this type of approach

    1) Query excel for the list of files in the folder (you should be able to do this synchronously)
    2) For Each file in the folder, launch a separate job which carries out creating an instance of excel, and runs the check
    3) Once you've launched all the jobs, you could then check the status of a job every say 5 seconds
    4) If the status of the job is complete, extract the data from the job output
    5) If the status of the job is running, wait for a given period of time.
    6) If the time is exceeded, then you want to kill the excel process(s) that have caused errors. Because your other jobs will have completed by then, it should mean you are only killing a process that has hung due to an error. Kill the job as well, and record the details of the one that failed.

    Of course, i'd suggest introducing a filter so you only have a few instances of excel running at a time in case there are forty files in the directory you're checking!

You must be logged in to reply to this topic.