Powershell/Excel - Deleting Partial Cell Contents

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 2 years, 1 month ago.

  • Author
    Posts
  • #20145
    Profile photo of Rob Phoenix
    Rob Phoenix
    Participant

    Hi,

    I'm trying to delete part of the content of a cell in excel using Powershell.

    One cell is:

    *1 CISCO1941/K9 FCZ1645C1QB
    and needs to be:

    FCZ1645C1QB

    and the second cell is:

    Leon-ver-live

    and needs to be:

    Leon

    So far I've been transferring the required part to a new cell in the same worksheet, and am now trying to transfer the results to a new workbook(Excuse the bad code):

    # Get info from CSV and put into csv
    Select-String -Path c:\*-Live CISCO1941/K9 -AllMatches | Export-CSV C:\xxxx.csv
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Excel.DisplayAlerts = $false
    #build spread sheet and import data
    $Excel.Workbooks.Open("C:\xxxx.csv")
    $Excel.Worksheets.Item(1).name="InventoryRAW"
    $Excel.Worksheets.Item("InventoryRAW").activate()

    #delete columns
    [void]$Excel.Cells.Item(1,1).EntireColumn.Delete()
    [void]$Excel.Cells.Item(1,1).EntireColumn.Delete()
    [void]$Excel.Cells.Item(1,6).EntireColumn.Delete()
    [void]$Excel.Cells.Item(1,5).EntireColumn.Delete()
    [void]$Excel.Cells.Item(1,3).EntireColumn.Delete()
    [void]$Excel.Cells.Item(2,1).EntireRow.Delete()

    #Autofit
    $Excel.Cells.Item("1,5").EntireColumn.AutoFit()
    $Excel.Cells.Item("1,4").EntireColumn.AutoFit()
    $Excel.Cells.Item("1,3").EntireColumn.AutoFit()
    $Excel.WorkSheets.item("InventoryRAW").UsedRange.Columns.Autofit() | Out-Null
    [void]$Excel.Cells.Item(2,1).EntireRow.Delete()

    #Excel Formula
    $Excel.Cells.Item(2,5).Value2 = '=RIGHT(A2,LEN(A2)-FIND("K",A2)-1)'
    $Excel.Cells.Item(2,4).Value2 = '=LEFT(B2,LEN(B2)-FIND("v",B2)-3)'

    #Put result in new csv file
    $xl = new-object -comobject excel.application
    $xl.Visible = $true
    $xl.DisplayAlerts = $False
    $wb = $xl.Workbooks.Add()
    $ws = $wb.Worksheets.Item(1)
    [void]$xl.Worksheets.Item(3).Delete()
    [void]$xl.Worksheets.Item(2).Delete()
    $ws.name = "Inventory"

    In trying to do this I've stumbled upon the .split function and am trying to work out if it's possible to use it to just update the original file instead of going through the rigmarole of transferring data from cell to cell to cell. So far I have this:

    # Get info from CSV and put into csv
    Select-String -Path c:\*-Live CISCO1941/K9 -AllMatches | Export-CSV C:\Users\robertph\Documents\RepeatableDesign\LeonPowershellScript\cs5-export.csv
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $true
    $Excel.DisplayAlerts = $false
    #build spread sheet and import data
    $Excel.Workbooks.Open("C:\Users\robertph\Documents\RepeatableDesign\LeonPowershellScript\csv5-export.csv")
    $Excel.Worksheets.Item(1).name="InventoryRAW"
    $Excel.Worksheets.Item("InventoryRAW").activate()

    $HostName = $Excel.Cells.Item("3,4")
    $Object = @()
    Foreach ($Entry in $HostName) {
    $one = ($Entry.Split(-)[0]).Trim(-)
    $two = ($Entry.Split(-)[1]).Trim(-)
    $three = ($Entry.Split(-)[2]).Trim(-)

    Remove-Item $two
    Remove-Item $three
    }
    Any advice is much appreciated!

    Rob

  • #20182
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You may want to post your question in StackOverflow; I'm seeing a pattern of Excel related questions going unanswered here because everyone is desperately trying to move away from programming Excel. It's awkward, and is a very old COM interface. I've been moving to SSRS, myself. Much nicer, and I can still use PowerShell to populate the data.

You must be logged in to reply to this topic.