Hey, Scripting Guy! Event 2 *Solutions* from Expert Commentators (Beginner and Advanced; the long jump)

Hey, Scripting Guy! Event 2 *Solutions* from Expert Commentators (Beginner and Advanced; the long jump)

  • Comments 1
  • Likes

2009 Summer Scripting Games  

(Note: These solutions were written for Event 2.)   

Beginner Event 2: The Long Jump

In the long jump event, you will be asked to determine the speed, the strength, and the agility of your computer.  

Guest commentator: Scott Hanselman

Guest commentator Scott Hanselman

Scott Hanselman is a principal program manager at Microsoft. He maintains the computer zen Web site and can be found on Twitter.

Windows PowerShell solution

At first blush, this event is relatively simple. This is because all of the information can be obtained by using the Get-WmiObject cmdlet and then storing the results into a variable. The more difficult part of the event is cleaning up the output. Here is the 5-minute, one-line solution. The code is not very readable, however:

gwmi win32_processor |fl name,MaxClockSpeed,L2CacheSize, L2CacheSpeed, L3CacheSize, L3CacheSpeed, NumberOfCores,NumberOfLogicalProcessors,AddressWidth

Here is a more involved solution that follows the spirit of the event:

BeginnerEvent2.ps1

set-alias ?: Invoke-Ternary -Option AllScope -Description "PSCX filter alias"
filter Invoke-Ternary ([scriptblock]$decider, [scriptblock]$ifTrue, [scriptblock]$ifFalse)
{
   if (&$decider) {
      &$ifTrue
   } else {
      &$ifFalse
   }
}

$hold = gwmi win32_processor | select Name,MaxClockSpeed,L2CacheSize, L2CacheSpeed, L3CacheSize, L3CacheSpeed, NumberOfCores,NumberOfLogicalProcessors,AddressWidth

write-host -fore "green" Strength Evaluation for (gi ENV:\COMPUTERNAME).Value
write-host -fore "yellow" Speed ... $hold.MaxClockSpeed
write-host -fore "yellow" L2 Cache Size: (?: {$hold.L2CacheSize} {$hold.L2CacheSize} {"N/A"})
write-host -fore "yellow" L2 Cache Speed: (?: {$hold.L2CacheSpeed} {$hold.L2CacheSpeed} {"N/A"})
write-host -fore "yellow" L3 Cache Size: (?: {$hold.L3CacheSize} {$hold.L3CacheSize} {"N/A"})
write-host -fore "yellow" L3 Cache Speed: (?: {$hold.L3CacheSpeed} {$hold.L2CacheSpeed} {"N/A"})
write-host -fore "magenta" Strength ...
write-host -fore "magenta" Number of Cores: $hold.NumberOfCores
write-host -fore "magenta" Number of Logical Processors: $hold.NumberOfLogicalProcessors
write-host -fore "magenta" Name: $hold.Name
write-host -fore "cyan" Agility ...
write-host -fore "cyan" Address Width: $hold.AddressWidth

I used the ?: alias from the PowerShell Community Extensions Project (PSCX). You can download the PSCX snap-in from CodePlex. The 1.1.1 version of PSCX for Windows PowerShell 1.0 works great. There is a 1.2 version in beta for Windows PowerShell 2.0. The reason for using the PSCX alias ?: is because there is no Ternary Operator in Windows PowerShell. After I installed the PSCX and had access to the ?: alias, I could do something cool like this:


(?: {$hold.L2CacheSize} {$hold.L2CacheSize} {"N/A"})

When the script is run, this output is displayed:

Image of the output display by the script

 

Guest commentator: Marcus Lerch

Guest commentator Marcus Lerch 

Marcus Lerch is a senior premier field engineer (PFE) for Microsoft Germany. He specializes in Platforms & Active Directory.

The beginner long jump task is to gather some information about the processor in our computer. The best place to retrieve this information is from the processor itself. The easiest way to obtain this information is to use Windows Management Instrumentation (WMI). My complete Windows PowerShell solution for event two is seen here.

BeginnerEvent2_Marcus.ps1

param([string]$Computer = "")
cls
if($Computer -eq ""){$Computer = get-content env:COMPUTERNAME}

$proc = Get-WmiObject win32_processor -computername $Computer

write-host "Strength evaluation for" $Computer -foregroundcolor green
write-host "Speed ..." -foregroundcolor yellow
if ($proc.MaxClockSpeed -ne $NULL)
    {$out = $proc.MaxClockSpeed}
else
    {$out = "unable to determine"}
