none
Excel Intellisense Support of Powershell ISE RRS feed

  • Question

  • I've done some Excel VBA scripting in the past and recently began using Powershell to perform some Excel-related tasks.  I'm using Powershell v4 which has Intellisense support but I cannot seem to figure out how to add or configure Intellisense support for Excel library functions.  Is this possible?
    Friday, March 4, 2016 4:30 PM

Answers

  • Not the same Intellisense.  The objects are no registered the same way.  Office completion is not Intellisense it is a pre-cursor that has been hooked into Office.

    TO get some completion do the following:

    enter in ISE this:

    $xl=New-Object -ComObject Excel.Application

    Now hit the run button.  You will now have partial auto-completion.  You still will not get parameter prompting.


    \_(ツ)_/

    • Marked as answer by jross54000 Friday, March 4, 2016 6:05 PM
    Friday, March 4, 2016 6:00 PM

All replies

  • What are Excel library functions?  Do you mean the VBA commands and macros?

    We do not use VBA with PowerShell.

    All of the methods and properties are correctly exposed with reflection and he object.  Ther is no auto-complete on methods on an object except for the name of the method.

    $xl=New-Object -Com Excel.Application
    $xl.Wor<tab>

    This will complete the Workbook property add a "." and <tab> some more for the properties and methods.


    \_(ツ)_/

    Friday, March 4, 2016 4:54 PM
  • For example, if I'm writing a VBA script and I type "Range" and an open parenthesis, I get a pop-up show what parameters the Range class requires (ie. a cell reference).  

    Then when I close the parenthesis and enter a ".", I get an Intellisense window with all the classes and functions associated with the Range class.

    I can also select which libraries I want Intellisense to use (Excel, Office, stdole, VBA or VBA project).  

    The Powershell ISE v3 and later supports Intellisense but I cannot figure out how to import the Excel library functions.  I hope this makes sense - I tried to insert a couple of screen shots but could not submit with images until my account has been verified.

    Thanks.

    Friday, March 4, 2016 5:48 PM
  • Also, after rereading your post, when I enter the sample code you provided, I do not get the autocomplete of the "Workbook" property, nor any associated properties or methods.
    Friday, March 4, 2016 5:54 PM
  • Not the same Intellisense.  The objects are no registered the same way.  Office completion is not Intellisense it is a pre-cursor that has been hooked into Office.

    TO get some completion do the following:

    enter in ISE this:

    $xl=New-Object -ComObject Excel.Application

    Now hit the run button.  You will now have partial auto-completion.  You still will not get parameter prompting.


    \_(ツ)_/

    • Marked as answer by jross54000 Friday, March 4, 2016 6:05 PM
    Friday, March 4, 2016 6:00 PM
  • Thanks - that worked and is helpful.
    Friday, March 4, 2016 6:07 PM
  • Good luck.

    \_(ツ)_/

    Friday, March 4, 2016 6:12 PM