none
Set Value of Excel ComboBox RRS feed

  • General discussion

  • I've got a macro I need to run from Powershell. The macro gets values from a ComboBox. How do I get the value of the ComboBox in Powershell?

    David Jenkins

    • Changed type Bill_Stewart Thursday, January 25, 2018 10:21 PM
    • Moved by Bill_Stewart Thursday, January 25, 2018 10:21 PM Off-topic
    Monday, November 13, 2017 4:13 PM

All replies

  • If the macro is a function then just execute it fro PowerShell and it will return the value.

    Post questions about Excel automation to the Excel VBA developers forum.

    You can always search for these answers and find numerous examples.

    https://stackoverflow.com/questions/3920343/access-return-value-from-vba-function-in-net


    \_(ツ)_/

    Monday, November 13, 2017 7:12 PM
  • Please don't tell me which forum to post in.  It's getting old. 

    It's EXCEL and POWERSHELL.

    Until the forums allow posting in multiple forms at the same time it's BS.


    David Jenkins

    Monday, November 13, 2017 7:33 PM
  • I understand I need to run the macros to complete things however I need combo boxes to be populated with correct info before running the macro.

    If I could populate a variable within a said macro that might work, but the macro doesn't take variables itself.  It gets it's data from a drop down list.


    David Jenkins

    Monday, November 13, 2017 7:35 PM
  • Your request is ambiguous.  If you post in the Excel VBA forum they will help you sort out how to use Excel.  You will also have to refine what you mean by needing the box to be populated.  ComboBoxes are populated when the user makes a selection.

    I will make a guess. set the "SelectedItem" property of the combobox.


    \_(ツ)_/


    • Edited by jrv Monday, November 13, 2017 7:53 PM
    Monday, November 13, 2017 7:51 PM
  • That's the same answer I got the other day posting in the Excel forms but in reverse.  Again tiresome.


    David Jenkins

    Monday, November 13, 2017 7:53 PM
  • The question isn't ambiguous either.  Their either is a way or isn't a way to call an existing combo box.  If there isn't I'm hoping to understand an alternative.


    David Jenkins

    Monday, November 13, 2017 7:53 PM
  • Here is the documentation for the ComboBox. https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.combobox_properties.aspx

    If this can't answer you question then you will have to post in the Excel VBA forum for further assistance.

    You issue is about Excel VBA and Excel objects.  This is a PowerShell form.  You will not get the level of experience you require here.

    I could figure this out for you , assuming you can clarify the question, but I won't waste 10 minutes on it because you can look up all of the things the rest of us would have to look up to tell you the answer.  A little research on your part would be very educational.


    \_(ツ)_/

    Monday, November 13, 2017 7:58 PM
  • The question isn't ambiguous either.  Their either is a way or isn't a way to call an existing combo box.  If there isn't I'm hoping to understand an alternative.


    David Jenkins


    What do you mean by "call a ComboBox"?  You have to be able to describe this in terms that are meaningful technically.  We can get a reference to a ComboBox.  We can assign property values on a ComboBox.  We can cal methods on a ComboBox.  "Call a ComboBox" is ambiguous.

    \_(ツ)_/

    Monday, November 13, 2017 8:02 PM
  • Dude I KNOW IT'S A POWERSHELL FORUM!
    Freaking DUH!
    Just don't bother answering if your going to be so pompous.

    You can start an Excel application from Powershell right?  RIGHT!!

    $objExcel = New-Object -ComObject Excel.Application


    And yes, if I knew everything exactly how to call it I wouldn't be on a forum. 

    Yes you can run a macro from powershell right?  RIGHT!!

    $objExcel.Run("ePM_CheckSheet_Template.xls!Auto_Open")


    You can call a workbook from the spreadsheet right?  Right.  WITH POWERSHELL.

    $worksheet = $Workbook.worksheets.item(1)

    Now somewhere in here is a method or something that might help.  I've looked.  Can't find how to USE IT!!!.

    THIS WOULD BE THE POWERSHELL QUESTION!!


    David Jenkins

    Monday, November 13, 2017 8:07 PM
  • Using Get-Member there are tons of methods.  How do I use them?  How do I get an existing ComboBox?

    See yet?  Do you see?


    David Jenkins

    Monday, November 13, 2017 8:08 PM
  • Please don't tell me which forum to post in.  It's getting old.
    ...
    Again tiresome.
    ...
    Freaking DUH!
    ...
    See yet?  Do you see?

    Please keep in mind that these are peer-to-peer support forums.

    There is no SLA nor any guarantee that anyone will take the time to read (let alone answer!) your question--particularly when you give these kinds of responses that have the potential to alienate the very people who might potentially be able to help you.

    As jrv said, this is not the right place to ask questions about the Excel object model.

    For questions about the Excel object model (regardless of what language you use to access said object model, PowerShell or otherwise), you need to ask in the Excel for Developers forum.


    -- Bill Stewart [Bill_Stewart]

    Monday, November 13, 2017 8:19 PM
  • This is a question on how to access Excel com objects using Powershell. 

    David Jenkins

    Monday, November 13, 2017 8:22 PM
  • This is a question on how to access Excel com objects using Powershell.

    I refer the honorable gentleman to the responses already provided some moments ago.


    -- Bill Stewart [Bill_Stewart]

    Monday, November 13, 2017 8:27 PM
  • The Excel forum would answer you question if you knew how to ask it. 

    What you need to learn first is how to get the objects in a cell.  You can also enumerate the controls collection to find your ComboBox.

    $combobox = $sheet.Shapes['ComboBox1']

    To mange the box you need to use the linked cells.


    \_(ツ)_/

    Monday, November 13, 2017 9:03 PM
  • Tom get answers on how to manage Excel from Net (PowerShell uses Net Interop) you must post in the Office Developers forum.  They do this all of the time.  Do not post in Office VBA forum or in Office users forums.  They are not developers.  All examples you find will be in C# and you will have to convert them.


    \_(ツ)_/

    Monday, November 13, 2017 9:05 PM
  • Thank you for the answers.

    Truly don't you see how difficult and frustrating it is to have to figure out which forum someone else wants you to post in?  Especially with items where more than one forum would be right?

    The other day someone moved my post without even reading it.  Then someone from that form complained I was in the wrong forum.

    So really thank you for the answers.  But until someone comes up with a better way to exactly post the way you want them to it won't happen.  People will get it wrong either way. 


    David Jenkins

    Monday, November 13, 2017 9:12 PM
  • Here is the only forum that can help you sort this out.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Your issue I lack of training in both development and Excel.  Just demanding an answer will not get you very far.

    I found the above information by searching.  Learn to effectively use your search engine and it will greatly simplify you life.

    Also realize that there are two kinds of ComboBoxes in Office. One is for forms and has events and normal properties. The other is an ActiveX version designed to be embedded in a spreadsheet. They are totally different objects. The spreadsheet object cannot be easily managed from code unless you create the object in code.

    Without a fundamental technical knowledge of Office, COM and PowerShell all of this will be very frustrating to you.  It is designed to be used by skilled programmers and not by end users.  Even programmers become frustrated by Office until they take Office developer training or get a book on Office development.


    \_(ツ)_/

    Monday, November 13, 2017 9:19 PM
  • I didn't demand anything at all. 

    I just said it wasn't exactly the wrong form when that was the answer you wanted to provide.

    You are reading to much into it and I'm pushing back a little.

    It's a powershell question not a vba question per say becase  I was looking to understand how interactivity worked from a powershell perspective not a vba perspective.

    Yes I am not a VBA programmer.  I don't want to be.  But I have to figure things out anyway to continue with what I'm doing.

    Again thanks for the help.

    Quipping about wrong forums is stupid.


    David Jenkins

    Monday, November 13, 2017 9:23 PM
  • For future reference.  It is an Excel question.  You posted no PowerShell code and had no PowerShell issues. You asked about an Excel ComboBox.  The smart nothing to have done would have been to ask Excel developers how they would do this.

    Excel is not a management tool or utility and has no relation to PowerShell.  The fact that PS can access COM objects is just part of the Net framework.

    If you showed up and asked how to automate your home IoT refrigerator we would send you elsewhere if you had no specific issues with PowerShell.  This and other technical forums cannot be responsible for every variation of request.

    I am only telling you this for future reference. Always start with the documentation for the technology that you are "targeting".  Work backwards to PowerShell.  This is the normal professional developer approach.  IF you get to a point where you knw how the item works but have issues translating what you have found into PowerShell then post here. TO some degree translation of APIs can be addressed here  but don't get your hopes to high.  C# examples are most helpful.  Subtle issues will require working with the vendor of the system.

    This has always been true of all technical forums.  You may, by chance, find someone with the experience.  Most of the time not.  Working from the product back is more efficient.

    I have been automating Excel for decades.  I had to look this up because I have never needed to automate  a ListBox or textbox.  That is nearly always managed via the spreadsheet which makes me wonder why you have gotten to this corner. 


    \_(ツ)_/

    Monday, November 13, 2017 10:17 PM