Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

Powershell and Excel

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
Tim
Asked:
Tim
1 Solution
 
Rajitha ChimmaniCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
footechCommented:
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
 
TimAuthor Commented:
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

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now