PS Run Excel AD

From DataSelf Knowledge Base
Jump to navigation Jump to search

Powershell Script used to run Excel AD. It eliminates the need for a separate .vbs script.

Keywords: COM, Excel, .vbs, VBScript, Powershell


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 "Start: $(Get-Date -Format G)"


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</nowiki></nowiki>