write-host "MaxClockSpeed               :" $out -foregroundcolor yellow
if ($proc.L2CacheSize -ne $NULL)
    {$out = $proc.L2CacheSize}
else
    {$out = "unable to determine"}
write-host "L2CacheSize                 :"  $out -foregroundcolor yellow
if ($proc.L2CacheSpeed -ne $NULL)
    {$out = $proc.L2CacheSpeed}
else
    {$out = "unable to determine"}
write-host "L2CacheSpeed                :" $out -foregroundcolor yellow
if ($proc.L3CacheSize -ne $NULL)
    {$out = $proc.L3CacheSize}
else
    {$out = "unable to determine"}
write-host "L3CacheSize                 :"  $out -foregroundcolor yellow
if ($proc.L3CacheSpeed -ne $NULL)
    {$out = $proc.L3CacheSpeed}
else
    {$out = "unable to determine"}
write-host "L3CacheSpeed                :"  $out -foregroundcolor yellow
write-host "Strength ..." -foregroundcolor magenta
if ($proc.NumberOfCores -ne $NULL)
    {$out = $proc.NumberOfCores}
else
    {$out = "unable to determine"}
write-host "NumberOfCores               :" $out -foregroundcolor magenta
if ($proc.NumberOfLogicalProcessors -ne $NULL)
    {$out = $proc.NumberOfLogicalProcessors}
else
    {$out = "unable to determine"}
write-host "NumberOfLogicalProcessors   :" $out -foregroundcolor magenta
if ($proc.Name -ne $NULL)
    {$out = $proc.Name}
else
    {$out = "unable to determine"}
write-host "Name                        :" $out -foregroundcolor magenta
write-host "Agility ..." -foregroundcolor cyan
if ($proc.AddressWidth -ne $NULL)
    {$out = $proc.AddressWidth}
else
    {$out = "unable to determine"}
write-host "AddressWidth                :" $out -foregroundcolor cyan

To get the WMI information, use the Get-WmiObject cmdlet and point it toward the Win32_processor WMI class, andbingo!you get all the information you wanted to see (and a lot more).

All you have to do now is read the values you need and present them to the user. So we assign the object the name $proc and use this object to get the information needed. This is seen here:

$proc = Get-WmiObject win32_processor -computername $Computer

The next step is to check whether we could get the information via WMI. For example, we determine whether the attribute of the object is not equal to $NULL. If the attribute is null, which means either there is no value in that attribute or we could not read it, we tell the user that we were unable to determine the information.

Now just display what we found on the screen (using some different colors for the output to make it look good).

As a little extension, I included the option to call the script with a computer name to connect to a remote computer instead of using the local computer. If no computer name is given to the command-line parameter, I use the environment variable COMPUTERNAME to connect to the local computer. Here is what it looks like when the script runs:

Image of the script's product  

Guest commentator: Ed Wilson

Image of guest commentator Ed Wilson 

Ed Wilson is one of the Microsoft Scripting Guys and a well-known scripting expert. He is a Microsoft-certified trainer who has delivered a popular Windows PowerShell workshop to Microsoft Premier Customers worldwide. He has written eight books, five on the subject of Windows scripting, including Windows PowerShell Scripting Guide and Microsoft Windows PowerShell Step by Step, both published by Microsoft Press. His WMI book, Microsoft Windows Scripting with WMI: Self-Paced Learning Guide, is the best-selling WMI book on the market. He has also contributed to nearly a dozen other books and is currently working on a PowerShell Best Practices book.

Ed holds more than 20 industry certifications, including Microsoft Certified Systems Engineer (MCSE) and Certified Information Systems Security Professional (CISSP). Before coming to work for Microsoft, he was a senior consultant for a Microsoft Gold Certified Partner, where he specialized in Active Directory design and Exchange implementation.

 

VBScript solution

The beginner long jump event required using WMI to retrieve specific pieces of information about the processor on the computer. To do this, you can use WMI. In VBScript, the best way to query WMI is to use the GetObject command and pass it a WMI moniker that points to the computer and the WMI namespace. These two pieces of informationcomputer name and WMI namespaceare actually optional, but if you always include them, you can create a WMI template script that will greatly reduce the amount of time spent typing redundant information. Here is the WMI template that I use for creating quick WMI scripts:

WMITemplate.vbs

'==========================================================================
'
'
' NAME: <wmiTemplate.vbs>
'
' AUTHOR: Ed Wilson , MS
' DATE  : $DATE
'
' COMMENT: <Use as a WMI Template>
'
'==========================================================================

