none
Convert Excel Formula to Display Value only RRS feed

  • Question

  • Hi,

    I'd like to 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

    • Moved by Bill_Stewart Thursday, October 30, 2014 2:14 PM Move to more appropriate forum
    Thursday, October 30, 2014 2:13 PM

All replies

  • To replace a formula buy its value just reassign the cell.

    $xl.Cells.Item(2,5).Value2 = $xl.Cells.Item(2,5).Value2

    Value2 is the calculated value and ti will overwrite the formula.


    ¯\_(ツ)_/¯

    • Proposed as answer by Michal Krzych Friday, October 31, 2014 8:11 AM
    Thursday, October 30, 2014 5:36 PM