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