Option Explicit
On Error Resume Next
dim strComputer      'target computer
dim wmiNS   'target wmi name space
dim wmiQuery         'the WMI query
dim objWMIService 'sWbemservices object
dim colItems         'sWbemObjectSet object
dim objItem          'sWbemObject

Const RtnImmedFwdOnly = &h30 'iflags for ExecQuery method of swbemservices object
strComputer = "."
wmiNS = "\root\cimv2"
wmiQuery = "Select * from win32_"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & wmiNS)
Set colItems = objWMIService.ExecQuery(wmiQuery,,RtnImmedFwdOnly)

For Each objItem in colItems
    Wscript.Echo ": " & objItem.
    Wscript.Echo ": " & objItem.
    Wscript.Echo ": " & objItem.
    Wscript.Echo ": " & objItem.
    Wscript.Echo ": " & objItem.
    Wscript.Echo ": " & objItem.
Next

In the header section of the script, we declare all of the variables and specify both Option Explicit and On Error Resume. Next, we move into the reference section of the script. (This script follows the four-part scripting model that was created in the Microsoft Press book, Microsoft Windows Scripting Self-Paced Learning Guide.)

A constant, RtnImmedFwdOnly, is created and set to hexadecimal 30. This is equivalent to decimal 48. The iflags parameter is documented on MSDN and is used to control the way the script runs. Hexadecimal 20 means to forward only cursor, and Hexadecimal 10 means to return immediately. When added together, we get Hexadecimal 30, which means forward only and return immediately. This value, when supplied to the ExecQuery method, will greatly improve the performance of the WMI query.

The WMI moniker is used with the GetObject command. It is seen here:

Set objWMIService = GetObject("winmgmts:\\" & strComputer & wmiNS)

A WMI service object is created and stored in the objWMIService variable. This WMI service object is used to execute the query that we stored in the wmiQuery variable. The object that is returned is a collection of Win32_Processor objects. (This happens even if you only have a single processor. The ExecQuery method always returns a collection. So in VBScript, you will always use For..Each...Next). This is shown here:

Set colItems = objWMIService.ExecQuery(wmiQuery,,RtnImmedFwdOnly)

We now use the For…Each…Next construction to walk through the collection of WMI objects that is stored in the colItems variable. Inside the For…Each…Next construction, we use the Wscript.Echo command to display information about the processor. If the script is run in cscript, as seen here, the results will be output to the command console. (For more information about running scripts, refer to the Learning section of the Script Center.)

Cscript BeginnerEvent2.vbs

If you just double-click the script, each value will be displayed in a pop-up box. The completed BeginnerEvent2.vbs script is seen here:

BeginnerEvent2.vbs

'==========================================================================
'
'
' NAME: Beg_2.vbs
'
' AUTHOR: Ed Wilson , MS
' DATE  : 6/2/2009
'
' COMMENT: Summer Scripting Games 2009, Long Jump event.
'
'==========================================================================

Option Explicit
On Error Resume Next
dim strComputer      'target computer
dim wmiNS   'target wmi name space
dim wmiQuery         'the WMI query
dim objWMIService 'sWbemservices object
dim colItems         'sWbemObjectSet object
dim objItem          'sWbemObject

Const RtnImmedFwdOnly = &h30 'iflags for ExecQuery method of swbemservices object
strComputer = "."
wmiNS = "\root\cimv2"
wmiQuery = "Select * from win32_processor"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & wmiNS)
Set colItems = objWMIService.ExecQuery(wmiQuery,,RtnImmedFwdOnly)

For Each objItem in colItems
    Wscript.Echo "AddressWidth: " & objItem.AddressWidth
    Wscript.Echo "L2CacheSize: " & objItem.L2CacheSize
    Wscript.Echo "L2CacheSpeed: " & objItem.L2CacheSpeed
    Wscript.Echo "L3CacheSize: " & objItem.L3CacheSize
    Wscript.Echo "L3CacheSpeed: " & objItem.L3CacheSpeed
    Wscript.Echo "MaxClockSpeed: " & objItem.MaxClockSpeed
    Wscript.Echo "Name: " & objItem.Name
    Wscript.Echo "NumberOfCores: " & objItem.NumberOfCores
    Wscript.Echo "NumberOfLogicalProcessors: " & objItem.NumberOfLogicalProcessors
Next

 

Advanced Event 2: The Long Jump

The long jump is an event in which athletes combine speed, strength, and agility in an attempt to leap as far as possible from a takeoff point. In this event, you will be required to rearrange an Office Excel spreadsheet containing the results of jumpers as well as their expected distances. Who is meeting expectations, exceeding, or falling short in their performance? 

