none
Powershell: Chart problem RRS feed

  • Question

  • I am writing a chart excel powershell problem, however there is a  problem

    $Y=$sheet.Range($start,$start.End($xlDirection::xlDown))

    $Y=$sheet.Range($start,$start.End($xlDirection::xlDown))
    Exception getting "End": "Exception from HRESULT: 0x800A03EC"
    At line:2 char:1
    + $Y=$sheet.Range($start,$start.End($xlDirection::xlDown))
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

    I found many forum on the net also using this , but don't know why I have error.

    Thursday, November 6, 2014 6:57 AM

Answers

  • PS C:\scripts> [enum]::GetNames([Microsoft.Office.Interop.Excel.xlDirection])
    xlUp
    xlToRight
    xlToLeft
    xlDown
    PS C:\scripts>
    # Used width
    PS C:\scripts> $right=$sheet.UsedRange.End([Microsoft.Office.Interop.Excel.xlDirection]::xlToRight)
    PS C:\scripts> $right.Column
    3


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, November 7, 2014 8:56 AM
    • Marked as answer by sakurai_db Monday, November 10, 2014 7:50 AM
    Friday, November 7, 2014 8:54 AM

All replies

  • It is not possible to know the answer from only one line.

    What is $start?

    The "Range()" command is very picky about what you feed it.


    ¯\_(ツ)_/¯

    Thursday, November 6, 2014 12:49 PM
  • Please give us the entire code of what you're attempted to do here.

    If this post was helpful, please vote up or 'Mark as Answer'! More of this sort of thing at www.foxdeploy.com

    Thursday, November 6, 2014 2:30 PM
  • Actually its only few lines

    $excelapp= new-object -comobject excel.application
    $workbook=$excelapp.Workbooks.open("C:\SLDATA\Book1.xlsx")
    $sheet=$workbook.Sheets.item(1)

    $start=$sheet.range("A1")

    $Y=$sheet.Range($start,$start.End($xlDirection::xlDown))

    Friday, November 7, 2014 1:34 AM
  • But that makes no sense.  $start is already the "Range".

    Are you trying to discover the last element in the range?


    ¯\_(ツ)_/¯

    Friday, November 7, 2014 1:52 AM
  • $start is only a cell

    Are you trying to discover the last element in the range? YES!

    Friday, November 7, 2014 8:15 AM
  • $start is only a cell

    Are you trying to discover the last element in the range? YES!

    No.  $start is a range:

    $start=$sheet.range("A1")

    $start.Cells.Count

    It is a range of one cell.  Using Range() method always returns a range object.

    I suspect theissue is lack of definition of the enumerated constant "xlDown".

    # add interop
    add-type -AssemblyName Microsoft.Office.Interop.Excel
    
    $excelapp= new-object -comobject excel.application
    $workbook=$excelapp.Workbooks.open("C:\SLDATA\Book1.xlsx")
    $sheet=$workbook.Sheets.item(1)
    $start=$sheet.range("A1")
    
    $end=$start.End([Microsoft.Office.Interop.Excel.xlDirection]::xlDown)
    $Y=$sheet.Range($start,$end)

    But I do not think that is what you want.

    PS C:\scripts> $end=$start.End([Microsoft.Office.Interop.Excel.xlDirection]::xlDown)
    PS C:\scripts> $end.row
    1048576

    Perhaps you want "UsedRange"


    ¯\_(ツ)_/¯

    Friday, November 7, 2014 8:46 AM
  • Here is how to reference the end of a sheet's data.

    PS C:\scripts> $sheet.UsedRange.End([Microsoft.Office.Interop.Excel.xlDirection]::xlDown).Column
    1
    PS C:\scripts> $sheet.UsedRange.End([Microsoft.Office.Interop.Excel.xlDirection]::xlDown).Row
    12
    PS C:\scripts>
    


    ¯\_(ツ)_/¯

    Friday, November 7, 2014 8:52 AM
  • PS C:\scripts> [enum]::GetNames([Microsoft.Office.Interop.Excel.xlDirection])
    xlUp
    xlToRight
    xlToLeft
    xlDown
    PS C:\scripts>
    # Used width
    PS C:\scripts> $right=$sheet.UsedRange.End([Microsoft.Office.Interop.Excel.xlDirection]::xlToRight)
    PS C:\scripts> $right.Column
    3


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, November 7, 2014 8:56 AM
    • Marked as answer by sakurai_db Monday, November 10, 2014 7:50 AM
    Friday, November 7, 2014 8:54 AM