?
Solved

Powershell and Excel

Posted on 2014-11-13
6
Medium Priority
?
99 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

765 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