Solved

Powershell and Excel

Posted on 2014-11-13
6
92 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 39

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DFS-R questions 4 26
Remove all hidden metadata properties of MS .Docx Files 7 38
need assistance with this powershell script 4 42
VB.net and sql server 4 35
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

770 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