Append Powershell output to an Excel file
<p>I am writing a powershell script that will gather some information from external HDDs I am archiving at work (connected via a USB HDD dock), and I would like to have the script add this info to an Excel file as one of the last steps.  I am somewhat familiar with Powershell, but not so much on working with Excel files with Powershell.</p>

<p>Basically, the script needs to be able to determine the first empty row in the sheet, and then paste some info into columns A, B, C, etc of that row.  I have found a couple different ways of doing this, but for some reason the variable that would hold the row number to add info to always seems to be blank.  I'm not sure if I'm doing something wrong, so any advice would be much appreciated.</p>

<p>It's a bit long, but here is my script in it's current state.  The bottom 2 sections (labeled with "Append the info to an excel document...") are the script samples I have found (and source links) that supposedly does what I'm trying to do.</p>

<p>Thanks in advance for the help.</p>

<p><pre>
cls
($null = reg.exe unload HKLM\EXTERNAL) 2> $null
 #IF ($AdminCred -eq $null) {New-Variable -Name AdminCred -Value (Get-Credential -UserName Administrator -Message "Enter password for the admin account") -Scope Global -Force}
$null = reg.exe load HKLM\EXTERNAL D:\Windows\System32\Config\SYSTEM</p>

<p>if ($LASTEXITCODE -eq 0) {
    #Find the hostname
        New-Variable -Name Hostname -Value ((Get-ItemProperty -Path HKLM:\EXTERNAL\ControlSet001\Control\ComputerName\ComputerName).ComputerName) -Scope Global -Force
        [GC]::Collect()
        IF ($LASTEXITCODE -ne 0) {Write-Host LastExitCode is $LASTEXITCODE}
        $null = reg.exe unload HKLM\EXTERNAL</p>

<code>#Find the serial number
    Clear-Variable -Name SerialNumber -Scope Global -Force
    New-Variable -Name SerialNumber -Value (Read-Host "Please enter the HDD's serial number.") -Scope Global -Force
    # I was originally trying to programatically gather the serial number of the HDD, but that is an issue for another post.
    # $Disks = Get-WMIObject -class win32_PhysicalMedia
    # $SerialNumber = foreach($Disk in $Disks) {IF ($Disk.SerialNumber -ne '     WD-WCC2EAV91692') {Write-Host $Disk.SerialNumber}}

#Find usernames    
    $Win7 = Test-Path D:\Users
    $WinXP = Test-Path 'D:\Documents and Settings'
    IF ($Win7 -eq 'True') {$Win7Users = (Get-ItemProperty -Path D:\Users\* -Exclude ADMINI~1,Administrator,Public,TEMP,UpdatusUser,'All Users',User).name}
    IF ($WinXP -eq 'True') {$WinXPUsers = (Get-ItemProperty -Path 'D:\Documents and Settings\*' -Exclude ADMINI~1,Administrator,Public,TEMP,UpdatusUser,'All Users',User).name}

#Display Hostname and Usernames
    Write-Host Hostname: -ForegroundColor Green
    $hostname
    Write-Host
    Write-Host Serial Number: -ForegroundColor Green
    $SerialNumber
    Write-Host
    Write-Host User List: -ForegroundColor Green
    $Win7Users
    $WinXPUsers

#Print the output
    Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "Hostname:")
    Out-File -FilePath C:\HDDinfo.txt -InputObject $Hostname -Append
    Add-Content -Path C:\HDDinfo.txt `n
    Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "Serial Number:") -Append
    Out-File -FilePath C:\HDDinfo.txt -InputObject $SerialNumber -Append
    Add-Content -Path C:\HDDinfo.txt `n
    Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "User List:") -Append
    Out-File -FilePath C:\HDDinfo.txt -InputObject $Win7Users -Append
    Out-File -FilePath C:\HDDinfo.txt -InputObject $WinXPUsers -Append
    Out-Printer \\servername\printername -InputObject (Get-Content C:\HDDinfo.txt)

#Append the info to an Excel document - Possible method 1
#   http://stackoverflow.com/questions/8452408/using-powershell-to-append-a-table-to-the-end-of-an-excel-file-the-last-row
        $ExcelPath = "C:\HDDInfo.xlsx"
        $xldown = -4121 # see: http://msdn.microsoft.com/en-us/library/bb241212(v=office.12).aspx
        $xlup = -4162
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $False
        $ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
        $ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")
        $ExcelWorkSheet.activate()
    # Find the last used cell
        {$lastRow = $ExcelWorkSheet.Cells.Range("A1048576").End($xlup).row
        $nextRow = $lastRow + 1
        $range = $ExcelWorkSheet.Range("A$nextRow")
        $ExcelWorkSheet.Paste($range)}
    # Append info to the spreadsheet
        $ExcelWorkSheet.Cells.Item($row,1) = 'COLUMN 1 Text'
        $ExcelWorkSheet.Cells.Item($row,2) = 'COLUMN 2 Text'
        $ExcelWorkSheet.Cells.Item($row,3) = 'COLUMN 3 Text'
        $ExcelWorkSheet.Cells.Item($row,4) = 'COLUMN 4 Text'
        $ExcelWorkSheet.Cells.Item($row,5) = 'COLUMN 5 Text'
    # Save and close
        $ExcelWorkBook.Save()
        $ExcelWorkBook.Close()
        $Excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
        Stop-Process -Name EXCEL -Force

#Append the info to an Excel document - Possible method 2
#   http://www.adamtheautomator.com/powershell-excel-worksheet/
        $excel_file_path = 'C:\HDDInfo.xlsx'
    # Instantiate the COM object
        $Excel = New-Object -ComObject Excel.Application
        $ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path)
        $ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
        $ExcelWorkSheet.activate()
    # Find the first row where the first 7 columns are empty
        $row = ($ExcelWorkSheet.UsedRange.Rows | Where-Object { ($_.Value2 | Where-Object {$_ -eq $null}).Count -eq 7 } | select -first 1).Row
        $ExcelWorkSheet.Cells.Item($row,1) = 'COLUMN 1 Text'
        $ExcelWorkSheet.Cells.Item($row,2) = 'COLUMN 2 Text'
        $ExcelWorkSheet.Cells.Item($row,3) = 'COLUMN 3 Text'
        $ExcelWorkSheet.Cells.Item($row,4) = 'COLUMN 4 Text'
        $ExcelWorkSheet.Cells.Item($row,5) = 'COLUMN 5 Text'
    # Save and close
        $ExcelWorkBook.Save()
        $ExcelWorkBook.Close()
        $Excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
        Stop-Process -Name EXCEL -Force
}
</code></pre>

<p></p>