Tuesday, November 20

Published Applications Report in Excel | VB Script

Keeping here for reference:

'Published Applications Report in Excel

strMbox = MsgBox("Do you want to include all the Applications Details?",3,"Include Details or just Basic?")

If strMbox = 6 Then

 appsList = "Details"
 
Elseif strMbox = 7 Then

 appsList = "Basic"
 
Else

 wscript.quit
 
End If


Set objExplorer = WScript.CreateObject("InternetExplorer.Application")
objExplorer.Navigate "about:blank"
objExplorer.Document.title = "Citrix Published Applications Report"
objExplorer.ToolBar = 0
objExplorer.StatusBar = 0
objExplorer.Width = 600
objExplorer.Height = 200 
objExplorer.Left = 0
objExplorer.Top = 0
objExplorer.Visible = 1  

Do While (objExplorer.Busy)
 Wscript.Sleep 200
Loop   

objExplorer.Document.Body.InnerHTML = "<b>Citrix Published Application Report is starting...</b><br>" 
wscript.sleep 1000

Set objExcel = CreateObject("Excel.Application")

Const xlSaveChanges = 1

objExcel.Visible = False

CurrentRow = 1 'the first row in excel is 1 (not 0)
Const xlAscending = 1
Const xlYes = 1 'will exclude the first active row from sorting

objExplorer.Document.Body.InnerHTML = "Retrieving the <b>Farm Name...</b> <br>"
wscript.sleep 1000

Set theFarm = CreateObject("MetaFrameCOM.MetaFrameFarm")

objExplorer.Document.Body.InnerHTML = "Gathering <b>" & theFarm.FarmName & "'s Apps...</b> <br>"
wscript.sleep 1000

'Formatting the current Date
strDate = pd(MONTH(date()),2) & "-" & _
   pd(DAY(date()),2) & "-" & _ 
          YEAR(Date()) 

If appsList = "Details" then

 strVBSLog = theFarm.FarmName & " Published Applications Detailed Report " & strDate & ".xls"
 
Else

 strVBSLog = theFarm.FarmName & " Published Applications Basic Report " & strDate & ".xls"

End if


objExplorer.Document.Body.InnerHTML = "<b>Setting up Excel... </b> <br>"
wscript.sleep 1000

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objExplorer.Document.Body.InnerHTML = "Adding <b>Header Row...</b>  <br>"
wscript.sleep 1000

If appsList = "Basic" then

 objExcel.Cells(CurrentRow, 1).Value = "Distinguished Name"
 objExcel.Cells(CurrentRow, 2).Value = "Application (Display) Name" 
 objExcel.Cells(CurrentRow, 3).Value = "Citrix Farm"

Else

 objExcel.Cells(CurrentRow, 1).Value = "Distinguished Name"
 objExcel.Cells(CurrentRow, 2).Value = "Application (Display) Name" 
 objExcel.Cells(CurrentRow, 3).Value = "Citrix Farm"
 objExcel.Cells(CurrentRow, 4).Value = "Command Line"
 objExcel.Cells(CurrentRow, 5).Value = "Working Directory"
 objExcel.Cells(CurrentRow, 6).Value = "Servers"
 objExcel.Cells(CurrentRow, 7).Value = "Users"
 objExcel.Cells(CurrentRow, 8).Value = "Groups"

End if
    
    Set mfFarm = CreateObject("MetaFrameCOM.MetaFrameFarm")
    mfFarm.Initialize 1
    
    objExplorer.Document.Body.InnerHTML = "Adding <b>Published Application Info...</b> <br>"
    wscript.sleep 1000

    For Each mfApp In mfFarm.Applications
        mfApp.LoadData 1
        
        
        objExplorer.Document.Body.InnerHTML = "Adding: <b>" & mfApp.AppName & " </b> <br>"
        
        CurrentRow = CurrentRow + 1
        
        If appsList = "Basic" then
        
  objExcel.Cells(CurrentRow, 1).Value = mfApp.DistinguishedName 
  objExcel.Cells(CurrentRow, 2).Value = mfApp.AppName
  objExcel.Cells(CurrentRow, 3).Value = mfApp.FarmName
 
 Else 
  appusers = ""
  appgroups = ""
  appservers= ""
     
  If mfApp.AppType = 17 Then 
   
   Set appContentObj=mfApp.ContentObject
   getusers()
   getgroups()  
   
   objExcel.Cells(CurrentRow, 1).Value = mfApp.DistinguishedName 
   objExcel.Cells(CurrentRow, 2).Value = mfApp.AppName
   objExcel.Cells(CurrentRow, 3).Value = mfApp.FarmName
   objExcel.Cells(CurrentRow, 4).Value = appContentObj.Contentaddress
   objExcel.Cells(CurrentRow, 5).Value = ""
   objExcel.Cells(CurrentRow, 6).Value = "This is Content, usually an internal shortcut, and just gets passed to the client. Does not run on a Citrix server"
   objExcel.Cells(CurrentRow, 7).Value = appusers
   objExcel.Cells(CurrentRow, 8).Value = appgroups
    
   
  Else
   
   Set aWinApp = mfApp.WinAppObject
   getservers()
   getusers()
   getgroups()

   objExcel.Cells(CurrentRow, 1).Value = mfApp.DistinguishedName 
   objExcel.Cells(CurrentRow, 2).Value = mfApp.AppName
   objExcel.Cells(CurrentRow, 3).Value = mfApp.FarmName

   If aWinApp.PNAttributes = 8 Then
       objExcel.Cells(CurrentRow, 4).Value = "Published Desktop"
       objExcel.Cells(CurrentRow, 5).Value = ""
      Else
       objExcel.Cells(CurrentRow, 4).Value = aWinApp.DefaultInitProg
       objExcel.Cells(CurrentRow, 5).Value = aWinApp.DefaultWorkDir
      End if
   objExcel.Cells(CurrentRow, 6).Value = appservers
   objExcel.Cells(CurrentRow, 7).Value = appusers
   objExcel.Cells(CurrentRow, 8).Value = appgroups
            
  End if 
 
 End if
 
    Next
    
