Solved

Powershell and Excel

Posted on 2014-11-13
6
97 Views
Last Modified: 2015-07-30
I am trying to learn how to export Data into Excel, problem is, I need the data to go to a specific Range of Cells.  I know how to export-csv, however I'm trying to write a script that will gather data from various Security Groups and export the data into Excel.

Get-ADGroupMember ADgroupname-here | select Name will give me the list of employees in a specific group, however, I need all of those names to appear in Excel (one name per cell) starting at Cell A2 for example, next name populate in A3, etc..  (then once that is complete I'll have it get adgroupmember info from another ADGroup and start populating the names starting in Cell B2...

I found the below example online and it works (for disk info) but I cannot seem to modify it to work with ADGroupMember - thanks

$excel = New-Object -ComObject excel.application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
$diskSpacewksht= $workbook.Worksheets.Item(1)
$diskSpacewksht.Name = 'DriveSpace'
$diskSpacewksht.Cells.Item(1,1) = 'DeviceID'
$diskSpacewksht.Cells.Item(1,2) = 'VolumeName'
$diskSpacewksht.Cells.Item(1,3) = 'Size(GB)'
$diskSpacewksht.Cells.Item(1,4) = 'FreeSpace(GB)'

$row = 2
$column = 1
Get-CimInstance -ClassName Cim_LogicalDisk | ForEach {
    #DeviceID
    $diskSpacewksht.Cells.Item($row,$column) = $_.DeviceID
    $column++
    #VolumeName
    $diskSpacewksht.Cells.Item($row,$column) = $_.VolumeName
    $column++
    #Size
    $diskSpacewksht.Cells.Item($row,$column) = ($_.Size /1GB)
    $column++
    #FreeSpace
    $diskSpacewksht.Cells.Item($row,$column) = ($_.FreeSpace /1GB)
    #Increment to next Row and reset Column
    $row++
    $column = 1
}

$usedRange = $diskSpacewksht.UsedRange                                    
$usedRange.EntireColumn.AutoFit() | Out-Null
$workbook.SaveAs("C:\temp\DiskSpace.xlsx")
$excel.Quit()

Anyone have any suggestions on how to do what I'm trying to do? - Fairly new to PowerShell
0
Comment
Question by:Tim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:Rajitha Chimmani
ID: 40441440
I have not tested this but tried to edit to work for you. Try to run this

$excel = New-Object -ComObject excel.application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
$diskSpacewksht= $workbook.Worksheets.Item(1)
$diskSpacewksht.Name = 'DriveSpace'
$diskSpacewksht.Cells.Item(1,1) = 'Name'

$row = 2
$column = 1
Get-ADGroupMember ADgroupname-here | ForEach {
    $diskSpacewksht.Cells.Item($row,$column) = $_.Name
    $row++
    $column = 1
}

$usedRange = $diskSpacewksht.UsedRange                                    
$usedRange.EntireColumn.AutoFit() | Out-Null
$workbook.SaveAs("C:\temp\DiskSpace.xlsx")
$excel.Quit()

Open in new window

0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40442387
Almost. We should change the var names (no longer disk space info here), and allow for multiple AD group names to use different columns. And the group names should be stored in the Excel sheet as well. This could be expanded to use an existing worksheet containing the group names in row 1, and the PS script using those to get the members.
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()

$sheet= $workbook.Worksheets.Item(1)
$sheet.Name = 'AD Group Members'

$column = 1
foreach ($grp in 'Group1', 'Group2', 'Group3') {
  $sheet.Item(1,$column).Value2 = $grp
  $row = 2
  Get-ADGroupMember $grp | ForEach {
    $sheet.Cells.Item($row++,$column).Value2 = $_.Name
  }
  $column++
}

[void] $sheet.UsedRange.EntireColumn.AutoFit()
$workbook.SaveAs('C:\temp\ADGroups.xlsx')
$excel.Quit()

Open in new window

0
 
LVL 40

Expert Comment

by:footech
ID: 40445683
Don't give me any points for this, but I just wanted to show an example of how you could code to enter the group names at runtime instead of having them hard-coded into the script or an existing spreadsheet.

Just substitute the following for lines 8-16 of Qlemo's code.
$column = 1
do
{
    $row = 1
    $group = Read-Host "Enter group name"
    $sheet.Cells.Item($row,$column).Value2 = $group

    Get-ADGroupMember $group | Sort Name | ForEach {
        $sheet.Cells.Item(++$row,$column).Value2 = $_.Name
    }
    $column++
} while ( (Read-Host "Do you want to enter another? (y/n)") -ne "n")

Open in new window

0
 

Author Comment

by:Tim
ID: 40730570
Thanks to all who have replied and so sorry for the very late reply.  I actually haven't had time to revisit this project as it is a very low project

All of your suggestions look great.  I'll try and test and post update soon..

thanks again
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Simple Linear Regression
Starting up a Project

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question