Guest commentator: Chris Bellée

Chris Bellee is a premier field engineer for Microsoft. He is based in Sydney, Australia. He teaches a very popular Windows PowerShell class to Microsoft Premier customers.

Windows PowerShell solution

In my script, I used the 2007 Office System Driver: Data Connectivity Components (ACE components). This is not a standard installation, but you can download the ACE components from the Microsoft Download Center. The advantage of using these components is the .NET Framework classes can then be used to interact with non-Microsoft applications as well. The ACE components are discussed on MSDN. The object model is pretty easy to work with. I have been doing a lot of work recently that required me to use these components. The other thing you need to know about this script is that if you are using a 64-bit version of Microsoft Windows and you use the 64-bit version of Windows PowerShell, you will get an error. The error is misleading, stating, “The provider is not registered on the local machine.” The error message is seen here:

Image of the error message

To work around the 64-bit/32-bit problem, launch the 32-bit version of Windows PowerShell on your 64-bit operating system. You will find the 32-bit of Windows PowerShell in the program folder along with the 64-bit version. The 32-bit Windows PowerShell will have “(x86)” after its name. Here is the complete AdvancedEvent2.ps1 script for the 2009 Summer Scripting Games 2009 advanced long jump event.

AdvancedEvent2.ps1


####################################################

Function Get-ExcelData {

Param([string]$strFilePath="./LongJump_Adv2.xls",$strSheetname="Jump Data")

$strSheetname=$strSheetname+’$’
$strFilepath=resolve-path $strFilePath
$strConn="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$strfilePath;Extended Properties='Excel 12.0;HDR=Yes'"
$objXlsConn = New-Object system.data.oledb.oledbconnection($strConn)
$objXlsConn.open()

$objCmd = New-Object system.data.oledb.oleDbCommand("select * from [$strSheetname]",$objXlsConn)
$objDa = New-Object system.data.oledb.oleDbDataAdapter($objCmd)
$objDs = New-Object system.data.DataSet

[void]$objDa.fill($objDs, "UserData")
$objXlsConn.close()

$colNames = ($objDs.tables["UserData"].columns) | %{$_.columnName}

$objDs.tables["UserData"].rows | foreach {
          $objRow = $_
          $objPS = new-object psobject

          $colNames | foreach-object {
                   $colName = $_
                   $objPS = $objPS | add-member -passthru NoteProperty $colName $objRow[$colName]
                   }
                   $objPS
          }



} # end function

####################################################