objExplorer.Document.Body.InnerHTML = "<b>Sorting Aplhabetically...</b>  <br>"
wscript.sleep 200
    
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes

objExplorer.Document.Body.InnerHTML = "<b>Autofitting Columns...</b>  <br>"
wscript.sleep 200

objRange.EntireColumn.Autofit()

objExplorer.Document.Body.InnerHTML = "<b>Formatting Header Row...</b>  <br>"
wscript.sleep 200

If appsList = "Basic" then

 objExcel.Cells(1, 1).Font.Bold = TRUE
 objExcel.Cells(1, 1).Interior.ColorIndex = 30
 objExcel.Cells(1, 1).Font.ColorIndex = 2

 objExcel.Cells(1, 2).Font.Bold = TRUE
 objExcel.Cells(1, 2).Interior.ColorIndex = 30
 objExcel.Cells(1, 2).Font.ColorIndex = 2

 objExcel.Cells(1, 3).Font.Bold = TRUE
 objExcel.Cells(1, 3).Interior.ColorIndex = 30
 objExcel.Cells(1, 3).Font.ColorIndex = 2

Else

 objExcel.Cells(1, 1).Font.Bold = TRUE
 objExcel.Cells(1, 1).Interior.ColorIndex = 30
 objExcel.Cells(1, 1).Font.ColorIndex = 2
 
 objExcel.Cells(1, 2).Font.Bold = TRUE
 objExcel.Cells(1, 2).Interior.ColorIndex = 30
 objExcel.Cells(1, 2).Font.ColorIndex = 2

 objExcel.Cells(1, 3).Font.Bold = TRUE
 objExcel.Cells(1, 3).Interior.ColorIndex = 30
 objExcel.Cells(1, 3).Font.ColorIndex = 2

 objExcel.Cells(1, 4).Font.Bold = TRUE
 objExcel.Cells(1, 4).Interior.ColorIndex = 30
 objExcel.Cells(1, 4).Font.ColorIndex = 2

 objExcel.Cells(1, 5).Font.Bold = TRUE
 objExcel.Cells(1, 5).Interior.ColorIndex = 30
 objExcel.Cells(1, 5).Font.ColorIndex = 2

 objExcel.Cells(1, 6).Font.Bold = TRUE
 objExcel.Cells(1, 6).Interior.ColorIndex = 30
 objExcel.Cells(1, 6).Font.ColorIndex = 2

 objExcel.Cells(1, 7).Font.Bold = TRUE
 objExcel.Cells(1, 7).Interior.ColorIndex = 30
 objExcel.Cells(1, 7).Font.ColorIndex = 2

 objExcel.Cells(1, 8).Font.Bold = TRUE
 objExcel.Cells(1, 8).Interior.ColorIndex = 30
 objExcel.Cells(1, 8).Font.ColorIndex = 2

End if
objRange2.Range("A2").Select
objExcel.ActiveWindow.FreezePanes = "True"
objRange2.Range("A1").Select

objExplorer.Document.Body.InnerHTML = "Saving Spreadsheet as <b>" & strVBSLog & " </b> <br>"
wscript.sleep 1000
objExplorer.Quit

objExcel.ActiveWorkbook.SaveAs (strVBSLog)
objExcel.Quit

wscript.echo "File has been saved to My Documents as " & strVBSLog

objExcel.Application.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(strVBSLog)

wscript.quit

Function pd(n, totalDigits) 
 If totalDigits > len(n) then 
  pd = String(totalDigits-len(n),"0") & n 
        else 
         pd = n 
        End if 
End Function 

sub getusers()
For Each anUser In mfApp.Users

 appusers= appusers & " " & anUser.AAName & "\" & anUser.UserName
      
Next
End sub

sub getgroups()
For Each anGroup In mfApp.Groups
 
 appgroups= appgroups & " " & anGroup.AAName & "\" & anGroup.GroupName
 
Next 
End sub

sub getservers()
For Each anServer In mfApp.Servers
   
 appservers= appservers & " " & anServer.ServerName

Next  
End sub

No comments:

Post a Comment