I added Excel support to my XenDesktop 7.x script in order to add a user
requested feature. Here are some of my routines (some based on previous MBS
work).
#need to make sure Excel is installed and not already running in the current
user's session
Function CheckExcelPrereq
{
If((Test-Path REGISTRY::HKEY_CLASSES_ROOT\Excel.Application) -eq
$False)
{
$ErrorActionPreference = $SaveEAPreference
Write-Host "`n`n`t`tFor the Delivery Groups Utilization option,
this script directly outputs to Microsoft Excel, `n`t`tplease install Microsoft
Excel or do not use the DeliveryGroupsUtilization (DGU) switch`n`n"
Exit
}
#find out our session (usually "1" except on TS/RDC or Citrix)
$SessionID = (Get-Process -PID $PID).SessionId
#Find out if excel is running in our session
[bool]$excelrunning = ((Get-Process 'Excel' -ea 0)|?{$_.SessionId -eq
$SessionID}) -ne $Null
If($excelrunning)
{
$ErrorActionPreference = $SaveEAPreference
Write-Host "`n`n`tPlease close all instances of Microsoft Excel
before running this report.`n`n"
Exit
}
}
Somewhere towards the beginning of the script, just run CheckExcelPrereq
The main routine that uses Excel.
$TempFile = "$($pwd)\emtempgraph_$(Get-Date -UFormat %Y%m%d_%H%M%S).csv"
Write-Verbose "$(Get-Date): `t`t`tGetting utilization data for $($Group.Name)"
-Verbose
$Results = Get-BrokerDesktopUsage @XDParams2 -DesktopGroupName $Group.Name
-SortBy Timestamp | Select-Object Timestamp, InUse
If($? -and $Results -ne $Null)
{
$Results | Export-Csv $TempFile -NoTypeInformation *>$Null
#Create excel COM object
$excel = New-Object -ComObject excel.application 4>$Null
#Make not visible
$excel.Visible = $False
$excel.DisplayAlerts = $False
#Various Enumerations
$xlDirection = [Microsoft.Office.Interop.Excel.XLDirection]
$excelChart = [Microsoft.Office.Interop.Excel.XLChartType]
$excelAxes = [Microsoft.Office.Interop.Excel.XlAxisType]
$excelCategoryScale = [Microsoft.Office.Interop.Excel.XlCategoryType]
$excelTickMark = [Microsoft.Office.Interop.Excel.XlTickMark]
Write-Verbose "$(Get-Date): `t`t`tOpening Excel with temp file
$($TempFile)"
#Add CSV File into Excel Workbook
$null = $excel.Workbooks.Open($TempFile)
$worksheet = $excel.ActiveSheet
$Null = $worksheet.UsedRange.EntireColumn.AutoFit()
#Assumes that date is always on A column
$range = $worksheet.Range("A2")
$selectionXL = $worksheet.Range($range,$range.end($xlDirection::xlDown))
$Start = @($selectionXL)[0].Text
$End = @($selectionXL)[-1].Text
Write-Verbose "$(Get-Date): `t`t`tCreating chart for $($Group.Name)"
$chart = $worksheet.Shapes.AddChart().Chart
$chart.chartType = $excelChart::xlXYScatterLines
$chart.HasLegend = $false
$chart.HasTitle = $true
$chart.ChartTitle.Text = "$($Group.Name) utilization"
#Work with the X axis for the Date Stamp
$xaxis = $chart.Axes($excelAxes::XlCategory)
$xaxis.HasTitle = $False
$xaxis.CategoryType = $excelCategoryScale::xlCategoryScale
$xaxis.MajorTickMark = $excelTickMark::xlTickMarkCross
$xaxis.HasMajorGridLines = $true
$xaxis.TickLabels.NumberFormat = "m/d/yyyy"
$xaxis.TickLabels.Orientation = 48 #degrees to rotate text
#Work with the Y axis for the number of desktops in use
$yaxis = $chart.Axes($excelAxes::XlValue)
$yaxis.HasTitle = $true
$yaxis.AxisTitle.Text = "Desktops in use"
$yaxis.AxisTitle.Font.Size = 12
$worksheet.ChartObjects().Item(1).copy()
$word.Selection.PasteAndFormat(13) #Pastes an Excel chart as a picture
Write-Verbose "$(Get-Date): `t`t`tClosing excel for $($Group.Name)"
$excel.Workbooks.Close($false)
$excel.Quit()
FindWordDocumentEnd
WriteWordLine 0 0 ""
While(
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($selectionXL)){}
While(
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Range)){}
While(
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Chart)){}
While(
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet)){}
While(
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){}
Write-Verbose "$(Get-Date): `t`t`tDeleting temp files $($TempFile)"
Remove-Item $TempFile *>$Null
}
ElseIf($? -and $Results -eq $Null)
{
$txt = "There is no Utilization data for $($Group.Name)"
OutputWarning $txt
}
Else
{
$txt = "Unable to retrieve Utilization data for $($Group.name)"
OutputWarning $txt
}
I have another script that uses nothing but Excel and also does email stuff
that works with Office 365. You can look at it to get more code samples.
https://dl.dropboxusercontent.com/u/43555945/Get-PvDStatsExcel.ps1
Thanks
Webster
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Sean Martin
> Sent: Monday, April 06, 2015 11:18 PM
> To: [email protected]
> Subject: Re: [powershell] Working with Web Query Files
>
> I suppose that is a possibility. I have this running as a scheduled task on a
> system that wouldn't introduce any conflicting Excel processes. It's probably
> possible to get the process ID for the Excel process launched by a specific
> user account. That then could be passed to the kill command.
>
> I did confirm that simply quitting Excel does not actually stop the process.
>
> - Sean
>
> > On Apr 6, 2015, at 7:53 PM, Joshua Delaughter
> <[email protected]> wrote:
> >
> > Could the last line possibly kill other Excel instances that might be
> > running?
> >
> > Does the Quit method in the second to last line not kill the process?
> >
> > Is there a way to get the PID for the particular instance of Excel that this
> snippet starts instead?
> >
> > Sent from my iPhone
> >
> >> On Apr 6, 2015, at 5:40 PM, Sean Martin <[email protected]>
> wrote:
> >>
> >> Good afternoon,
> >>
> >> I've been working on a Powershell script to create/update Mail Contacts
> in Exchange based on information stored in our SharePoint site. This may be
> trivial to some of you, but I thought I would share for those who are
> relatively new to Powershell such as myself.
> >>
> >> Saving the list from Sharepoint to a Web Query File (.IQY) allows for the
> presentation of current data each time the file is opened. I had a hard time
> figuring out how to work with the file directly, and didn't have the option of
> running the script on our Sharepoint server or remotely against it, so I used
> the following to save the content as a CSV.
> >>
> >> # Convert Web Query File to CSV
> >> $xl = New-Object -C Excel.Application -vb:$false $xl.DisplayAlerts =
> >> $False $iqy = $xl.Workbooks.Open('<path to IQY file'>)
> >> $iqy.SaveAs('<Path for saved CSV file>', 6)
> >> $iqy.Close($false)
> >> $xl.Quit()
> >> Get-Process Excel | kill
> >>
> >> I can't take credit for the above, but it did take some time track it down
> >> so
> I thought sharing it might help someone else avoid the research time.
> >>
> >> - Sean
> >>
> >> ================================================
> >> Did you know you can also post and find answers on PowerShell in the
> forums?
> >> http://www.myitforum.com/forums/default.asp?catApp=1
> >
> >
> > ================================================
> > Did you know you can also post and find answers on PowerShell in the
> forums?
> > http://www.myitforum.com/forums/default.asp?catApp=1
> >
>
>
> ================================================
> Did you know you can also post and find answers on PowerShell in the
> forums?
> http://www.myitforum.com/forums/default.asp?catApp=1
================================================
Did you know you can also post and find answers on PowerShell in the forums?
http://www.myitforum.com/forums/default.asp?catApp=1
