none
Powershell error (cannot index into a NULL array) RRS feed

  • Question

  • Hi Scripting Guru's,

    I'm still new to Power-shell and wanting to not rely on excel to much here, but what I am basically trying to do is take an existing excel and populate it with some data. But I get the error cannot index into a NULL array with the attached.

    If you could please provide some advice/assistance in helping me understand the error and how I need to go about resolving it?

    Regards,

    Scramble

    $path = "C:\support\users.xlsx"
    
    $xl = new-object -comobject excel.application
    $xl.Visible = $True
    
    if (Test-Path $path){
    	$wb = $xl.WorkBooks.Open($path)
        $wb = $xl.Worksheets.Item(1)
    }else{
    	$wb = $xl.Workbooks.Add()
        $wb = $xl.Worksheets.Item(1)
    }
    
    
    $row = 2
    Get-Content  C:\support\users.txt |
    	ForEach-Object{
    		$user = Get-AdUser $_ -Server myserver.com -Properties SamAccountName, Enabled, LockedOut, LastLogonDate
    		$wb.Sheets[1].Cells[$row, 1].Value2 = $user.SamAccountName
    		$wb.Sheets[1].Cells[$row, 2].Value2 = $user.Enabled
            $wb.Sheets[1].Cells[$row, 3].Value2 = $user.LockedOut
            $wb.Sheets[1].Cells[$row, 4].Value2 = $user.LastLogon
    
    
    		if ($user.Enabled){
    			$wb.Sheets[1].Cells[$row, 2].Value2 = "True"
                $wb.Sheets[1].Cells[$row, 3].Value2 = "True"
    
    		}else{
    			$wb.Sheets[1].Cells[$row, 2].Value2 = 'False'
                $wb.Sheets[1].Cells[$row, 3].Value2 = 'False'
    		}
    		$row++
    	}

    Wednesday, January 22, 2020 12:15 AM

Answers

  • This is how we would normally do this:

    $path = 'C:\support\users.xlsx'
    
    $xl = new-object -comobject excel.application
    $xl.Visible = $True
    
    if(Test-Path $path){
    	$wb = $xl.WorkBooks.Open($path)
    }else{
    	$wb = $xl.Workbooks.Add()
    }
    
    $row = 1
    Get-Content  C:\support\users.txt |
    	ForEach-Object{
    		$row++
    		$user = Get-AdUser $_ -Server myserver.com -Properties LockedOut, LastLogonDate
    		$wb.Sheets[1].Cells[$row, 1].Value2 = $user.SamAccountName
    		$wb.Sheets[1].Cells[$row, 2].Value2 = $user.Enabled
                    $wb.Sheets[1].Cells[$row, 3].Value2 = $user.LockedOut
                    $wb.Sheets[1].Cells[$row, 4].Value2 = $user.LastLogon
    		$wb.Sheets[1].Cells[$row, 2].Value2 = $user.Enabled
    		$wb.Sheets[1].Cells[$row, 3].Value2 = $user.Enabled
    	}


    \_(ツ)_/


    • Edited by jrv Wednesday, January 22, 2020 12:59 AM
    • Marked as answer by Scramble69 Wednesday, January 22, 2020 1:32 AM
    Wednesday, January 22, 2020 12:58 AM

All replies

  • You need to post the complete error message.


    \_(ツ)_/

    Wednesday, January 22, 2020 12:50 AM
  • This is how we would normally do this:

    $path = 'C:\support\users.xlsx'
    
    $xl = new-object -comobject excel.application
    $xl.Visible = $True
    
    if(Test-Path $path){
    	$wb = $xl.WorkBooks.Open($path)
    }else{
    	$wb = $xl.Workbooks.Add()
    }
    
    $row = 1
    Get-Content  C:\support\users.txt |
    	ForEach-Object{
    		$row++
    		$user = Get-AdUser $_ -Server myserver.com -Properties LockedOut, LastLogonDate
    		$wb.Sheets[1].Cells[$row, 1].Value2 = $user.SamAccountName
    		$wb.Sheets[1].Cells[$row, 2].Value2 = $user.Enabled
                    $wb.Sheets[1].Cells[$row, 3].Value2 = $user.LockedOut
                    $wb.Sheets[1].Cells[$row, 4].Value2 = $user.LastLogon
    		$wb.Sheets[1].Cells[$row, 2].Value2 = $user.Enabled
    		$wb.Sheets[1].Cells[$row, 3].Value2 = $user.Enabled
    	}


    \_(ツ)_/


    • Edited by jrv Wednesday, January 22, 2020 12:59 AM
    • Marked as answer by Scramble69 Wednesday, January 22, 2020 1:32 AM
    Wednesday, January 22, 2020 12:58 AM
  • Cheers JRV,

    Will remember to add the error in future. Should have realized this.

    As always appreciate your input to a Newbie who is still learning.

    Regards,

    Scramble

    Wednesday, January 22, 2020 1:34 AM