none
Using Powershell to protect specific Cells in Excel RRS feed

  • Question

  • Hi Everybody,

    I have a (hopefully) simple question:

    How can I protect Cells from beeing edited by the User (using a Password) with Powershell.

    I need to create many different Kind of Excel Workbooks and the User should be able to change only some values. The rest of the Cells should be write protected.

    Is this somehow possible using Powershell and the "-ComObject Excel.Application" ?

    In general: is everything posible with Powershell like it is with VBA Macros? (the same code base?)

    Thanks in advance!

    Monday, November 10, 2014 4:48 PM

Answers

All replies

  • You can access the Excel object model using PowerShell.

    However, for specific questions about how to use this object (whether from PowerShell or VBA), please ask in the Excel for Developers forum:

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

    In general, I would recommend learning how to automate using VBA first. Then, if necessary, you can translate the VBA code into PowerShell.


    -- Bill Stewart [Bill_Stewart]

    Monday, November 10, 2014 5:15 PM
    Moderator
  • In general: is everything posible with Powershell like it is with VBA Macros? (the same code base?)

    Thanks in advance!

    Not completely.  In VBA we generally use internal constants. These work differently in PowerShell through Interop.

    Reference to collections is usually offset with .Item(args). 

    As Mike posted.  Good knowledge of how to do it in VBA is a must.  There is no formal PowerShell documentation.

    Always use s much from templates as possible including embedding macros and VBA code.  Do very little in PowerShell,

    Remember that Excel was automated well before scripting started to be used with Excel. It has better internal support for automation and it has a better debugger for Office products.

    Power Shell to Excel is quite slow so don't plan on large updates.  I have one client who is using an external script to modify 10,000 rows and the task runs for 30 hours on a quad processor system with 16Gb.  Office is slow and PowerShell/Excel is even slower.


    ¯\_(ツ)_/¯

    Monday, November 10, 2014 6:31 PM
  • Thank you for your answers!

    I am already an advanced programmer/scripter and did a lot of stuff with vbs, perl, bash, java, c# and Powershell.

    The thing why I think about using Powershell is the following:

    I need to split 1 File into arround 150 single Excel files and I thought it would be easier to use an external Programming language when it comes to handle multiple files.

    What do you think? Is Powershell the better choice or should I try using vba?

    Tuesday, November 11, 2014 8:26 AM
  • If you are as software engineer then you know that the answer cannot be gleaned from so little information.  It all depends on the rules of the split.  If you need extensive filtering then an external solution will be slow and difficult if you are not a master of Excel.  It can be done.  You just need to apply good design principles to the choice of tools.

    If Excel is 32 bit then you would use a 32 bit tool (it is faster to not cross the boundary).

    Example:

    You are splitting on a worksheet boundary - any language will be good enough
    You are extracting data from a sheet based on a filter - Use SQL from any language.
    You need cross cell calculations that cannot be done with SQL such as a pivot table - use VBA.

    You can automate a workbook by opening it in PowerShell (or other system) and injecting a template with macros (VBA) and kick off a process in the WB. The process can load and save workbooks as needed.

    Picking the correct design will determine how easy or difficult this is. Never choose your tools before you have a complete understanding of the task.


    ¯\_(ツ)_/¯

    Tuesday, November 11, 2014 9:27 AM
  • If you are as software engineer then you know that the answer cannot be gleaned from so little information.  It all depends on the rules of the split.  If you need extensive filtering then an external solution will be slow and difficult if you are not a master of Excel.  It can be done.  You just need to apply good design principles to the choice of tools.

    If Excel is 32 bit then you would use a 32 bit tool (it is faster to not cross the boundary).

    Example:

    You are splitting on a worksheet boundary - any language will be good enough
    You are extracting data from a sheet based on a filter - Use SQL from any language.
    You need cross cell calculations that cannot be done with SQL such as a pivot table - use VBA.

    You can automate a workbook by opening it in PowerShell (or other system) and injecting a template with macros (VBA) and kick off a process in the WB. The process can load and save workbooks as needed.

    Picking the correct design will determine how easy or difficult this is. Never choose your tools before you have a complete understanding of the task.


    ¯\_(ツ)_/¯

    yes, you are right.

    the application would be quite simple:

    there is one file with a amount of arround 14.000 datasets (rows) and they will have a format like:

    Name, Age, Serial, Size, Location1, Location2

    test1, 15, 12351, 10, 01, 05
    test2, 15, 12351, 10, 02, 05
    test3, 15, 12351, 10, 01, 07

    The file should be splittet by the location so later there will be files like:

    file_01_05.xls
    file_02_05.xls
    file_01_07.xls

    I guess I will try to make a combination of powershell and vba.

    thank you very much!

    Tuesday, November 11, 2014 2:53 PM
  • If you are only splitting on one field the SQL would be the fastest and easiest.

    Query from powershell by sheet and you will get a filterable dataset that can be exported quickly.


    ¯\_(ツ)_/¯

    Tuesday, November 11, 2014 3:18 PM