none
PowerShell and Excel's form controls RRS feed

  • Question

  • Hi,

    Simple question:

    How to read the Checkbox value using the powershell from the Excel sheet? Excel 2010 if that matters, and Powershell 3.0.

    In VBA you can read it by "ThisWorkbook.Worksheets(1).Shapes("cbxTest1").OLEFormat.Object.Value", but when I try the same on the PowerShell the it gives me:

    PS C:\> $ExcelFile.Worksheets(1).Shapes("cbxTest1").OLEFormat.Object.Value
    Method invocation failed because [System.__ComObject#{000208da-0000-0000-c000-000000000046}] doesn't contain a method named 'Worksheets'.
    At line:1 char:15
    + $wb.Worksheets <<<< (1).Shapes("Fine").OLEFormat.Object.Value
        + CategoryInfo          : InvalidOperation: (Worksheets:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Anyone have any idea from where to look at this ?


    Petri

    Wednesday, November 26, 2014 3:38 PM

Answers

  • Hi Petri,

    Based on the description, you want to get the value of form control on the worksheet using PowerShell.

    As far as I know, we can't use the Worksheets(1) to return the item of an collection, we should use Worksheets.Item(1) to return the item from an collection.

    I also wrote a workable sample to get the value from the form control on the worksheet for your reference:

    $excelApp = New-Object -comobject Excel.Application
    $excelApp.Visible = $True
    $aWorkbook = $excelApp.Workbooks.open("C:\Users\UserName\Desktop\Book3.xlsm")
    Write-Output $aWorkbook.Worksheets.item("Sheet1").Shapes.item("cbxTest2").Oleformat.Object.value
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Petri X Thursday, November 27, 2014 10:12 AM
    Thursday, November 27, 2014 3:27 AM
    Moderator

All replies

  • Hi Petri,

    Based on the description, you want to get the value of form control on the worksheet using PowerShell.

    As far as I know, we can't use the Worksheets(1) to return the item of an collection, we should use Worksheets.Item(1) to return the item from an collection.

    I also wrote a workable sample to get the value from the form control on the worksheet for your reference:

    $excelApp = New-Object -comobject Excel.Application
    $excelApp.Visible = $True
    $aWorkbook = $excelApp.Workbooks.open("C:\Users\UserName\Desktop\Book3.xlsm")
    Write-Output $aWorkbook.Worksheets.item("Sheet1").Shapes.item("cbxTest2").Oleformat.Object.value
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Petri X Thursday, November 27, 2014 10:12 AM
    Thursday, November 27, 2014 3:27 AM
    Moderator
  • Hi Fei

    How  you could know that! :D

    This was a great help and time to learn something new again :D

    Many thanks to you!


    Petri

    Thursday, November 27, 2014 10:14 AM