Using Powershell to display value of a formula RRS feed

  • Question

  • 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"

    #delete columns

    #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



    • Moved by Bill_Stewart Friday, November 28, 2014 5:17 PM Off-topic
    Thursday, October 30, 2014 2:20 PM