时间戳

首页 > 解决方案 > Making a list of computers and their rdp settings

excel - Making a list of computers and their rdp settings

问题描述

I'm trying to use Powershell to compile a simple excel spreadsheet that lists my computer's ping status's, RPD settings, and Hostnames.

I'm frustrated with the portion of the code that uses excel. I'm not really sure why, but when I run this code I'm getting an multiple errors for calling a method on a null value expression.

$ComputerList = Get-Content C:\Users\\Desktop\RDPSTATUS\ComputerList.txt
$excel_file_path = 'C:\Users\\Desktop\RDPSTATUS\RDPSTATUS.xlsx'


# instantiate EXCEL object
$Excel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path)
$ExcelWorkSheet = $Excel.Worksheets.item("sheet1")
$ExcelWorksheet.activate()

$row = 0
$col = 0

$ExcelWorkSheet.Cells.Item($row , $col) = "Device Name"
$ExcelWorkSheet.Cells.Item($row , $col++) = "RDP Status"
$ExcelWorkSheet.Cells.Item($row , $col+2) = "ping Status"

$row = 1

foreach ($computername in $ComputerList){

    $ExcelWorkSheet.Cells.Item($row , $col) = $computername
    $PingStatus = Gwmi Win32_PingStatus -Filter "Address = '$computername'" | Select-Object StatusCode

    If ($PingStatus.StatusCode -eq 0)
    {
        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computername )
        $regKey= $reg.OpenSubKey("System\\CurrentControlSet\\Control\\Terminal Server" ,$true)
        $Value = $regkey.GetValue("fDenyTSConnections")
        $ExcelWorkSheet.Cells.Item($row , $col+2) = "PING"
    }
    else {
        $ExcelWorkSheet.Cells.Item($row , $col+2) = "NO PING"
    }

    If ($Value -eq 1){
        $ExcelWorkSheet.Cells.Item($row , $col++) = "RDP is Disabled"
    }

    If ($Value -eq 0){
        $ExcelWorkSheet.Cells.Item($row , $col++) = "RDP is Enabled"
    }
}

$ExcelWorkBook.Save()
$ExcelWorkBook.Close()
$Excel.Quit([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel))
Stop-Process -Name EXCEL -Force

标签： excelpowershellregistry

解决方案

你有几个直接的问题，我可以看到：

  1. $ExcelWorkSheet = $Excel.Worksheets.item("sheet1")应该是$ExcelWorkbook.Worksheets.item("sheet1")。工作表是工作簿的属性，而不是 Excel 应用程序的属性。

  2. $row并且$col起始值应该是1，而不是0。

  3. $ExcelWorksheet.activate()没有必要。

推荐阅读