Powershell/Excel - Deleting Partial Cell Contents

Welcome Forums General PowerShell Q&A Powershell/Excel - Deleting Partial Cell Contents

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
4 years, 3 months ago.

  • Author
    Posts
  • #20145

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    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.

The topic ‘Powershell/Excel - Deleting Partial Cell Contents’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort