Using Excel to Create Published Applications on XenApp 7.11

I have been working with a client on a XenApp 6 to 7.11 (Seven Eleven) migration project recently.  The client has a large list of shortcuts which were published in their existing Farm and staff were used to accessing content via those shortcuts.  This presented a challenge and was potentially going to slow down the migration time until all shortcuts had been published on the new 7.11 site.

My approach to this challenge was to use a combination of powershell and Excel to automatically create the new published content and apps.  I did this by extracting a list of published applications on the client's existing XenApp 6.5 Farm and saving the data into and Excel Spreadsheet

The article will describe the the method and Powershell script that I used to automatically publish all of the application shortcuts within the Excel Spreadsheet.  This should be used as a guide and I'm sure many of you will be able to tweak and improve the Powershell code or perhaps remove the requirement for Excel.

Method:

Installed Excel on one of my Delivery Controllers

Copied the Exported Application Data and removed unwanted column information so that I was left with (Publication Name, Command Executable, Command path, Working Directory)

From the Delivery Controller, launched Powershell and ran Get-BrokerDeliveryGroup. Noted the UID of the delivery group

Copied the Shortcut.excel spreadsheet into a directory called "Shortcuts"

Modified the powershell script to imclude the path to the excel file and the UID of the delivery group.


POWERSHELL SCRIPT

$rowcount=0
$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\shortcuts\shortcuts.xlsx")
$ws = $wb.Sheets.Item(1)
$rowcount = $ws.UsedRange.Rows.Count
$wb.Close()
$xl.Quit()

$FilePath = "C:\shortcuts\shortcuts.xlsx"
$SheetName = "Sheet1"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$WorkBook = $objExcel.Workbooks.Open($FilePath)
$WorkSheet = $WorkBook.sheets.item($SheetName)
$Row=1
$NAMEVAR=1
$EXEVAR=2
$PATHVAR=3
$WRKDIRVAR=4
while($row -le $rowcount){
$CommandLineEXE=$worksheet.Rows.Item($ROW).Columns.Item($EXEVAR).Text
$CommandLinePATH=$worksheet.Rows.Item($ROW).Columns.Item($PATHVAR).Text
$WORKDIR=$worksheet.Rows.Item($ROW).Columns.Item($WRKDIRVAR).Text
$Name=$worksheet.Rows.Item($Row).Columns.Item($NAMEVAR).Text
$PubNameVAR=$worksheet.Rows.Item($Row).Columns.Item($NAMEVAR).Text
$pspubnameVAR="-PublishedName "+$PubName
$psnameVAR=$NameVAR
$appVAR=New-BrokerApplication -ApplicationType “HostedOnDesktop” -CommandLineArguments $CommandLinePATH -CommandLineExecutable $CommandLineEXE -CpuPriorityLevel "Normal" -DesktopGroup 5 (This it the delivery group UID) -Enabled $True -MaxPerUserInstances 0 -MaxTotalInstances 0 $psname -Priority 0 -SecureCmdLineArgumentsEnabled $True -ShortcutAddedToDesktop $False -ShortcutAddedToStartMenu $False -workingdirectory $workdir -UserFilterEnabled $False -Visible $True -WaitForPrinterCreation $False -Description "Keywords:auto"
Add-BrokerApplication $appVAR -DesktopGroup "NAME OF YOUR DESKTOP DELIVERY GROUP"
$Row+=1
}

$workbook.close
$objexcel.close

EXCEL SPREASHEET COLUMNS

Column A - Exported Publication's Name

Column B - Exported Publication's Command Line

Column C - Exported Publication's Path

Column D - Exported Publication's Working Directory

Save the spreadsheet to the path specified in the script.

Run the script