Hi,
I'm trying to work out a Powershell script that will display only the value result of a Excel formula, so that if I delete the cell the formula is applied to the result is not affected.
This is what I have:
#Create Excel Object
$xl = New-Object -ComObject Excel.Application
$xl.visible = $true
$xl.DisplayAlerts = $false
#Open Existing Workbook, Name & Activate Worksheet
$wb = $xl.Workbooks.Open("C:xxxx.csv")
$ws = $xl.Worksheets.Item(1).name="InventoryRAW"
$xl.Worksheets.Item("InventoryRAW").activate()
#delete columns
[void]$xl.Cells.Item(1,1).EntireColumn.Delete()
[void]$xl.Cells.Item(1,1).EntireColumn.Delete()
[void]$xl.Cells.Item(1,6).EntireColumn.Delete()
[void]$xl.Cells.Item(1,5).EntireColumn.Delete()
[void]$xl.Cells.Item(1,3).EntireColumn.Delete()
[void]$xl.Cells.Item(2,1).EntireRow.Delete()
[void]$xl.Cells.Item(2,2).EntireRow.Delete()
#Excel Formula
$SN = ('=RIGHT(A2,LEN(A2)-FIND("K",A2)-1)')
$HN = ('=LEFT(B2,LEN(B2)-FIND("v",B2)-3)')
$xl.Cells.Item(2,5).Value2 = $SN
$xl.Cells.Item(2,4).Value2 = $HN
I thought I might be able to do something like = $SN.Value and = $HN.Value but it's not working.
Any help will be greatly appreciated
Thanks,
Rob
