Solved

Powershell and Excel

Posted on 2014-11-13
6
94 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
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 69

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

791 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