Difference between revisions of "PS Run Excel AD"

From DataSelf Knowledge Base
Jump to navigation Jump to search
(Created page with " <nowiki> cls #============================================================= # Modify this file reference # $PSScriptRoot is the root path of this script. # "C:\DataSel...")
(No difference)

Revision as of 01:52, 7 March 2019



cls #============================================================= # Modify this file reference # $PSScriptRoot is the root path of this script. # "C:\DataSelf\Excel_AD\Daily_DS_ExcelADController.xlsm" $WBRef = "$PSScriptRoot\Daily_DS_ExcelADController.xlsm" #========================================================== # Prepare to open transcript / log file $RunLogsPath = "$PSScriptRoot\RunLogs" # Name of path for log files Write-Host "Transcript file: $RunLogsPath" if (!(Test-Path -Path $RunLogsPath )) { New-Item -ItemType "directory" -Path $RunLogsPath -Force } $DateTimeStr = Get-Date -UFormat %Y-%m-%d@%H-%M $Transcript_FileRef = "$RunLogsPath\RunLog_$DateTimeStr.txt" Start-Transcript -Path $Transcript_FileRef -Append Write-Host "" Write-Host "This script: $PSCommandPath" Write-Host "Root path $PSScriptRoot" Write-Host "Excel AD workbook to run: $WBRef" Write-Host "Copies of Excel open when this script started/ Current Excel processes listed below" get-process -name "*EXCEL*" -IncludeUserName | Select Id, Username, StartTime Write-Host "" TRY { Write-Host "Open COM for Excel.Application" $ExcelObj = New-Object -ComObject "excel.application" $ExcelObj.Visible = $false $ExcelObj.DisplayAlerts = $false $ExcelObj.ScreenUpdating = $false $ExcelObj.UserControl = $false $ExcelObj.Interactive = $false } CATCH { write-host "`n----------------------------------------" write-host "FATAL ERROR trying to find COM object for Excel" Write-Host ("Catch Time Stamp: $(Get-Date -UFormat "%Y-%m-%d %T")") write-host $_.Exception.Message write-host $_.InvocationInfo.PositionMessage Write-Host "Problems may be due to other copies of Excel interferring." Write-Host "Kill other Excel processess with Task Manager" get-process -name "*EXCEL*" -IncludeUserName | Select Username, Id, StartTime Stop-Transcript Exit } Write-host "Excel Object Created" TRY { Write-Host "Open Excel Workbook = $WBRef" $Excel_Workbook = $ExcelObj.Workbooks.Add($WBRef) } CATCH { write-host "`n----------------------------------------" write-host "FATAL ERROR trying to open Excel AD Workbook $WBRef" Write-Host ("Catch Time Stamp: $(Get-Date -UFormat "%Y-%m-%d %T")") write-host $_.Exception.Message write-host $_.InvocationInfo.PositionMessage Write-Host "Problems may be due to other copies of Excel interferring." Write-Host "Kill other Excel processess with Task Manager" get-process -name "*EXCEL*" -IncludeUserName | Select Username, Id, StartTime $ExcelObj.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj) Remove-Variable ExcelObj Stop-Transcript Exit } Write-Host "Excel workbook opened - OK" TRY { Write-Host "Run Macro in Excel AD Excel_Workbook" $excelObj.Run("Main", $WBRef, $True) } CATCH { write-host "`n----------------------------------------" write-host "FATAL ERROR trying to run Excel AD macro in Excel AD Workbook $WBRef" Write-Host ("Catch Time Stamp: $(Get-Date -UFormat "%Y-%m-%d %T")") write-host $_.Exception.Message write-host $_.InvocationInfo.PositionMessage $ExcelObj.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj) Remove-Variable ExcelObj Stop-Transcript Exit } Write-Host "Macro called - OK" $Excel_Workbook.close() "Release Excel_Workbook" [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel_Workbook) Remove-Variable Excel_Workbook -ErrorAction Ignore # Clean up and garbage collection Start-Sleep 5 "Quit COM" $ExcelObj.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj) Remove-Variable ExcelObj # This may be overkill [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() Stop-Transcript write-host "End Transcript" Start-Sleep -Seconds 1