Powershell/Excel - Deleting Partial Cell Contents

PowerShell Help
1

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

2

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.