Question:
PROBLEM ONLY APPEARS WHEN NO MAIL ACCOUNT IS CONFIGURED – STILL I WOULD APPRECIATE A SOLUTION
I need some help. I have found a very weird habit of this little Script. And I have absolutely no clue, why this should happen.
If I run through the code posted below, Microsoft Outlook starts. And as long as I don’t terminate the Outlook process the script is stuck! Why would this code, ever, start Outlook?
I am lost!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$Path = "C:\test.xls" #Excelvar: $Row = [int] 2 $Excel = New-Object -ComObject Excel.Application $Excel.Visible = $true $Excel.DisplayAlerts = $false #Sheets: $ADUsers = "Active Directory Users" $Groups = "Create Groups" $UsertoGroup = "User to groups" $DNS = "DNS" #$Worksheet = $Workbook.Sheets.Add() $checkxls = test-path -pathtype Any $Path if ($checkxls -eq $false) { $wb = $Excel.Workbooks.Add() $wb.Worksheets.add() $wb.SaveAs($Path) $wb.Close() $Excel.Quit() |
Thx in advance!
Powershell output after Outlook is terminated:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
Application : Microsoft.Office.Interop.Excel.ApplicationClass Creator : 1480803660 Parent : System.__ComObject CodeName : _CodeName : Index : 1 Name : Tabelle4 Next : System.__ComObject OnDoubleClick : OnSheetActivate : OnSheetDeactivate : PageSetup : System.__ComObject Previous : ProtectContents : False ProtectDrawingObjects : False ProtectionMode : False ProtectScenarios : False Visible : -1 Shapes : System.__ComObject TransitionExpEval : False AutoFilterMode : False EnableCalculation : True Cells : System.__ComObject CircularReference : Columns : System.__ComObject ConsolidationFunction : -4157 ConsolidationOptions : {False, False, False} ConsolidationSources : DisplayAutomaticPageBreaks : False EnableAutoFilter : False EnableSelection : 0 EnableOutlining : False EnablePivotTable : False FilterMode : False Names : System.__ComObject OnCalculate : OnData : OnEntry : Outline : System.__ComObject Rows : System.__ComObject ScrollArea : StandardHeight : 15 StandardWidth : 10,71 TransitionFormEntry : False Type : -4167 UsedRange : System.__ComObject HPageBreaks : System.__ComObject VPageBreaks : System.__ComObject QueryTables : System.__ComObject DisplayPageBreaks : False Comments : System.__ComObject Hyperlinks : System.__ComObject _DisplayRightToLeft : False AutoFilter : DisplayRightToLeft : False Scripts : System.__ComObject Tab : System.__ComObject MailEnvelope : CustomProperties : System.__ComObject SmartTags : System.__ComObject Protection : System.__ComObject ListObjects : System.__ComObject EnableFormatConditionsCalculation : True Sort : System.__ComObject PrintedCommentPages : 0 |
Answer:
The issue here is that when you run $wb.Worksheets.add() it returns the new worksheet to the pipeline (this is why the Sheet properties are displayed when you kill Outlook).
I believe the MailEnvelope property of the Worksheet is what causes Outlook to open (if you store the Sheet and return the MailEnvelope property, the same behaviour occurs).
To get around this you can store the returned sheet, use the Out-Null cmdlet or cast the worksheet to void: $ws = $wb.Worksheets.add() or
$wb.Worksheets.add() | Out-Null or [void] $wb.Worksheets.add()