PullList.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
#Resolves each computer to FQDN. DNS suffix must exist in the local machine.
Function ResolveDNS ($Computers) {
    Write-Verbose -Verbose "Resolving the Computer Name..." 
    $ResolvedComputers = $Computers | foreach { 
        Try { 
            Resolve-DnsName $_ -ErrorAction Stop | select Name -ExpandProperty Name -First 1
        } 
        Catch { Write-Warning ($_.Exception.Message + ". " + "Try using FQDN.") }
    }
    if ($ResolvedComputers) {
        return $ResolvedComputers
    }
    else {
        Break
    }
}

#Pulls the items from the excel sheet starting from column 1, row 2
Function PullList ($filepath) {

    $sheetName = "Sheet1" 
    $objExcel = New-Object -ComObject Excel.Application
    $workbook = $objExcel.Workbooks.Open($filepath)
    $sheet = $workbook.Worksheets.Item($sheetName)
    $rowMax = ($sheet.UsedRange.Rows).count
    $rowName, $colName = 1, 1
    $List = @()
    for ($i = 1; $i -le $rowMax - 1; $i++) {
        $name = $sheet.Cells.Item($rowName + $i, $colName).text
        $List += $name.trim()
    }       
    $objExcel.quit()
    return $List
   
} 

#Validates if the input is individual Computer Name or Excel sheet.
Function ValidateComputerName { 
    [CmdletBinding()]
    Param(
        
        [Parameter(Mandatory = $True,
            Position = 0)]
        [ValidateNotNullOrEmpty()]
        [string[]]$ComputerName        
    ) 
    try {
        if ($ComputerName -match "^*.xlsx$") {                       
            $pathExists = Test-Path $ComputerName
            if ($pathExists -eq $true) {
                Write-Verbose -Verbose "Pulling the Computer List..."                        
                $newComputerName = PullList ($ComputerName)                                
            }
            else { Write-Warning "Check the file path $ComputerName"}
        }
        else {
            $newComputerName = $ComputerName
        } 
        return $newComputerName
    }
    catch {
        Write-Warning $_.Exception.Message
    }
    
}