none
EXCEL handling via PowerShell scripting RRS feed

  • Question

  • Hi all, 

    Thank you in advance for all your help. 

    End goal is to create an excel file automatically with all groups members for some important machines that I have in our active directory structure via powershell. I managed to achieve via the attached script  



    The Excel file produced by the above script looks like this: 

    It is possible to remove blank cell spaces via powershell? Can someone advise if the steps mentioned in this link https://www.ablebits.com/office-addins-blog/2018/11/28/remove-blank-cells-excel/ can be implemented via powershell? 

    Thank you!

    Regards,

    Aurelian 



    • Moved by Bill_Stewart Friday, December 6, 2019 2:43 PM Move to more appropriate forum
    • Edited by CalimanA Friday, January 3, 2020 9:08 AM
    Friday, December 6, 2019 7:13 AM

Answers

  • Hi Aurelian,

    When populating the cells with values you are using $i and $j which are both increasing in value.

    Your start value(start column for groups) will always be 2, so replace$i+$j+1 with $j+2.

    This way your values wil start being populated from the second column.

    Regards,

    • Marked as answer by CalimanA Friday, December 6, 2019 8:06 AM
    Friday, December 6, 2019 8:05 AM

All replies

  • Hi, 

    A colleague of mine just showed me the way to fix this: 
    # this line 
    
      $worksheet.Cells.Item($i,$i+$j+1) = $mof[$j].GroupName   
    
    # needs to be changed as follow 
    
      $worksheet.Cells.Item($i,$j+2) = $mof[$j].GroupName  
    
    Thank you - Horia Onea 

    Friday, December 6, 2019 8:02 AM
  • Hi Aurelian,

    When populating the cells with values you are using $i and $j which are both increasing in value.

    Your start value(start column for groups) will always be 2, so replace$i+$j+1 with $j+2.

    This way your values wil start being populated from the second column.

    Regards,

    • Marked as answer by CalimanA Friday, December 6, 2019 8:06 AM
    Friday, December 6, 2019 8:05 AM
  • What blank cell spaces. Excel has rows and columns. If you don't want a cell filled just don't fill it.

    I think the first thing you need to do is learn a bit about Excel.  Once you have learned Excel then try asking a question that makes more sense.  Consider what you would do in the GUI Then look up the objects in the Excel documentation and find the method or property   that does what you have done in the GUI. To simply delete a cell just use the "Delete" method of the cell object.

    You can also use your search provider to search for solutions.

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.delete?view=excel-pia


    \_(ツ)_/

    Friday, December 6, 2019 8:07 AM