none
Powershell to append data to excel workbook RRS feed

  • Question

  • Hello Everyone

    I have a PS which get specific information from a local system and creates an excel workbook with column names.

    I would like to run this PS on another system and have that data appended to the excising workbook and not overwrite the data in the workbook already.

    This is my PS Code.

    # Get date and add to File name
    
    
    $CurrentDate = Get-Date
    $CurrentDate = $CurrentDate.ToString('MMM-DD-YYYY')
    
    # This information is pulled from Function Get-PCS for the Domain OU
    
    
    # This creates the Excel workbook and puts data in workbook columns.
    
    $excel = New-Object -ComObject excel.application
    
    $excel.visible = $True
    
    $workbook = $excel.Workbooks.Add()
    
    $workbook.Worksheets.Add()
    
    $BiosInfo= $workbook.Worksheets.Item(1)
    
    
    #Headers in the worksheet
    
    $BiosInfo.Cells.Item(1,1) = 'Workstation'
    $BiosInfo.Cells.Item(1,2) = 'OS'
    $BiosInfo.Cells.Item(1,3) = 'Manufacturer'
    $biosInfo.Cells.Item(1,4) = 'Type'
    $BiosInfo.Cells.Item(1,5) = 'Model'
    $BiosInfo.Cells.Item(1,6) = 'Bios Version'
    $BiosInfo.Cells.Item(1,7) = 'Last Logged On'
    
    $row = 2
    $column = 1
    
       
     
    
      $os = Get-WmiObject -class win32_operatingsystem -computer localhost
      $Type = Get-WmiObject -class win32_computersystemproduct -computer localhost
      $comp = Get-WmiObject -class win32_computersystem -computer localhost
      $Bios = Get-WmiObject -class Win32_BIOS  -computer localhost 
       
        
        #Workstations Name
        $BiosInfo.Cells.Item($row,$column) = $comp.Name
        $column++
        #Operating System
        $BiosInfo.Cells.Item($row,$column) = $os.name.split("|")[0]
        $column++
        #Bios Manufacturer
        $BiosInfo.Cells.Item($row,$column) = $bios.Manufacturer
        $column++
        #Manufacturer Type
        $BiosInfo.Cells.Item($row,$column) = $Type.Version
        $column++
        #Workstation Model
        $BiosInfo.Cells.Item($row,$column) = $comp.Model
        $column++
        #Bios Version
        $BiosInfo.Cells.Item($row,$column) = $bios.SMBIOSBIOSVersion
        $column++
        #Last Logged On User
        $BiosInfo.Cells.Item($row,$column) = $comp.UserName
        #Increment to next Row and reset Column
        $row++
        $column = 1
              
            
       
       
    
    
    $usedRange = $BiosInfo.UsedRange						
    $usedRange.EntireColumn.AutoFit() | Out-Null
    
    $workbook.SaveAs("K:\CS IT\Applications\BIOS Updates\BiosVersions-$((Get-Date).ToString('MMM-dd-yyyy')).xlsx")
    $excel.Quit()

    Thanks for you help

    Tuesday, March 6, 2018 7:53 PM

Answers

  • Rather than use Excel, it is usually a lot easier to just output custom objects and then export as CSV (Export-Csv). One advantage is that if you need to do so you can easily use Import-Csv (or import into Excel, if you really need to). CSV is a much more universal format.

    -- Bill Stewart [Bill_Stewart]

    Tuesday, March 6, 2018 10:38 PM
    Moderator

All replies

  • I recommend asking the author to change the script for you.  We do not modify scripts on request.

    If you have a specific coding question then ask it such as, "How do I find the end of the spreadsheet?"


    \_(ツ)_/

    Tuesday, March 6, 2018 8:30 PM
  • I am the person that did this PS. I looked at various PS scripts and used pieces to do this.

    I just wanted to know how I can append to the excel workbook. not asking to re-write the script.

    Tuesday, March 6, 2018 9:41 PM
  • Rather than use Excel, it is usually a lot easier to just output custom objects and then export as CSV (Export-Csv). One advantage is that if you need to do so you can easily use Import-Csv (or import into Excel, if you really need to). CSV is a much more universal format.

    -- Bill Stewart [Bill_Stewart]

    Tuesday, March 6, 2018 10:38 PM
    Moderator
  • Hello Bill

    Yes you are correct to export to CSV but it is a lot neater save this to excel workbook as you do not need to adjust the columns to read/see the information as you do in a CSV file.

    thanks

    Tuesday, March 6, 2018 10:47 PM
  • The information doesn't have to be visible for it to be present in the file.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, March 6, 2018 10:49 PM
    Moderator
  • Hello Bill

    Yes you are correct to export to CSV but it is a lot neater save this to excel workbook as you do not need to adjust the columns to read/see the information as you do in a CSV file.

    thanks

    That is why Excel ahs templates.  Just open the CSV and a custom formatted template and Save it as an XLSX and you are done.


    \_(ツ)_/

    Tuesday, March 6, 2018 10:51 PM
  • I am the person that did this PS. I looked at various PS scripts and used pieces to do this.

    I just wanted to know how I can append to the excel workbook. not asking to re-write the script.

    Then know that the same code can add if you get the last row of "UsedRange" and start adding the new rows at that point. at that point.

    $lastrow = $sheet.UsedRange.Rows.Count


    \_(ツ)_/

    Tuesday, March 6, 2018 10:57 PM