function Write-ExcelData {

Param([string]$strFilePath="./LongJump_Adv2.xls",$strSheetname="NewJumpData")

begin {

$ErrorActionPreference = "silentlycontinue"

$strFilepath=resolve-path $strFilePath
$strConn="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$strfilePath;Extended Properties='Excel 12.0;HDR=Yes'"
$objXlsConn = New-Object system.data.oledb.oledbconnection($strConn)
$objXlsConn.open()

$objCmd = New-Object system.data.oledb.oleDbCommand("DROP TABLE [$strSheetname]",$objXlsConn)

$objCmd.ExecuteNonQuery()

$objCmd = New-Object system.data.oledb.oleDbCommand("CREATE TABLE [$strSheetname] (Name char(255), Country char(255),`
 PersonalBest char(255), SeasonBest char(255), Jump1 char(255), Jump2 char(255), Jump3 char(255), Result char(255),`
ExceedAchieveUnderPerform char(255))",$objXlsConn)

$objCmd.ExecuteNonQuery()


} # begin

process {

$objCmd = New-Object system.data.oledb.oleDbCommand("INSERT INTO [$strSheetname] (Name,Country,PersonalBest,`
SeasonBest,Jump1,Jump2,Jump3,Result,ExceedAchieveUnderPerform) values ('$($_.Name)','$($_.Country)','$($_."Personal Best")',`
'$($_."Season Best")','$($_."Jump 1")','$($_."Jump 2")','$($_."Jump 3")','$($_.Result)','$($_."Exceed/Achieve/Under Perform ")')",$objXlsConn)

$objCmd.ExecuteNonQuery()
$_

} # process

end {

$objXlsConn.close()

} # end

} # end function

####################################################

Function Get-BestJump {

process
{
         
          function isDouble($object)
          { 
          [Boolean]($object -as [double])
          }

$arrJump=@()

if (isDouble $($_."Jump 1")) {$arrJump+=[double]$_."Jump 1"}
if (isDouble $($_."Jump 2")) {$arrJump+=[double]$_."Jump 2"}
if (isDouble $($_."Jump 3")) {$arrJump+=[double]$_."Jump 3"}

$Top = $arrJump.getenumerator() | sort-object -desc | select-object -first 1
if ($top -eq $null) {$Top=0}
$_.result=$Top

          if ($_.result -gt $_."season best") {$_."Exceed/Achieve/Under Perform "="Exceed"
          Add-member -inp $_ -membertype NoteProperty -name performance -value 2}
          elseif ($_.result -lt $_."season best") {$_."Exceed/Achieve/Under Perform "="Under Perform"
          Add-member -inp $_ -membertype NoteProperty -name performance -value 0}
          elseif ($_.result -eq $_."season best") {$_."Exceed/Achieve/Under Perform "="Achieve"
          Add-member -inp $_ -membertype NoteProperty -name performance -value 1}
          else {$_."Exceed/Achieve/Under Perform "="Invalid"
          Add-member -inp $_ -membertype NoteProperty -name performance -value -1}

$_

}

} # end function

####################################################

function Sort-Performance {

$input | sort-object -property performance -desc | write-output

}

####################################################

Function Get-OverallBestJump {

$input | sort-object -property result -desc | select-object -first 1

} # end function

####################################################


##########################
# Main()
##########################

Get-ExcelData | Get-BestJump | Sort-Performance | Write-ExcelData | Get-OverallBestJump |
select-object name,country,result,"Exceed/Achieve/Under Perform " | ft -autosize

####################################################

When you run the script, keep in mind the following:

·         Make sure you have installed the ACE components.

·         Make sure you run the script from the same directory as the LongJump_Adv2.xls Office Excel spreadsheet.

·         Make sure you run the script inside 32-bit Windows PowerShell.

When you run the script, you will see the following output:

Image of the script's output

 

Guest commentator: Stephanie Peters

Image of guest commentator Stephanie Peters

 

Stephanie Peters has worked for Microsoft for more than 10 years. She is a senior premier field engineer and a veteran scripting trainer. She writes a very popular blog on TechNet called Something About Scripting.

VBScript solution

I used up my entire track-and-field pun repertoire during the Advanced Scripting Event 1, so for this one, let us just jump right in. (Sorry, couldn’t resist.) This event seems to be a straightforward challenge that is all about using the Office Excel object model, so almost all of the heavy lifting will be done there. The given file, though, is using the Excel 8 format. That being the case, I should probably not go crazy using Excel 2007 tables and so forth. Keeping it to code that works in Excel 2003 might be a good idea. Here is the complete AdvancedEvent2.vbs script:

AdvancedEvent2.vbs

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                     '
' Adv_2.vbs                                                           '
'   written by Stephanie Peters, Microsoft PFE                        '
'   for the 2009 Summer Scripting Games                               '
'                                                                     '
' The goal of this script is to read a particular Excel workbook      '
' to find a winning long jump, determine each jumper's performance    '
' as compared with their average, and to arrange the jumpers in order '
' of personal achievement.                                             '
'                                                                     '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

' Constants required in the scenario
Const FILE_NAME = "LongJump_Adv2.xls"
Const JUMPER_COLUMN_NUMBER = 1
Const SEASON_BEST_COLUMN_LETTER = "$D"
Const JUMP1_COLUMN_LETTER = "$E"
Const JUMP2_COLUMN_LETTER = "$F"
Const JUMP3_COLUMN_LETTER = "$G"
Const RESULT_COLUMN_LETTER = "$H"
Const RESULT_COLUMN_NUMBER = 8
Const RATING_COLUMN_NUMBER = 9
Const xlDescending = 2

' Script Variables
Dim objFSO
Dim objXL, objWorkbook, objWorksheet, objOriginalRange
Dim strCurrentPath, strFilePath, strBackupFilePath
Dim strIndividualResultFormula, strWinnerFormula, strAchievementStatusFormula
Dim intUsedRows, intRowCount
Dim dblBestJumpLength, strBestJumper
Dim intShowNewFile

' Set up objects for later use
Set objXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Locate Excel file in the same folder with the currently running script
strCurrentPath = Replace(Wscript.ScriptFullName,Wscript.ScriptName,"")
strFilePath = strCurrentPath & FILE_NAME
' Determine name for the backup of the original data and make the copy if
' necessary
strBackupFilePath = Replace(strFilePath, ".xls",".Original.xls")
If Not objFSO.FileExists(strBackupFilePath) Then
          objFSO.CopyFile strFilePath, strBackupFilePath
End If

' Open the backed up Excel workbook and locate the data range
Set objWorkbook = objXL.Workbooks.Add(strBackupFilePath)
Set objWorksheet = objWorkbook.Worksheets("Jump Data")
Set objOriginalRange = objWorksheet.UsedRange
intUsedRows = objOriginalRange.Rows.Count

' Set up formulas
strIndividualResultFormula = _
          "=MAX(" & JUMP1_COLUMN_LETTER & "%ROW%," & JUMP2_COLUMN_LETTER & _
          "%ROW%," & JUMP3_COLUMN_LETTER & "%ROW%)"
strWinnerFormula = _
          "=MAX(" & RESULT_COLUMN_LETTER & "$2:" & RESULT_COLUMN_LETTER & _
          intUsedRows & ")"
strAchievementStatusFormula = "=IF((" & RESULT_COLUMN_LETTER & "%ROW%/" & _
          SEASON_BEST_COLUMN_LETTER & "%ROW%) > 1,TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Exceeded"",IF((" & RESULT_COLUMN_LETTER & _
          "%ROW%/" & SEASON_BEST_COLUMN_LETTER & "%ROW%) = 1, TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Achieved"",IF((" & RESULT_COLUMN_LETTER & _
          "%ROW%/" & SEASON_BEST_COLUMN_LETTER & "%ROW%)< 1,TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Under Performed"",""Error"")))"

' Select the best jump
For intRowCount = 2 to intUsedRows
          objWorksheet.Cells(intRowCount,RESULT_COLUMN_NUMBER).Formula = _
                   Replace(strIndividualResultFormula,"%ROW%",intRowCount)
Next

objWorkSheet.Cells(intUsedRows + 1,RESULT_COLUMN_NUMBER).Formula = _
          strWinnerFormula
dblBestJumpLength = objWorksheet.Cells(intUsedRows + 1,RESULT_COLUMN_NUMBER).Value

For intRowCount = 2 to intUsedRows
          If objWorksheet.Cells(intRowCount,RESULT_COLUMN_NUMBER).Value = _
          dblBestJumpLength Then
                   If strBestJumper = "" Then
                             strBestJumper = objWorksheet.Cells(intRowCount,JUMPER_COLUMN_NUMBER).Text
                   Else
                             strBestJumper = strBestJumper & "/" & _
                                      objWorksheet.Cells(intRowCount,JUMPER_COLUMN_NUMBER).Text
                   End If
          End If
Next

' Set up performance against average
For intRowCount = 2 to intUsedRows
          objWorksheet.Cells(intRowCount,RATING_COLUMN_NUMBER).Formula = _
                    Replace(strAchievementStatusFormula,"%ROW%",intRowCount)
Next

' Sort by performance against average
objOriginalRange.Sort objWorksheet.Cells(1,RATING_COLUMN_NUMBER), xlDescending

' Output results
Wscript.echo "The best jump of the meet was " & dblBestJumpLength & _
          " by " & strBestJumper

' Delete the existing file and save the new one.  We still have a backup of the original.
If objFSO.FileExists(strFilePath) Then
          objFSO.DeleteFile strFilePath
End If
objXL.ActiveWorkbook.SaveAs strFilePath, objXL.ActiveWorkbook.FileFormat

' If the user wants to see the new file, display it, otherwise, quit Excel.
intShowNewFile = MsgBox("Would you like to see the new worksheet?",vbYesNo,"Long Jump Results")
If intShowNewFile = vbYes Then
          objXL.Visible = True
Else
          objXL.Quit
End If

Header section

In the header section of the script, we declare the variables and create the constants we will use for the script. This is shown here:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                     '
' Adv_2.vbs                                                           '
'   written by Stephanie Peters, Microsoft PFE                        '
'   for the 2009 Summer Scripting Games                               '
'                                                                     '
' The goal of this script is to read a particular Excel workbook      '
' to find a winning long jump, determine each jumper's performance    '
' as compared with their average, and arrange the jumpers in order    '
' of personal achievement.                                             '
'                                                                     '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

' Constants required in the scenario
Const FILE_NAME = "LongJump_Adv2.xls"
Const JUMPER_COLUMN_NUMBER = 1
Const SEASON_BEST_COLUMN_LETTER = "$D"
Const JUMP1_COLUMN_LETTER = "$E"
Const JUMP2_COLUMN_LETTER = "$F"
Const JUMP3_COLUMN_LETTER = "$G"
Const RESULT_COLUMN_LETTER = "$H"
Const RESULT_COLUMN_NUMBER = 8
Const RATING_COLUMN_NUMBER = 9
Const xlDescending = 2

' Script Variables
Dim objFSO
Dim objXL, objWorkbook, objWorksheet, objOriginalRange
Dim strCurrentPath, strFilePath, strBackupFilePath
Dim strIndividualResultFormula, strWinnerFormula, strAchievementStatusFormula
Dim intUsedRows, intRowCount
Dim dblBestJumpLength, strBestJumper
Dim intShowNewFile

Reference section

In the reference section of the script, there are two important objects we need to set up to use later—the Excel.Application object and the Scripting.FileSystemObject object. I love that the word “Object” is in the name of the object, forcing me to say it twice. It always makes me smile. (It is kind of like New York, New York.) Here are the two objects:

' Set up objects for later use
Set objXL = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")

The remainder of the reference section of the script is shown here:

' Locate Excel file in the same folder with the currently running script
strCurrentPath = Replace(Wscript.ScriptFullName,Wscript.ScriptName,"")
strFilePath = strCurrentPath & FILE_NAME
' Determine name for the backup of the original data and make the copy if
' necessary
strBackupFilePath = Replace(strFilePath, ".xls",".Original.xls")
If Not objFSO.FileExists(strBackupFilePath) Then
          objFSO.CopyFile strFilePath, strBackupFilePath
End If

Worker section

The main portion of the script is the worker section. In order to process the LongJump_Adv2.xls file, we need to open it in Office Excel using the Excel.Application object we initialized earlier:

' Open the backed-up Excel workbook and locate the data range
Set objWorkbook = objXL.Workbooks.Add(strBackupFilePath)
Set objWorksheet = objWorkbook.Worksheets("Jump Data")
Set objOriginalRange = objWorksheet.UsedRange
intUsedRows = objOriginalRange.Rows.Count

I usually find that when you are going to have some complex string-building to do, it’s better to knock it out early, so all the formulas that I want to use in the workbook are defined here. I would have put them in the reference section, but I needed to know the size of the used range to plug in. This means that task had to wait until after we opened the file. The string “%ROW$” is the placeholder we will replace later with an actual row number:

' Set up formulas
strIndividualResultFormula = _
          "=MAX(" & JUMP1_COLUMN_LETTER & "%ROW%," & JUMP2_COLUMN_LETTER & _
          "%ROW%," & JUMP3_COLUMN_LETTER & "%ROW%)"
strWinnerFormula = _
          "=MAX(" & RESULT_COLUMN_LETTER & "$2:" & RESULT_COLUMN_LETTER & _
          intUsedRows & ")"
strAchievementStatusFormula = "=IF((" & RESULT_COLUMN_LETTER & "%ROW%/" & _
          SEASON_BEST_COLUMN_LETTER & "%ROW%) > 1,TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Exceeded"",IF((" & RESULT_COLUMN_LETTER & _
          "%ROW%/" & SEASON_BEST_COLUMN_LETTER & "%ROW%) = 1, TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Achieved"",IF((" & RESULT_COLUMN_LETTER & _
          "%ROW%/" & SEASON_BEST_COLUMN_LETTER & "%ROW%)< 1,TEXT(" & _
          RESULT_COLUMN_LETTER & "%ROW%/" & SEASON_BEST_COLUMN_LETTER & _
          "%ROW%,""000.0%"") & "" - Under Performed"",""Error"")))"

 

Now we will start to populate all those formulas. We start with the individual Result column. The formula we’re populating here is something like “=MAX($E2,$F2,$G2)”:

 

' Determine the best jump for each individual jumper (in Excel formula)
For intRowCount = 2 to intUsedRows
          objWorksheet.Cells(intRowCount,RESULT_COLUMN_NUMBER).Formula = _
                   Replace(strIndividualResultFormula,"%ROW%",intRowCount)
Next

 

After the individual results are posted, we can use another formula to find the overall winner. That formula goes at the bottom of Column H, and it looks like this:  “=MAX($H$2:$H19)”:

 

' Determine the best jump for any jumper (in Excel formula)
objWorkSheet.Cells(intUsedRows + 1,RESULT_COLUMN_NUMBER).Formula = _
          strWinnerFormula
dblBestJumpLength = _
objWorksheet.Cells(intUsedRows + 1,RESULT_COLUMN_NUMBER).Value

 

After we know the overall winning distance, we can find all the jumpers who achieved that distance. If there is more than one with the winning distance, they both need to be recognized. In this case, there is not a tie, but we do not know that unless we have previous knowledge of the contents of the file—and that is cheating:

 

' Find all the jumpers who had the best jump distance
For intRowCount = 2 to intUsedRows
          If objWorksheet.Cells(intRowCount,RESULT_COLUMN_NUMBER).Value = _
          dblBestJumpLength Then
                   If strBestJumper = ""
Then
                             strBestJumper = _
                             objWorksheet.Cells(intRowCount,JUMPER_COLUMN_NUMBER).Text
                  
Else
                             strBestJumper = strBestJumper & "/" & _
                             objWorksheet.Cells(intRowCount,JUMPER_COLUMN_NUMBER).Text
                  
End If
         
End If
Next

 

The next step is to compare each jumper’s best jump with his or her season average. We do this by adding a formula in column I something like this: 

“=IF(($H2/$D2) > 1,TEXT($H2/$D2,"000.0%") & " - Exceeded",IF(($H2/$D2) = 1, TEXT($H2/$D2,"000.0%") & " - Achieved",IF(($H2/$D2)< 1,TEXT($H2/$D2,"000.0%") & " - Under Performed","Error")))”

 

OK, this step is complicated. It is using logic to find out if the jumper exceeded, achieved, or underperformed as compared with his or her season average. Based on the task, we did not have to include their percentage of overperformance or underperformance, but I thought it might be a nice addition—and it makes the upcoming sort much easier:

 

' Set up performance against average (in Excel formula)
For intRowCount = 2 to intUsedRows
          objWorksheet.Cells(intRowCount,RATING_COLUMN_NUMBER).Formula = _
                   Replace(strAchievementStatusFormula,"%ROW%",intRowCount)
Next

All that’s left is to sort the performance metrics. I had enough of sorting on the previous exercise, so luckily, Excel’s object model makes this a snap. I already had a range object from earlier in the script that exactly matches the range I want to sort, so it is just a matter of calling the sort method.

 

' Sort by performance against average
objOriginalRange.Sort objWorksheet.Cells(1,RATING_COLUMN_NUMBER), _
          xlDescending

Output section

We determined earlier who won the event at this meet, so it is probably a good idea to go ahead and display that on the console for easy reading:

 

' Output results
Wscript.echo "The best jump of the meet was " & dblBestJumpLength & _
          " by " & strBestJumper

 

Now, for the Excel file—which is the real output here—we need to save the current version of the file. If the file had been previously processed, we would need to delete that copy to make room for the new one:

 

' Delete the existing file and save the new one.  We still have a backup
' of the original.
If objFSO.FileExists(strFilePath)
Then
          objFSO.DeleteFile strFilePath
End If
objXL.ActiveWorkbook.SaveAs strFilePath, objXL.ActiveWorkbook.FileFormat

 

We can also use the MsgBox function to ask the user if they would like to see the file or not. Up to this point, we never set the Visible property of the Excel.Application object, and its default value is False. That means, so far, Office Excel has been running in the background (if it was not already running). We can flip that visible property to True to display the workbook and allow the user to interact with it. If they choose no, we can just quit Excel, because we already saved the workbook:

 

' If the user wants to see the new file, display it; otherwise, quit Excel.
intShowNewFile = MsgBox("Would you like to see the new worksheet?", _
vbYesNo,"Long Jump Results")
If intShowNewFile = vbYes
Then
          objXL.Visible =
True
Else
          objXL.Quit
End If

The console output for this script is not very interesting. The output is shown here along with the dialog box that prompts you to display the Office Excel spreadsheet. Notice that I am running cscript inside of a Windows PowerShell 2.0 prompt. The script code is VBScript. I am just launching the cscript process inside of Windows PowerShell. I do this because I seldom use the old-fashioned command prompt:

Image of the dialog box displayed 

 

If at this prompt we click No, the script will shut down Excel and we will be none the wiser. If we click Yes, we get to admire our handiwork. The Office Excel spreadsheet is seen here:

Image of the Office Excel spreadsheet that is produced

 

Awesome job Scott, Marcus, Chris, and Stephanie! (Scripting Editor: And Ed!) We draw to a conclusion our commentaries for the long jump event of the 2009 Summer Scripting Games. Join us tomorrow as we reveal solutions for the discus throw and usher in another group of commentators for Event 3. It should be another fact-filled day. Until tomorrow, keep on scripting. 

 

Ed Wilson and Craig Liebendorfer, Scripting Guys

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • All the Scripting Games links in one location! Let the learning begin. (We will update this page every